Ignore null in case statement

[sujitgouda]

I have 7 values for a field in the database excluding a null value.
How do I use a case statement to ignore that null value.

case [data_item] when One then 1
when Two then 2
when Three then 3
when Four then 4
else 99
end

I am using the above case statement, but I am not able to ignore the null values.

[ToriBurns]

What database are you using?.. I'm not sure if this one will work. But, the idea is to select only the [data_item] that is not null before you apply your second case. That way [data_item] which is null will remain null and will not fall into '99'. Hope it helps.  :)

Script:

Code Select Expand

case [data_item] when is not null then
case [data_item] when One then 1
when Two then 2
when Three then 3
when Four then 4
else 99
end
end

Cheers!
Tori

[bdbits]

As ToriBurns noted, it matters what database you are using. The ANSI mostly-database-independent way is to use a COALESCE statement. For SQL Server, you can use ISNULL(), Oracle I believe is NVL(). Other databases may have their own functions.