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' elsecase 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'ENDFROM WhereverDuane.http://ditchiecubeblog.wordpress.com/ |
|
|
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. |
|
|
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/ |
|
|
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 |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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/ |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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! ;-) |
|
|
|