Using CASE and ISNULL together

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using CASE and ISNULL together

Author

Topic

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-11-05 : 07:01:55

Hi All,

Small problem here which I think will be relatively simple to answer.

I have a query and in part of it I have the following
CASE ISNULL(PT.pt_PreviousDiagnosis,3)
WHEN 'Y' THEN 1
WHEN 'N' THEN 2
WHEN 'U' THEN 3

Which I take to say if PT.pt_PreviousDiagnosis Is NUll then make it 3 and go through the CASE statement. But it does not do this. I am still getting NULL's?

Thanks,
Gavin

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-05 : 07:09:20

quote:

Hi All,

Small problem here which I think will be relatively simple to answer.

I have a query and in part of it I have the following
CASE ISNULL(PT.pt_PreviousDiagnosis,3)
WHEN 'Y' THEN 1
WHEN 'N' THEN 2
WHEN 'U' THEN 3

Which I take to say if PT.pt_PreviousDiagnosis Is NUll then make it 3 and go through the CASE statement. But it does not do this. I am still getting NULL's?

Thanks,
Gavin

Because you're not testing for nulls. Your case statement is incomplete, or back to front...

CASE isnull (PT.pt_PreviousDiagnosis,3)
when 1 then 'Y'
when 2 then 'N'
when 3 then 'U'
else 'something else' -- to catch nulls
end

or as you had it

CASE ISNULL(PT.pt_PreviousDiagnosis,3)
WHEN 'Y' THEN 1
WHEN 'N' THEN 2
WHEN 'U' THEN 3
ELSE 'some other value'
end

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-11-05 : 07:09:50

As none of the conditions in the case matches the value 3 the result of the case expression is null.

CASE PT.pt_PreviousDiagnosis
WHEN 'Y' THEN 1
WHEN 'N' THEN 2
else 3

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-11-05 : 07:20:24

I cannot beleive I missed that

Thanks it's working prefectly now

mr_mist
Thanks for the help and showing me that you can use Else statement in Case statements

Cheers,
Gavin

quote:

As none of the conditions in the case matches the value 3 the result of the case expression is null.

CASE PT.pt_PreviousDiagnosis
WHEN 'Y' THEN 1
WHEN 'N' THEN 2
else 3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources