Question on CASE and NULL - SQL Server Forums

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)

Question on CASE and NULL

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-09 : 09:38:41

How do you resolve a conditional in a CASE statement where you want to determine whether the value you are looking is null or not?



		CASE OFFICE.OFFICE_ID
			WHEN IS NOT NULL THEN 'NY'
		END AS AMLSKey,

This causes an error. I just want to determine if the value in the row is NOT null, then assign the value NY.

thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 09:46:51

Select case when OFFICE_ID is null then 'NY' end from yourTable

Madhivanan

Failing to plan is Planning to fail

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-09 : 09:53:30

[code]
select OFFICE_NAME,
case
when OFFICE_ID is null then 'NY'
end
from mytable
[/code]

Incorrect syntax near the keyword 'IS'.
Incorrect syntax near the keyword 'END'.

This causes a syntax error.

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2006-02-09 : 10:00:01

my apologies Madhivanan,

your solution worked perfectly.

thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 10:06:34

Also you need to make sure what you should have if it is not null


select OFFICE_NAME,
case 
   when OFFICE_ID is null then 'NY' 
   else OFFICE_ID 
end
from mytable

Madhivanan

Failing to plan is Planning to fail

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-09 : 10:12:36

I think your confusion is because there are 2 styles of CASE statments: Simple and Searched. Check them out in Books Online. I pretty much always use the "Searched" style. Your original post represents the "simple" style and that style doesn't handle nulls in a very intuitive way.

Be One with the Optimizer
TG

dlit
Starting Member

2 Posts

Posted - 2006-02-09 : 19:26:33

You didn't give the structure of your data, but if the only thing you want to do in the CASE is replace nulls with a given value, then this is better

ISNULL(OFFICE.OFFICE_ID, 'NY')

The ISNULL function returns the second parameter when the first parameter is null or the value of the first parameter if it is not null.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources