CASE myField WHEN NULL ... 1

[jonbatts]

I'm trying to use a case statement to return a specific value.

CASE myField WHEN NULL THEN 'FieldIsNull' WHEN 'A' THEN 'AValue' WHEN 'B' THEN 'BValue' ELSE 'NotListed' END

Of course when myField is NULL I'm getting 'NotListed' because of the inability to compare NULL. Is there a way to do this with a comparitive SQL statement like this, or do I have to resort to

CASE WHEN myField IS NULL THEN 'FieldIsNull' WHEN myField = 'A' THEN 'AValue' WHEN myField = 'B' THEN 'BValue' ELSE 'NotListed' END

Thanks.

 

[cLFlaVA]

will this work?

case isnull(myfield, 'NULL')
when 'NULL' then 'FieldIsNull'
when 'A' then 'AValue'
when 'B' then 'BValue'
else 'NotListed'
end

*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[jonbatts]

Hadn't thought of that. It would do the trick in my case. The only problem is if I wanted to allow myField to be the actual string 'NULL'. Like I said it works in my case. Thanks a lot.

 

[cLFlaVA]

yeah, that would work only under the fixed assumption that it would never contain a value "NULL". not the best solution... but a solution.



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]