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.
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:
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
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.