Posted by Mike C# on 07/27/06 18:23

Use a searched CASE expression with IS NULL. The way you're doing it is
effectively doint the comparison NULL/4.0 = NULL, which evaluates to
UNKNOWN, which means your CASE will drop through to the next WHEN (or the
ELSE in this case). NULL comparisons need to use IS NULL or IS NOT NULL.

SELECT NULL/4.0 AS 'TEST1', TEST2 =
CASE
WHEN NULL/4.0 IS NULL THEN 'HURAY'
ELSE 'OH DARN'
END

"rcamarda" <robc390@hotmail.com> wrote in message
news:1154022027.273127.29840@m73g2000cwd.googlegroups.com...
>I have a field that may be null which is valid, and I am finding
> something I didnt expect when working with nulls.
> SELECT NULL/4.0 will return NULL (which I expect), however, when I test
> it with a case it does not:
>
> SELECT NULL/4.0 AS 'TEST1', TEST2 = CASE NULL/4.0 WHEN NULL THEN
> 'HURAY' ELSE 'OH DARN' END
>
> I can work around by testing for NULL first else CASE ..., but I'd like
> to understand why the CASE does not test for null.
> TIA
> rob
> SQL 2005 Enterprise x64, SP1
>

[Back to original message]