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
 CASE simplified

Author  Topic 

nevzab
Starting Member

34 Posts

Posted - 2013-06-26 : 15:49:05
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
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-26 : 15:56:52
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

34 Posts

Posted - 2013-06-26 : 16:04:41
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
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-26 : 16:06:07
It works like ISNULL except it's not limited to 2 parameters.

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-26 : 17:25:59
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

34 Posts

Posted - 2013-06-26 : 19:53:05
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

52326 Posts

Posted - 2013-06-27 : 01:16:42
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
Go to Top of Page

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2013-06-27 : 02:28:42
visakh, you are just showing off now ;)


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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 03:01:06
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 - 2013-06-27 : 05:20:29
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

52326 Posts

Posted - 2013-06-27 : 05:22:04
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

34 Posts

Posted - 2013-06-27 : 18:33:08
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
   

- Advertisement -