What does this SQL mean? (case-then-else)

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

What does this SQL mean? (case-then-else)

Author

Topic

yeeting
Starting Member

4 Posts

Posted - 2011-11-11 : 10:33:55

This SQL has a different way of using case-then-else query. Can anyone please let me know what below SQL means.

case when t1.TIER in ("Y1","Y2") then "Y" else
case when t1.TIER = "Y3" then
case when t1.DATE >= '01FEB2001'd then "N" else "Y" end
else
"N" end end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 10:36:42

looks like its a nested case structure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-11 : 10:47:51

it will be much clearer if you format it nicely


case 	when    t1.TIER in ('Y1','Y2') 
	then 	'Y' 
	else
		case 	when    t1.TIER = 'Y3' 
			then
				case 	when 	t1.DATE >= '01FEB2001'
					then 	'N' 
					else 	'Y' 
					end
			else
				'N' 
			end 
	end

KH
[spoiler]Time is always against us[/spoiler]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 10:50:27

[code]
case when t1.TIER in ("Y1","Y2")
then "Y"
else
case when t1.TIER = "Y3"
then
case when t1.DATE >= '01FEB2001'
then "N"
else "Y"
end
else "N"
end
end

same as
case
when t1.TIER in ("Y1","Y2")
then "Y"
when t1.TIER = "Y3" and t1.DATE >= '01FEB2001'
then "N"
when t1.TIER = "Y3" and t1.DATE < '01FEB2001' or t1.TIER is null
then "Y"
else "N"
end
[/code]

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yeeting
Starting Member

4 Posts

Posted - 2011-11-11 : 10:53:03

oh, thanks a lot, khtan!

yeeting
Starting Member

4 Posts

Posted - 2011-11-11 : 10:56:36

nigelrivett, thanks so much for your idea. Your revised SQL is so much clearer. The first SQL was actually written by someone I've just replaced in my new company.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 11:02:58

The original isn't that bad an idea as it avoids the null check (if that column is nullable and that is what's wanted).
I don't like putting else keywords on the same line as the then unless the whole case is a single line.
It pays to follow how you think - so if you think of it as a nested statment code it that way. maybe just a comment at the top to say what it's doing would help future victims.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources