Posted by Erland Sommarskog on 02/15/07 22:22

Radu (cuca_macaii2000@yahoo.com) writes:
> SELECT ImportedPINS.PIN,
> CASE ManagerReport.PIN
> WHEN (IS NULL)
> THEN 'FALSE'
> ELSE
> 'TRUE'
> END AS IsRerun,
> FROM
> ImportedPINS LEFT OUTER JOIN ManagerReport
> ON ImportedPINS.PIN = ManagerReport.PIN
>
> This version returns "Incorrect syntax near the keyword 'IS'"

The CASE expressions has two forms. The main form is

CASE WHEN expression1 THEN expression2 WHEN expression2 THEN ....

The other form is

CASE expresion1 WHEN expression2 THEN expression3 WHEN expression4...

This is a shortcut for

CASE WHEN expr1 = expr2 THEN expr3 WHEN expr1 = expr4 THEN ...

Thus, in your case:

CASE WHEN ManagerReport.PIN IS NULL
THEN 'TRUE'
ELSE
'FALSE'
END AS IsRerun

> CASE ManagerReport.PIN
> WHEN NULL
> THEN 'TRUE'
> ELSE
> 'FALSE'
> END AS IsRerun
>
> (this one executes, but ALL show as TRUE, and my data says that out of
> 200 test records I should have 195 Reruns and 5 Not Reruns)

This is because all comparisons with NULL yields UNKNOWN, as NULL is
an unknown value.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]