Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stairway to T-SQL: Beyond The Basics Level 6: Using the CASE Expression and IIF Function Expand / Collapse
Author
Message
Posted Friday, April 4, 2014 4:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:06 PM
Points: 1,040, Visits: 277
Comments posted to this topic are about the item Stairway to T-SQL: Beyond The Basics Level 6: Using the CASE Expression and IIF Function

Gregory A. Larsen, MVP

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #1558432
Posted Wednesday, April 9, 2014 6:52 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 532, Visits: 448
Thanks Greg, good stuff! I like the addition of IIF in 2012.
Post #1559916
Posted Wednesday, April 9, 2014 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:28 PM
Points: 20, Visits: 165
Thank you, very helpful.

In a large development, I would use a only on CASE rather than IIF for readability and uniformity.
Post #1559937
Posted Wednesday, April 9, 2014 7:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 26, 2014 7:28 AM
Points: 257, Visits: 902
I was surprised you didn't discuss case inside aggregate functions.

before PIVOT existed we used something like this:
select
entityid
,fieldA = max( case when attribute="A" then value else '' end )
,fieldB = max( case when attribute="B" then value else '' end )
,...
,fieldN = max( case when attribute="N" then value else '' end )
from
EAVtable
group by
entityid

I've also used case for custom group/order by:
order by
case @tcSortField
when "LastName" then person.LastName
when "Email" then person.EmailAddress
etc.
end
Post #1559942
Posted Thursday, April 10, 2014 7:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:37 PM
Points: 2,677, Visits: 215
Just a comment about the simple version that burned me. As stated the simple version is like using the equal sign (=). So you cannot check for NULLS. So you cannot say: CASE variable WHEN NULL THEN... because that's like saying IF variable = NULL THEN... We all know that that won't work. You have to say IF variable IS NULL THEN... So to check for a NULL you can't use the simple version of CASE. You would have to say CASE WHEN variable IS NULL THEN... to make it work.
Post #1560426
Posted Thursday, April 10, 2014 7:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:37 PM
Points: 2,677, Visits: 215
Mike Dougherty-384281 (4/9/2014)
I was surprised you didn't discuss case inside aggregate functions.

before PIVOT existed we used something like this:
select
entityid
,fieldA = max( case when attribute="A" then value else '' end )
,fieldB = max( case when attribute="B" then value else '' end )
,...
,fieldN = max( case when attribute="N" then value else '' end )
from
EAVtable
group by
entityid

I've also used case for custom group/order by:
order by
case @tcSortField
when "LastName" then person.LastName
when "Email" then person.EmailAddress
etc.
end


Yes, I use case in an order by all the time. It works great.
Post #1560429
Posted Thursday, April 10, 2014 3:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:24 AM
Points: 2,351, Visits: 2,700
Thanks for the great article!

I just want to make one small comment. I ran the code below:

SELECT OrderAmt,
IIF(OrderAmt > 200,
'High $ Order',
'Low $ Order') AS OrderType
FROM MyOrder;


and got this error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '>'.

When I Googled the error, it turns out IIF() is not implemented in SQL 2008. I didn't see that noted in the article so it would be helpful to add a note about that at the start of the piece.

But again, this is a fantastic tutorial! Thanks again!

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1560646
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse