Why does the case expression ignore the condition I have to catch null values? With this test table: create table AATest (ID integer, Code varchar (5)); insert into AATest values (1, 'AAAA'); insert into AATest values (2, 'BBBB'); insert into AATest values (3, 'CCCC'); insert into AATest values (4, NULL); insert into AATest values (5, NULL); and this select statement: SELECT ID , case AATest.Code when 'AAAA' then 'A' when 'BBBB' then 'A' when 'CCCC' then 'A' when NULL then 'X' else 'U' end case as Status FROM AATest; I would expect to get 3 rows of status 'A' and two rows of status 'X'. Instead I get 3 rows of status 'A' and two rows of status 'U'. Why doesn't the When Null line match the null values? This format does give my expected results: SELECT ID , case when AATest.Code = 'AAAA' then 'A' when AATest.Code = 'BBBB' then 'A' when AATest.Code = 'CCCC' then 'A' when AATest.Code is NULL then 'X' else 'U' end case as Status FROM AATest; SQL Anywhere version 12.0.1 build 3436 asked 07 Aug '13, 15:38 Siger Matt |
The first SELECT statement is an example of a 'simple case' statement where AATest.Code is being compared to NULL using the equality comparison ( The second SELECT statement is an example of a 'searched case' statement where each condition is specified individually. This allows you to specify The expression answered 07 Aug '13, 16:16 Mikel Rychliski 1
Got it. Thanks for the clarification. When handling this situation is it more common to use the searched case (which seems like quite a bit more typing) or use the simple case with a more filtered initial comparison like this: SELECT ID , case coalesce(AATest.Code,'X') when 'AAAA' then 'A' when 'BBBB' then 'A' when 'CCCC' then 'A' when 'X' then 'X' else 'U' end case as Status FROM AATest;
(07 Aug '13, 16:39)
Siger Matt
If you want accurate results, use the searched case. Who cares if it's more typing? You want accurate results, don't you?
(08 Aug '13, 08:47)
TonyV
Replies hidden
2
Hm, besides the particular NULL comparison effect, I'd usually prefer the simple form if it's exactly "that simple case": i.e. comparing one expression to a set of constant values... - here, using the simple form is more comprehensible IMHO. So I would not generally recommend the "searched case" - and unless one is aware of the "= NULL isn't IS NULL" problem, the searched case will tend to be a potential pitfall, as well... NULLs are difficult, but usable:)
(08 Aug '13, 09:23)
Volker Barth
I agree: code the simple case if you can get away with it (you can't, here). Re: "NULLs are difficult" - if "difficult" is German for "despicable" then I agree with that too :)
(08 Aug '13, 16:31)
Breck Carter
|
Note the different behaviour when using an IF expression, as documented here...