problem with case statment - 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

General SQL Server Forums

New to SQL Server Programming

problem with case statment

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2012-11-23 : 12:34:21

Hi All.

I cannot figure out what problem in select


select
Employee_Id
,Position
,Reason
,CASE WHEN i.Insurance = 'OtherInsurance' THEN NULL  end i.Insurance 
From Employees e
left outer join dbo.v_Insurances i
on e.Insurance_cd = i.InsuranceCode

What is my problem?

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-23 : 12:47:54

quote:
Originally posted by eugz

Hi All.

I cannot figure out what problem in select

select
Employee_Id
,Position
,Reason
,CASE WHEN i.Insurance = 'OtherInsurance' THEN NULL end i.Insurance
From Employees e
left outer join dbo.v_Insurances i
on e.Insurance_cd = i.InsuranceCode

What is my problem?

Thanks.

eugz
Posting Yak Master

210 Posts

Posted - 2012-11-23 : 14:22:38

Hi sodeep. Thanks for replay.

When I run select I got error message:
None of the result expressions in a CASE specification can be NULL.

SqlGirl87
Starting Member

26 Posts

Posted - 2012-11-23 : 17:30:57

select
Employee_Id
,Position
,Reason
,CASE WHEN i.Insurance = 'OtherInsurance' THEN convert(nvarchar(4), NULL) end Insurance
From Employees e
left outer join dbo.v_Insurances i
on e.Insurance_cd = i.InsuranceCode

Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-11-23 : 19:05:00

If you have a case expression like shown below, that would be no different from having no case expression at all:

CASE WHEN i.Insurance = 'OtherInsurance' THEN convert(nvarchar(4), NULL) end Insurance

That is because, you are asking SQL Server to return you NULL if the Insurance is "OtherInsurance". But you are not telling it what it should do if the insurance is indeed not "OtherInsurance". And, so it would return a null there as well.

Perhaps this is what you want?

-----------------------
CASE 
     WHEN i.Insurance = 'OtherInsurance' THEN NULL
     ELSE i.Insurance
END Insurance

If that is so, you might consider either of the following as well, which should be functionally the same:

CASE
	WHEN i.Insurance <> 'OtherInsurance' THEN i.Insurance
END Insurance

-----------------------
NULLIF(i.Insurance,'OtherInsurance') Insurance

________________________________________
-- Yes, I am indeed a fictional character.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-11-24 : 10:38:46

If you want to return a null when insurance equals OtherInsurance - this will work:

NULLIF(i.Insurance, 'OtherInsurance') As Insurance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources