Sybase 11 - Case Statement

[Jim Egan]

I believe the CASE statement/expression was introduced in 11.5.
Are you trying to use it as a statement or an expression?

Example of an expression:

SELECT CASE
WHEN column1 < 0 THEN "Negative"
WHEN column1 = 0 THEN "Zero"
WHEN column1 > 0 THEN "Positive"
ELSE "Unknown"
END
FROM Table
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Get your free subscription to PowerTimes at http://www.powertimes.com

[Bret Halford]

CASE was introduced in ASE 11.5, it is not in 11.0.x.

The proper syntax is documented in the ASE TSQL Guide, which is available
online
at http://manuals.sybase.com/onlinebooks/group-as/asg1200e

-bret

ABarone wrote:

> I am new to Sybase and have not found a clear answer to the following
> question:
>
> Is there a Case statement (or equivalent) that will work in Sybase 11 and
> what is its proper syntax?
>
> Thanks very much.
>
> AB

[Alex Chudnovsky]

You surely meant:

> SELECT CASE
> WHEN column1 < 0 THEN "Negative"
> WHEN column1 = 0 THEN "Zero"
> WHEN column1 > 0 THEN "Positive"
> ELSE "Unknown"

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ELSE "Put that bottle of whiskey aside and call your DBA now!"

> END
> FROM Table

:)
--
Alex Chudnovsky,
http://www.chudnovsky.org

"Jim Egan" <dba...@eganomics.com> wrote in message
news:MPG.150dcef87...@forums.sybase.com...

[ABarone]

[Jim Egan]

>>
You surely meant:

> SELECT CASE
> WHEN column1 < 0 THEN "Negative"
> WHEN column1 = 0 THEN "Zero"
> WHEN column1 > 0 THEN "Positive"
> ELSE "Unknown"
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
ELSE "Put that bottle of whiskey aside and call your DBA now!"

> END
> FROM Table
<<

Sometimes I work in the fourth dimension and you've got to be REALLY careful there. <g>

[Bret Halford]

Jim Egan wrote:

> >>
> You surely meant:
>
> > SELECT CASE
> > WHEN column1 < 0 THEN "Negative"
> > WHEN column1 = 0 THEN "Zero"
> > WHEN column1 > 0 THEN "Positive"
> > ELSE "Unknown"
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> ELSE "Put that bottle of whiskey aside and call your DBA now!"
>
> > END
> > FROM Table
> <<
>
> Sometimes I work in the fourth dimension and you've got to be REALLY careful there. <g>
> --

You all might enjoy the following brain-teaser, which looks remarkably similar.
Consider the following:

You can run the following as many times as you want, and never see a value
larger than three:

select convert(int,(RAND() * 3))

But run this a hundred times, and you will get a fair number of "More than Threes":

select
CASE convert(int, (RAND() * 3))
when 0 then "Zero"
when 1 then "One"
when 2 then "Two"
when 3 then "Three"
else "More than Three"
end

Can you explain why? (hint: it is not a bug).

-bret

[Sherlock, Kevin]

Bret Halford wrote:
>
> You all might enjoy the following brain-teaser, which looks remarkably similar.
> Consider the following:
>
> You can run the following as many times as you want, and never see a value
> larger than three:
>
> select convert(int,(RAND() * 3))
>
> But run this a hundred times, and you will get a fair number of "More than Threes":
>
> select
> CASE convert(int, (RAND() * 3))
> when 0 then "Zero"
> when 1 then "One"
> when 2 then "Two"
> when 3 then "Three"
> else "More than Three"
> end
>
> Can you explain why? (hint: it is not a bug).
>
> -bret

This is a good one. I'm not claiming to know the answer, but I'll
hazard a guess. Since the expression involves the RAND function, it is
not a considered a constant. Therefore the expression is resolved for
every "when" condition. This is the equivalent of

select CASE
when convert(int, (RAND() * 3)) = 0 then "Zero"
when convert(int, (RAND() * 3)) = 1 then "One"
when convert(int, (RAND() * 3)) = 2 then "Two"
when convert(int, (RAND() * 3)) = 3 then "Three"
else "More Than Three"
end

So for each comparison, the RAND function generates a "new" integer.
So, in the first "when", if we happen to generate a zero, well luckily
we'll return "Zero". If not, this comparison fails. Now in the next
"when", a NEW number is generated and compared to 1. Well, we could
generate any number between 0 and 3. In other words, each "when" line
generates a new number and is compared and has a 1/3 chance of matching
(actually the chances of getting a 3 are very slim compared to 0 thru
2). So, after failing 4 attempts to get one of the 4 numbers, you're
left with the "else" condition. The else condition should properly read:

else "We guessed wrong 4 times in a row!"

[Alex Chudnovsky]

Interesting explanation, unfortunately cant run code (not at work!), however
my understanding was that:

SELECT AccountNo,rand(3) FROM Whatever

would always produce SAME "random" number on 11.9.2 (Solaris). I think this
is because Sybase is trying to speedup function and thinks its a constant.
Now, why would it think differently in case of "cases"?

I am pretty sure it will "evaluate" this expression only once. Now, what
would be the answer then, hmmm.

My take (again pity cant verify!) is that from my observations sometimes
Sybase insists on having small fraction attached to a number, ie:
3.000000000000000000001 -- as opposed to exactly 3, for some reason (I wasnt
drunk!) I noticed it doing a few times.
convert(int) probably converts it to 4, hence the last ELSE case gets its
chance to execute.

Well, that doesnt explain why "select convert(int,(RAND() * 3))" is ok all
the time. I must admit I like Sherlock's theory (isnt it obvious?)

--
Alex Chudnovsky,
http://www.chudnovsky.org

"Sherlock, Kevin" <ksh...@denims01.dex.uswest.com> wrote in message
news:3AA6B261...@denims01.dex.uswest.com...

[Jim Egan]

Just because the output says it's "More than three" doesn't mean it should be. That's as
close as I can come but I'll agree with Kevin's answer and here's why: I base it on the
fact that if you manually seed the RAND() (I tried it through 10,000,000 seed values but
you only really need 3 or 4) then the ELSE will never come up.

[Bret Halford]

Full marks!

Congratulations.

-bret

[Bret Halford]

Alex Chudnovsky wrote:

> Interesting explanation, unfortunately cant run code (not at work!), however
> my understanding was that:
>
> SELECT AccountNo,rand(3) FROM Whatever
>
> would always produce SAME "random" number on 11.9.2 (Solaris). I think this
> is because Sybase is trying to speedup function and thinks its a constant.

You are correct on the behavior. Can't verify at the moment why it is done that
way,
but yes, the rand() in this case is evaluated just once.

>
> Now, why would it think differently in case of "cases"?
>

Cause the functional spec called for evaluating the expression for each
"when". :-)

You can, of course, declare a variable, select rand() into that variable, and
then
run the CASE against the variable to get the behavior the author of this example

was actually trying for.

-bret

[Chris King]

No need for a DBA, column1 is just NULL ;-)

Chris King
CK Software
ch...@cks.com.au

"Alex Chudnovsky" <al...@chudnovsky.org> wrote in message
news:3RgWIUo...@forums.sybase.com...

[Sherlock, Kevin]

Alex Chudnovsky wrote:
>
> I am pretty sure it will "evaluate" this expression only once. Now, what
> would be the answer then, hmmm.
>

Well, to prove it to yourself that the evaluation is done for each
"when" statement, run the following example which is similar, but will
prove this theory out:

select
CASE
when convert(int, (RAND() * 3)) = 0 then "Zero - as in -> " + convert(varchar,convert(int,(RAND()*3)))
when convert(int, (RAND() * 3)) = 1 then "One - as in -> " + convert(varchar,convert(int,(RAND()*3)))
when convert(int, (RAND() * 3)) = 2 then "Two - as in -> " + convert(varchar,convert(int,(RAND()*3)))
when convert(int, (RAND() * 3)) = 3 then "Three - as in -> " + convert(varchar,convert(int,(RAND()*3)))
else "More than Three - and here is what it is -> " + convert(varchar,convert(int,(RAND()*3)))
end

Now, if the expression evaluated just once, you would see output that
makes sense. Ie:

Zero - as in -> 0
One - as in -> 1
...
...
More than Three - and here is what it is -> ???

But as such, since the expression is evaulated for each when/else
condition, you will see output that illustrates that, Ie:

Zero - as in -> 2
One - as in -> 0
...
More than Three - and here is what it is -> 1

Of course you will also see some "correct" instances where two
evaluations happen to produce the same number.

I've also got to believe that RAND() produces numbers strictly ">=
convert(double precision,0) and < convert(double precision,1)". That
is, you will never see a "3" in the example above, and therefore it is
an impossible condition to meet. Otherwise, the RAND() function would
be producing one more possible number than it should. Here is a
correlary (sp) example to prove that all is sane in the world:

select
CASE
when convert(int,(RAND()*3)) between 0 and 2 then "Between Zero and Two"
else "The sky is falling"
end

[Ilya Zvyagin]

Alex Chudnovsky wrote in message <3RgWIUo...@forums.sybase.com>...
>You surely meant:

I'm sure Jim meant

>> SELECT CASE
>> WHEN column1 < 0 THEN "Negative"
>> WHEN column1 = 0 THEN "Zero"
>> WHEN column1 > 0 THEN "Positive"

ELSE "column 1 is NULL"

>> END
>> FROM Table