The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

Note the different behaviour when using an IF expression, as documented here...

(08 Aug '13, 03:58) Volker Barth

The first SELECT statement is an example of a 'simple case' statement where AATest.Code is being compared to NULL using the equality comparison (AATest.Code = NULL).

The second SELECT statement is an example of a 'searched case' statement where each condition is specified individually. This allows you to specify AATest.Code is NULL instead of AATest.Code = NULL

The expression AATest.Code = NULL will always evaluate to 'unknown' and never 'true', therefore the condition will never be satisfied. This is standard SQL behavior.

permanent link

answered 07 Aug '13, 16:16

Mikel%20Rychliski's gravatar image

Mikel Rychliski
2.1k1641
accept rate: 30%

edited 07 Aug '13, 16:20

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18
×11

question asked: 07 Aug '13, 15:38

question was seen: 1,126 times

last updated: 08 Aug '13, 16:32