case problem 2

[shannanl]

I am using the following. It returns "IT IS NOT NULL" no matter if there is a null value in the Disch_date field or not. What am I doing wrong?

Thanks in advance - Shannan

SELECT Admit_date, Disch_date,

CASE Disch_date
WHEN NULL THEN 'IT IS NULL'
ELSE 'IT IS NOT NULL'
END 'RESULTS'

FROM Pat_visit_T

 

[gmmastros]

You cannot test for null that way. Here's a method that will work.

[COLOR=blue]SELECT[/color] Admit_date, Disch_date,
 
  [COLOR=blue]CASE[/color] [COLOR=blue]When[/color] Disch_date [COLOR=blue]Is[/color] NULL
      [COLOR=blue]THEN[/color] [COLOR=red]'IT IS NULL'[/color]
      [COLOR=blue]ELSE[/color] [COLOR=red]'IT IS NOT NULL'[/color]
  [COLOR=blue]END[/color] [COLOR=red]'RESULTS'[/color]

[COLOR=blue]FROM[/color] Pat_visit_T

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

try this

[COLOR=blue]SELECT[/color] Admit_date, Disch_date,
 
  [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] Disch_date
       [COLOR=blue]IS[/color] NULL [COLOR=blue]THEN[/color] [COLOR=red]'IT IS NULL'[/color]
      [COLOR=blue]ELSE[/color] [COLOR=red]'IT IS NOT NULL'[/color]
  [COLOR=blue]END[/color] [COLOR=red]'RESULTS'[/color]

[COLOR=blue]FROM[/color] Pat_visit_T

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[shannanl]

Thanks guys. I appreciate the quick response.

Shannan