SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 problem with case statment
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

eugz
Posting Yak Master

195 Posts

Posted - 11/23/2012 :  12:34:21  Show Profile  Reply with Quote
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.

Edited by - eugz on 11/23/2012 12:44:01

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/23/2012 :  12:47:54  Show Profile  Reply with Quote
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.

Go to Top of Page

eugz
Posting Yak Master

195 Posts

Posted - 11/23/2012 :  14:22:38  Show Profile  Reply with Quote
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.
Go to Top of Page

SqlGirl87
Starting Member

26 Posts

Posted - 11/23/2012 :  17:30:57  Show Profile  Reply with Quote
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
Go to Top of Page

Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/23/2012 :  19:05:00  Show Profile  Reply with Quote
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.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 Posts

Posted - 11/24/2012 :  10:38:46  Show Profile  Reply with Quote
If you want to return a null when insurance equals OtherInsurance - this will work:

NULLIF(i.Insurance, 'OtherInsurance') As Insurance
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000