Returning CASE as Null problem

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 2005 Forums

Transact-SQL (2005)

Returning CASE as Null problem

Author

Topic

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-06 : 15:40:39

Hello again! I'm having yet another problem with my code.


CASE Kit WHEN 'Y' THEN '(Kit)' WHEN 'N' THEN '' END + ' ' + 
CASE cream WHEN 'Y' THEN '(cream)' WHEN ' N' THEN '' END + ' ' + 
CASE Phone WHEN 'Y' THEN '(Phone)' WHEN 'N' THEN '' END

The problem that I am running into is that if one of the values comes up as NULL the whole field is NULL. am i doing something wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 15:44:55

Use COALESCE function or CONCAT_NULL_YIELDS_NULL database option to handle NULL data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-06 : 16:09:48

K i've read the MSDN article and several others but I can't seem to get it to work with the code i have right now. Some examples forego the CASE and puts the values in ().

LOL i can feel all your eyes rolling....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 16:16:37

I don't know what you mean. Did you try putting COALESCE around your columns? Did you try the database setting I mentioned?

Show us what you tried.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-06 : 16:41:48

OHHHH okay, i think here is the problem. I'm trying to join the result of the 'CASE' not the Y and N. I was putting

wait omg this makes no sense.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 16:48:55

The COALESCE goes around the columns that can contain NULL data. So in your original post, it'd be around Kit, cream, and phone. That is, if they allow NULLs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-06 : 16:57:06

that's the problem I think. It's not an actual NULL value it's a Yes or No value represented by Y or N. The Y and N are cased into a meaningful description that can apply to the multiple products.

Product 1 is (Kit) (Cream)
Product 2 is (kit) (Phone)
Prodcut 3 is (Phone)

I just want it to have no value if there is an 'N' in the database. But it looks like if there is an 'N' in between two Y's that the result feild comes up as NULL.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 16:59:12

The problem is with NULL data and not Y or N.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-06 : 17:58:27

NULLS aside, using a CASE statement like that appears to only work as expected if you include the ELSE clause. Here are some different ways to do it:

DECLARE @Yak TABLE (Kit CHAR(1), Cream CHAR(1), Phone CHAR(1))

INSERT @Yak
SELECT 'Y', 'Y', 'Y'
UNION ALL SELECT 'Y', 'Y', 'N'
UNION ALL SELECT 'Y', 'N', 'Y'
UNION ALL SELECT 'N', 'Y', 'Y'
UNION ALL SELECT 'Y', 'N', 'N'
UNION ALL SELECT 'N', 'N', 'N'
UNION ALL SELECT 'Y', NULL, 'N'
UNION ALL SELECT NULL, 'N', 'Y'
UNION ALL SELECT 'Y', 'Y', NULL

-- Wierd
SELECT 
	CASE Kit WHEN 'Y' THEN '(Kit)' WHEN 'N' THEN '' END + ' ' + 
	CASE cream WHEN 'Y' THEN '(cream)' WHEN ' N' THEN '' END + ' ' + 
	CASE Phone WHEN 'Y' THEN '(Phone)' WHEN 'N' THEN '' END
FROM @Yak

-- Works if there are not any NULLS
SELECT 
	CASE Kit WHEN 'Y' THEN '(Kit)' WHEN 'N' THEN '' ELSE '' END + ' ' + 
	CASE cream WHEN 'Y' THEN '(cream)' WHEN ' N' THEN '' ELSE '' END + ' ' + 
	CASE Phone WHEN 'Y' THEN '(Phone)' WHEN 'N' THEN '' ELSE '' END
FROM @Yak

-- Also works if there are not any NULLS
SELECT 
	CASE WHEN Kit = 'Y' THEN '(Kit)' ELSE '' END + ' ' + 
	CASE WHEN cream = 'Y' THEN '(cream)' ELSE '' END + ' ' + 
	CASE WHEN Phone  = 'Y' THEN '(Phone)' ELSE ''END
FROM @Yak

-- If you can have NULLs then you should COALESCE
SELECT 
	CASE COALESCE(Kit, 'N') WHEN 'Y' THEN '(Kit)' WHEN 'N' THEN '' ELSE '' END + ' ' + 
	CASE COALESCE(cream, 'N') WHEN 'Y' THEN '(cream)' WHEN ' N' THEN '' ELSE '' END + ' ' + 
	CASE COALESCE(Phone, 'N') WHEN 'Y' THEN '(Phone)' WHEN 'N' THEN '' ELSE '' END
FROM @Yak

-- OR
SELECT 
	CASE WHEN COALESCE(Kit, 'N') = 'Y' THEN '(Kit)' ELSE '' END + ' ' + 
	CASE WHEN COALESCE(cream, 'N') = 'Y' THEN '(cream)' ELSE '' END + ' ' + 
	CASE WHEN COALESCE(Phone, 'N')  = 'Y' THEN '(Phone)' ELSE ''END
FROM @Yak

wndrboy2k3
Starting Member

37 Posts

Posted - 2008-05-06 : 18:17:07

Okay all your examples worked. I just want to understand what is going on so i learn from this. What is the difference between the CASE WHEN COALESCE... and using CASE COALESCE(Phone, 'N') WHEN

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-06 : 18:29:31

or directly:

Coalesce(CASE Kit WHEN 'Y' THEN '(Kit)' WHEN 'N' THEN '' END,' ') + ' ' +
Coalesce(CASE cream WHEN 'Y' THEN '(cream)' WHEN ' N' THEN '' END,' ') + ' ' +
Coalesce(CASE Phone WHEN 'Y' THEN '(Phone)' WHEN 'N' THEN '' END,' ')

It replaces null values with assigned value like ' ' in this case

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-06 : 18:54:04

other than the original case function not working as expected without the ELSE. They are very similar. One is called a Simple Case Function and the other is called a Searched Case Function. It is probably hard to tell much of a difference is the samples I provided as these case functions are pretty simple. I'd suggest you look at BOL as it shows both ways to use the case function and why you might choose one way over the othter. For the most part I use the Searched Case Function as I'm usually doing something where I need to evalute boolean expressions:

CASE
	WHEN T.Reveune <= $100.00 AND T.CustomerType = 5 THEN 1
	WHEN T.Reveune > $100.00 AND T.CustomerType = 5 THEN 2
	ELSE 3
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources