http://www.sqlservercentral.com/articles/Stairway+Series/108723/
This article is part of the Stairway Series: Stairway to T-SQL: Beyond The Basics
Following on from his Stairway to T-SQL DML, Gregory Larsen covers more advanced aspects of the T-SQL language such as subqueries.
Following on from his Stairway to T-SQL DML, Gregory Larsen covers more advanced aspects of the T-SQL language such as subqueries.
There are times where you need to write a single TSQL statement that
is able to return different TSQL expressions based on the evaluation of
another expression. When you need this kind of functionality you can use
the CASE expression or IIF function to meet this requirement. In this
article I will be reviewing the CASE and IIF syntax and showing you
examples of how the CASE expression and IIF function.
Regardless of which CASE format is used, each WHEN clause is compared in the order in which it appears. The result of the CASE expression will be based on the first WHEN clause that evaluates to TRUE. If no WHEN clause evaluates to TRUE then the ELSE expression is returned. When the ELSE clause is omitted and no WHEN clause evaluates to TRUE, then a NULL value is returned.
When my simple CASE expression is evaluated it uses the equality operator (“=”) between the “YEAR(OrderDate)” value and the different WHEN expressions. Therefore the code in Listing 1 is going to display “Year 1” for the YearType column for rows with an OrderDT year value of “2014”, or it will display “Year 2” for rows with an OrderDT year of “2013” or it will display “Year 3” for rows with an OrderDT year of “2012”. If the year of the OrderDT doesn’t match any of the WHEN expressions then the ELSE condition will display “Year 4 and beyond”.
When I run the code in Listing 2 I get the output shown in Result 1.
To demonstrate this let’s use the CASE expression to display “200 dollar Order” when OrderAmt is within the $200 range, “100 Dollar Order” when the OrderAmt is within the $100 range and “< 100 Dollar Order” when the OrderAmt is less than $100When an OrderAmt doesn’t fall into any of these categories then categorize the order as a “300 Dollar and above Order”. Let’s review the code in Listing 5 to demonstrate what happens when multiple WHEN expressions evaluate to TRUE when trying to categorize orders into one of these OrderAmt_Category values.
By re-ordering my WHEN clauses I can get the results I want. The code in Listing 6 is the same as Listing 5 but I have re-ordered the WHEN clauses to correctly categorized my orders.
The re-written code that uses an IIF function instead of a CASE expression can be found in Listing 10.
CASE Expression Limitations:
Understanding the CASE Expression
The Transact-SQL CASE expression allows you to place conditional logic in your TSQL code. This conditional logic provides you with a way to place different code blocks in your TSQL statements which can be executed depending on a TRUE or FALSE evaluation of the conditional logic. You can place multiple conditional expressions in a single CASE expression. When you have multiple conditional expressions in your CASE clause, the first expression that evaluates to TRUE will be the code block that is evaluated by your TSQL statement. To better understand how the CASE expression works I will review the syntax of the CASE expression and then go through a number of different examples.CASE Expression Syntax
The CASE expression has two different formats: Simple and Searched. Each of these types has a slightly different format as shown in Figure 1.Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Searched CASE expression:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
Figure 1: CASE expression syntax
By reviewing the two different formats for the CASE expression in
Figure 1 you can see how each format provides a different way to
identify one of multiple expressions that determine the results of the
CASE expression. With both types of CASE, a Boolean test is performed
for each WHEN clause. With the Simple CASE expression the left hand side
of the Boolean test appears right after the CASE word and is called the
“input_expression”, and right hand sided of the Boolean test is right
after word WHEN and is called the “when expression”. With the Simple
CASE expression the operator between the “input_expression” and the
“when_expression” is always the equality operator. Whereas with the
searched CASE expression each WHEN clause will contain a
“Boolean_expression”. This “Boolean_expression” can be a simple Boolean
expression with a single operator, or a complex Boolean expression with
many different conditions. In addition, the searched CASE expression can
use the full set of Boolean operators.
Regardless of which CASE format is used, each WHEN clause is compared in the order in which it appears. The result of the CASE expression will be based on the first WHEN clause that evaluates to TRUE. If no WHEN clause evaluates to TRUE then the ELSE expression is returned. When the ELSE clause is omitted and no WHEN clause evaluates to TRUE, then a NULL value is returned.
Sample Data for Examples
In order to have a table to demonstrate using the CASE expression I will be using the script in Listing 1 to create a sample table named MyOrder. If you would like to follow along with my examples and run them on your SQL Server instance you can create this table in a database of your choice.CREATE TABLE MyOrder (
ID int identity,
OrderDT date,
OrderAmt decimal(10,2),
Layaway char(1));
INSERT into MyOrder VALUES
('12-11-2012', 10.59,NULL),
('10-11-2012', 200.45,'Y'),
('02-17-2014', 8.65,NULL),
('01-01-2014', 75.38,NULL),
('07-10-2013', 123.54,NULL),
('08-23-2009', 99.99,NULL),
('10-08-2013', 350.17,'N'),
('04-05-2010', 180.76,NULL),
('03-27-2011', 1.49, NULL);
Listing 1: Create sample table MyOrder
Using a Simple CASE expression with WHEN and ELSE expressions
To demonstrate how the simple CASE expression format works let me run the code in Listing 2.SELECT YEAR(OrderDT) AS OrderYear,
CASE YEAR(OrderDT)
WHEN 2014 THEN 'Year 1'
WHEN 2013 THEN 'Year 2'
WHEN 2012 THEN 'Year 3'
ELSE 'Year 4 and beyond' END AS YearType
FROM MyOrder;
Listing 2: Simple CASE expression with ELSE expression
Let me first talk about why this is a Simple CASE expression. If you
review the code in Listing 2 you can see that right after the word CASE I
specified the expression “YEAR(OrderDT)”,
and then I followed that by three different WHEN expressions each one
having a different year specified, starting with 2014. Because I
specified that expression between the CASE and the first WHEN keyword
this tell SQL Server that this is a simple CASE expression.
When my simple CASE expression is evaluated it uses the equality operator (“=”) between the “YEAR(OrderDate)” value and the different WHEN expressions. Therefore the code in Listing 1 is going to display “Year 1” for the YearType column for rows with an OrderDT year value of “2014”, or it will display “Year 2” for rows with an OrderDT year of “2013” or it will display “Year 3” for rows with an OrderDT year of “2012”. If the year of the OrderDT doesn’t match any of the WHEN expressions then the ELSE condition will display “Year 4 and beyond”.
When I run the code in Listing 2 I get the output shown in Result 1.
OrderYear YearType ----------- ----------------- 2012 Year 3 2012 Year 3 2014 Year 1 2014 Year 1 2013 Year 2 2009 Year 4 and beyond 2013 Year 2 2010 Year 4 and beyond 2011 Year 4 and beyond
Result 1: Results when running Listing 2
Using a Simple CASE expression without an ELSE expression
Let me run the code in Listing 3 which will show what happens when a Simple CASE expression doesn’t have an ELSE clause.SELECT YEAR(OrderDT) AS OrderYear,
CASE YEAR(OrderDT)
WHEN 2014 THEN 'Year 1'
WHEN 2013 THEN 'Year 2'
WHEN 2012 THEN 'Year 3' END AS YearType
FROM MyOrder;
Listing 3: Simple CASE expression without ELSE clause
The code in Listing 3 is just like code in Listing 2 but without an
ELSE clause. When I run the code in Listing 3 it produces the results
shown in Result 2.
OrderYear YearType ----------- -------- 2012 Year 3 2012 Year 3 2014 Year 1 2014 Year 1 2013 Year 2 2009 NULL 2013 Year 2 2010 NULL 2011 NULL
Result 2: Results when running Listing 3
By reviewing the output in Result 2 you can see that when the year of the OrderDT in the MyOrder table doesn’t meet any of the WHEN clause conditions SQL Server displays “NULL” for the YearType value for that row.
Using a Searched CASE expression
In the simple CASE expression the WHEN expressions were evaluated based on equality operator. With the searched CASE expressions you can use other operators, and the CASE expression syntax is a little different. To demonstrate this let’s look at the code in Listing 4.SELECT YEAR(OrderDT) AS OrderYear,
CASE
WHEN YEAR(OrderDT) = 2014 THEN 'Year 1'
WHEN YEAR(OrderDT) = 2013 THEN 'Year 2'
WHEN YEAR(OrderDT) = 2012 THEN 'Year 3'
WHEN YEAR(OrderDT) < 2012 THEN 'Year 4 and beyond'
END AS YearType
FROM MyOrder;
Listing 4: Searched CASE expression
If you look at the code in Listing 4 you can see that the WHEN clause
follows directly after the CASE clause with no text between the two
clauses. This tells SQL Server this a searched CASE expression. Also
note the Boolean expression following each WHEN clause. As you can see
not all those Boolean expressions are using the equality operator, the
last WHEN expression uses the less than (“<”) operator. The CASE
expression in Listing 4 is logically the same as the CASE expression in
Listing 2. Therefore when I run the code in Listing 4 it produces the
same results as shown in Result 1.
What expression is returned if multiple WHEN expressions evaluate to TRUE?
There may be situations where different WHEN expressions evaluate to TRUE in a single CASE expression. When this happens SQL Server will return the result expression associated with the first WHEN expression that evaluates to true. Therefore the order of your WHEN clauses will control what result you get returned from your CASE expression if multiple WHEN clauses evaluate to TRUE.To demonstrate this let’s use the CASE expression to display “200 dollar Order” when OrderAmt is within the $200 range, “100 Dollar Order” when the OrderAmt is within the $100 range and “< 100 Dollar Order” when the OrderAmt is less than $100When an OrderAmt doesn’t fall into any of these categories then categorize the order as a “300 Dollar and above Order”. Let’s review the code in Listing 5 to demonstrate what happens when multiple WHEN expressions evaluate to TRUE when trying to categorize orders into one of these OrderAmt_Category values.
SELECT OrderAmt,
CASE
WHEN OrderAmt < 300 THEN '200 Dollar Order'
WHEN OrderAmt < 200 THEN '100 Dollar Order'
WHEN OrderAmt < 100 THEN '< 100 Dollar Order'
ELSE '300 Dollar and above Order'
END AS OrderAmt_Category
FROM MyOrder;
Listing 5: Example where multiple WHEN expressions evaluate to TRUE
When I run the code in Listing 5 I get the output in Result 3.
OrderAmt OrderAmt_Category --------------------------------------- -------------------------- 10.59 200 Dollar Order 200.45 200 Dollar Order 8.65 200 Dollar Order 75.38 200 Dollar Order 123.54 200 Dollar Order 99.99 200 Dollar Order 350.17 300 Dollar and above Order 180.76 200 Dollar Order 1.49 200 Dollar Order
Result 3: Results when running Listing 5
By reviewing the results in Result 3 you can see that every order is
reported to be either a 200 or a 300 and above order, and we know this
is incorrect. This happened because I only used the less than (“<”)
operator to simplistically categorize Orders which lead to multiple WHEN
expressions evaluating to TRUE in my CASE expression. The ordering of
the WHEN clauses did not allow the correct expression to be returned.
By re-ordering my WHEN clauses I can get the results I want. The code in Listing 6 is the same as Listing 5 but I have re-ordered the WHEN clauses to correctly categorized my orders.
SELECT OrderAmt,
CASE
WHEN OrderAmt < 100 THEN '< 100 Dollar Order'
WHEN OrderAmt < 200 THEN '100 Dollar Order'
WHEN OrderAmt < 300 THEN '200 Dollar Order'
ELSE '300 Dollar and above Order'
END AS OrderAmt_Category
FROM MyOrder;
Listing 6: Similar code as Listing 5, but with WHEN clauses are in a different order
When I run the code in Listing 5 I get the output in Result 4.
OrderAmt OrderAmt_Category --------------------------------------- -------------------------- 10.59 < 100 Dollar Order 200.45 200 Dollar Order 8.65 < 100 Dollar Order 75.38 < 100 Dollar Order 123.54 100 Dollar Order 99.99 < 100 Dollar Order 350.17 300 Dollar and above Order 180.76 100 Dollar Order 1.49 < 100 Dollar Order
Result 4: Results when running Listing 6
By reviewing the output in Result 4, you can see that by changing the
order of the WHEN expressions I got the correct results for each order.
Nesting CASE expressions
Occasionally you might have a need to do additional testing to further categorize data using the CASE expression. When that occurs you can use a nested CASE expression. The code in Listing 7 shows an example of nesting the CASE expression to further categorize orders in the MyOrder table to determine if the order was purchased using the Layaway value when an order is over $200.SELECT OrderAmt,
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;
Listing 7: Nesting CASE statement
The code in Listing 7 is similar to the code in Listing 6. The only
difference is I added an additional CASE expression to see if an order
in MyOrder table was purchased using the Layaway option, which
is only allowed on purchases over $200. Keep in mind when you nest CASE
expressions SQL Server only allow you to have up to 10 levels of
nesting.
Other Places where the CASE expression can be used
So far, all of my examples used the CASE expression to create a result string by placing the CASE expression in the select list of a TSQL SELECT statement. You can also use a CASE expression in an UPDATE, DELETE and SET statement. Additionally the CASE expression can be used in conjunction with the IN, WHERE, ORDER BY and HAVING clauses. In Listing 8 I am using a CASE expressing a WHERE clause.SELECT * FROM MyOrder WHERE CASE YEAR(OrderDT) WHEN 2014 THEN 'Year 1' WHEN 2013 THEN 'Year 2' WHEN 2012 THEN 'Year 3' ELSE 'Year 4 and beyond' END = 'Year 1';
Listing 8: Using CASE expression in a WHERE clause
In Listing 8 I only want to return an order from the MyOrder
table for rows in “Year 1”. To accomplish this I place the same CASE
expression as I used in Listing 2 in the WHERE clause. I used the CASE
expression as the left-hand part of the WHERE condition so it would
produce different “Year…” strings based on the OrderDT column. I
then tested the string that was produced from the CASE expression to
see if it was equal to the value “Year 1”, when it was a row would be
returned from MyOrder table. Keep in mind I would not recommend
use a CASE expression to select date from a date column using a sting
like “Year 1”, when there are other better methods like using the YEAR
function to select rows for a given year. I have only done it here to
demonstrate how to use a CASE statement in a WHERE clause.
Shortcutting the CASE Expression using the IIF Function
With the introduction of SQL Server 2012, Microsoft added the IIF function. The IIF function can be considered a shortcut to the CASE statement. In Figure 2 you can find the syntax for the IIF function.IIF ( boolean_expression, true_value, false_value )
Figure 2: Syntax for the IIF function
The “Boolean_expression” is a valid Boolean expression that equates to
TRUE or FALSE. When the Boolean expression equates to a TRUE value then
the “true_value” expression is executed. If the Boolean expression
equates to FALSE then the “false_value” is executed. Just like the CASE
expression the IIF function can be nested up to 10 levels.
Example of Using IIF
To demonstrate how the IIF function can be used to replace the CASE expression, let’s review the code that uses a searched CASE expression in Listing 9.SELECT OrderAmt,
CASE
WHEN OrderAmt > 200 THEN 'High $ Order'
ELSE 'Low $ Order' END AS OrderType
FROM MyOrder;
Listing 9: Simple CASE expression example
The code in Listing 9 has just a single WHEN expression that is used to determine if the OrderAmt is either a high or low dollar order. If the WHEN expression “OrderAMT > 200” evaluates to TRUE then the OrderType value is set to “High $ Order”. If the WHEN expression evaluates to FALSE then “Low $ Order” is set for the OrderType value.
The re-written code that uses an IIF function instead of a CASE expression can be found in Listing 10.
SELECT OrderAmt, IIF(OrderAmt > 200, 'High $ Order', 'Low $ Order') AS OrderType FROM MyOrder;
Listing 10: Example using the IIF function
By looking at Listing 10 you can see why the IIF function is
considered a shorthand version of the CASE expression. The word CASE is
replaced with the “IIF(“ string, the “THEN” clause is replaced with a
comma, the “ELSE” clause is replaced with a comma and the word “END” is
replaced with a “)”. When the Boolean expression “OrderAmt > 200” is TRUE the value “High $ Order” is displayed. When the Boolean expression ‘OrderAmt > 200”
is evaluated to FALSE then the “Low $ Order” is displayed. If you run
the code in Listing 9 and 10 you will see they both produce the exact
same output.
Example of Nesting the IIF Function
Just like the CASE expression SQL Server allows you to nest IIF functions. In Listing 11 is an example of nesting the IIF function.SELECT OrderAmt,
IIF (OrderAmt < 100,
'< 100 Dollar Order',
(IIF (OrderAmt < 200,
'100 Dollar Order',
(IIF (OrderAmt < 300,
(IIF (Layaway = 'N',
'200 Dollar Order without Layaway',
'200 Dollar Order with Layaway'
)
),
(IIF (Layaway = 'N',
'300 Dollar Order without Layaway',
'300 Dollar Order with Layaway'
)
)
)
)
)
)
) AS OrderAmt_Category
FROM MyOrder;
Listing 11: Example of nesting of an IIF function
In this example you can see that I have used the IIF function multiple
times. Each additional one is either used in the “true value” or the
“false value” of the IIF function. The code in Listing 11 is equivalent
to the code that uses the nested CASE expression in Listing 7.
Limitations
As with most TSQL functionality there are limitations. Below are some limitations to note regarding the CASE and IIF constructs.CASE Expression Limitations:
- You can only have up to 10 levels of nesting in CASE expressions.
- CASE expressions cannot be used to control the flow of execution of TSQL statements.
- You can only have up to 10 levels of nesting of the IIF clause.
Summary
The CASE expression and IIF function allow you to place expression logic within TSQL code that will change the results of your code based on the evaluated result of an expression. By using the comparison expression supported by the IIF function and the CASE expression you can have different code blocks executed depending on whether the comparison expression evaluates to TRUE or FALSE. The CASE expression and the IIF function provide you programmatic control to meet business requirements that you might not otherwise have.Question and Answer
In this section you can review how well you have understood using the CASE and IIF constructs by answering the following questions.Question 1:
There are two different syntax variations for the CASE expression: Simple and Searched. Which two statements below best describe the difference between a Simple and Searched CASE expression (Pick two).- The Simple CASE syntax only supports the equality operator, whereas the Searched CASE syntax supports multiple operators
- The Simple CASE syntax supports multiple operators, whereas the Searched CASE syntax supports only the equality operator
- The Simple CASE syntax has its Boolean expression specified after the WHEN clause, whereas the Searched CASE syntax has the left side of the Boolean expression right after the CASE statement, and the right side of the Boolean expression after the WHEN clause.
- The Simple CASE syntax has the left side of the Boolean expression right after the CASE statement and the right side of the Boolean expression after the WHEN clause, whereas the Searched CASE expression has its Boolean expression after the WHEN clause
Question 2:
If the CASE expression has multiple WHEN clauses that evaluate to TRUE, which THEN/ELSE clause is executed?- The THEN expression of the last WHEN clause that evaluates to TRUE is executed.
- The THEN expression of the first WHEN clause that evaluates to TRUE is executed.
- All THEN expressions of the WHEN clauses that evaluates to TRUE are executed.
- THE ELSE expression is executed
Question 3:
How many nesting levels can a CASE expression or IIF function have?- 8
- 10
- 16
- 32
No comments:
Post a Comment