On 2014/10/07 12:42, Tony Papadimitriou wrote:
You're right, ... but in that page it says:
The only difference between the following two CASE expressions is that the x
expression is evaluated exactly once in the first example but might be
evaluated multiple times in the second:
CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
So, my understanding is that the second CASE (for my example) should give the
same result as the first CASE.
No, your example second CASE statement compares a field value (a) to Boolean expressions which each can result to either 0 or 1...
so unless the value in a is exactly 0 or 1, the ELSE-section will always trigger and so output a '3'. Do you understand the
difference between a boolean result and a value result from an expression?
> select a,case a when a<10 then 1 when a < 20 then 2 else 3 end cased from a;
> gives unexpected result 3,3,3
In the document examples the first example compares x to w1 and w2 respectively, if it matches any the resulting output is r1 or r2,
and if no match is found, r3.
The second example do not have a base field it uses to compare, so in every WHEN section it evaluates the complete BOOLEAN
expression which returns either 1 or 0 (True or False) and so determines which is shown... If you change that to your examples form
to read:
CASE x WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
then x is compared to Boolean functions and will have unpredictable results, unless it is itself a boolean value, but if it was a
boolean value you could just have done this:
CASE x WHEN 1 THEN r1 ELSE r2 END
which is pretty mush the IF-THEN-ELSE form most languages support.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
