Syntax, please :-((((((((((((((

[Radu]

Hi. I'm trying to build a query which would return TRUE or FALSE as
'IsRerun' depending on the matching status with another table, as
follows (simplified query):

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'"

I have tried a lot of versions..... like
CASE ManagerReport.PIN
WHEN ManagerReport.PIN IS NULL
THEN 'TRUE'
ELSE
'FALSE'
END AS IsRerun

or

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

or

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)

Please, how should I write this ?
Thanks a lot
Alex.

[Gert-Jan Strik]

It's quite simple... once you've seen it!

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

HTH,
Gert-Jan
Radu wrote:

>
Hi. I'm trying to build a query which would return TRUE or FALSE as
'IsRerun' depending on the matching status with another table, as
follows (simplified query):

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'"

I have tried a lot of versions..... like
CASE ManagerReport.PIN
WHEN ManagerReport.PIN IS NULL
THEN 'TRUE'
ELSE
'FALSE'
END AS IsRerun

or

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

or

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)

Please, how should I write this ?
Thanks a lot
Alex.

[Erland Sommarskog]

Radu (cu*************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Radu]

Thank you both, Gert-Jan and Erland, for your reading & answering my
post.

Alex. :-)))