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
 CASE simplified
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/26/2013 :  15:49:05  Show Profile  Reply with Quote
Hi guys,

I often write quite complex case statements and wonder if there is a way of avoiding multiple ends as in the below example. (not a complex example but I have used up to 6 ends before now and I am sure there is a way of avoiding this!!!)

select

case when isnull(column1,'') <> ''
then 'A' else
case when isnull(column2,'') <> ''
then 'B' else 'C'
end end as [ExampleCase]

from Wherever




Cheers, Nev.

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/26/2013 :  15:56:52  Show Profile  Visit ditch's Homepage  Reply with Quote
Have you tried using COALESCE?
Less 'ENDs' ....but maybe not less work ;)
SELECT
CASE
WHEN COALESCE(Column1, Column2, '') = Column1 THEN 'A'
WHEN COALESCE(Column1, Column2, '') = Column2 THEN 'B'
ELSE
'C'
END

FROM
Wherever

Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/26/2013 :  16:04:41  Show Profile  Reply with Quote
Not worried about the amount of work involved. It's more about trying to interpret what's going on when you see end end end end end in the middle of an SP.

I'll give it a go - thanks for your suggestion. Never used coalesce before.
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/26/2013 :  16:06:07  Show Profile  Visit ditch's Homepage  Reply with Quote
It works like ISNULL except it's not limited to 2 parameters.

Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/26/2013 :  17:25:59  Show Profile  Reply with Quote
you can write if differently:
select 
	case 
		when column1 NOT NULL then 'A' 
		when column2 NOT NULL then 'B' 
		else 'C' 
	end as [ExampleCase]
from 
	Wherever
Go to Top of Page

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/26/2013 :  19:53:05  Show Profile  Reply with Quote
Lamprey, that's exactly what I was looking for. Tested and understood. That's the end of my multiple ends.

Cheers.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/27/2013 :  01:16:42  Show Profile  Reply with Quote
just an illustration of same scenario using series of replace statements

select 
replace(replace(replace(replace(isnull(column1,'')+ '||' + isnull(column2,''),column1 + '|','A'), '|' + column2,'B'),'|',''),'AB','C') 
FROM table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 06/28/2013 01:10:09
Go to Top of Page

ditch
Flowing Fount of Yak Knowledge

South Africa
1466 Posts

Posted - 06/27/2013 :  02:28:42  Show Profile  Visit ditch's Homepage  Reply with Quote
visakh, you are just showing off now ;)


Duane.
http://ditchiecubeblog.wordpress.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/27/2013 :  03:01:06  Show Profile  Reply with Quote
quote:
Originally posted by ditch

visakh, you are just showing off now ;)


Duane.
http://ditchiecubeblog.wordpress.com/



just thought of giving an alternate method.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

chbala85
Starting Member

49 Posts

Posted - 06/27/2013 :  05:20:29  Show Profile  Reply with Quote
select
case
when (column1 NOT NULL or column2 NOT NULL) then 'A'
else 'B'
end as [ExampleCase]
from
Wherever
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/27/2013 :  05:22:04  Show Profile  Reply with Quote
quote:
Originally posted by chbala85

select
case
when (column1 NOT NULL or column2 NOT NULL) then 'A'
else 'B'
end as [ExampleCase]
from
Wherever



Doesnt match with Op's required output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/27/2013 :  18:33:08  Show Profile  Reply with Quote
Cheers for that Visach.... interesting. I'll try it out but I fear nightmares about getting lost in all those parenthesis! ;-)
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.09 seconds. Powered By: Snitz Forums 2000