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: Wednesday, May 27, 2015 11:47 PM
Points: 1,040, Visits: 281
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: Yesterday @ 2:54 PM
Points: 533, Visits: 457
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: Monday, June 29, 2015 6:47 AM
Points: 21, Visits: 199
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: Friday, August 28, 2015 3:28 PM
Points: 267, Visits: 933
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 3:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:55 PM
Points: 2,622, Visits: 3,152
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
Posted Thursday, January 15, 2015 9:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 2, Visits: 37
Hello,
In your Stairway to T-SQL Beyond The Basics Level 6 Using the CASE Expression and IIF Function covering the Listing 7: Nesting CASE statement lesson. If I was to add to your “MyOrder” table the values;
('01-15-2015', 301.00,NULL), and then run your code, the result for my added value would return. Meaning that if the OrderAmt_Category is Null it would be a layaway. I would think that it should be without layaway.

301.00 NULL 300 Dollar Order with Layaway
USE tempdb
GO
SELECT OrderAmt, Layaway,
CASE
WHEN OrderAmt < 100 THEN '< 100 Dollar Order'
WHEN OrderAmt < 200 THEN '100 Dollar Order'
WHEN OrderAmt < 300 THEN
CASE
WHEN Layaway = 'N'
THEN '200 Dollar Order without Layaway'
ELSE '200 Dollar Order with Layaway' END
ELSE
CASE
WHEN Layaway = 'N'
THEN '300 Dollar Order without Layaway'
ELSE '300 Dollar Order with Layaway' END
END AS OrderAmt_Category
FROM MyOrder
order by OrderAmt;

-----------Reorder/Change the 2nd and 3rd CASE statements to accomodate the "Null" values correctly
USE tempdb
GO
SELECT OrderAmt, Layaway,
CASE
WHEN OrderAmt < 100 THEN '< 100 Dollar Order'
WHEN OrderAmt < 200 THEN '100 Dollar Order'
WHEN OrderAmt < 300 THEN
CASE
WHEN Layaway = 'Y'
THEN '200 Dollar Order with Layaway'
ELSE '200 Dollar Order without Layaway' END
ELSE
CASE
WHEN Layaway = 'Y'
THEN '300 Dollar Order with Layaway'
ELSE '300 Dollar Order without Layaway' END
END AS OrderAmt_Category
FROM MyOrder
order by OrderAmt;

Great article and very helpful
Post #1651616
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse