Transact-SQL User's Guide
|Chapter 13 Using Batches and Control-of-Flow Language|
|Using control-of-flow language|
case expression simplifies many conditional Transact-SQL constructs. Instead of using a series of if statements, case expression allows you to use a series of conditions that return the appropriate values when the conditions are met. case expression is ANSI SQL92 compliant.
With case expression, you can:
Simplify queries and write more efficient code
Convert data between the formats used in the database (such as int) and the format used in an application (such as char)
Return the first non-null value in a list of columns
Compare two values and return the first value if the values do not match, or a NULL value if the values do match
Write queries that avoid division by 0
case expression includes the keywords case, when, then, coalesce, and nullif. coalesce and nullif are an abbreviated form of case expression. For details on case expression syntax, see the Reference Manual.Using case expression for alternative representation
Using case expression you can represent data in a manner that is more meaningful to the user. For example, the pubs2 database stores a 1 or a 0 in the contract column of the titles table to indicate the status of the book's contract. However, in your application code or for user interaction, you may prefer to use the words "Contract" or "No Contract" to indicate the status of the book. To select the type from the titles table using the alternative representation:
select title, "Contract Status" = case when contract = 1 then "Contract" when contract = 0 then "No Contract" end from titles
title Contract Status ----- --------------- The Busy Executive's Database Guide Contract Cooking with Computers: Surreptitio Contract You Can Combat Computer Stress! Contract . . . The Psychology of Computer Cooking No Contract . . . Fifty Years in Buckingham Palace Contract Sushi, Anyone? Contract (18 rows affected)case and division by zero
case expression allows you to write queries that avoid division by zero (called exception avoidance). For example, if you attempt to divide the total_sales column for each book by the advance column, the query results in division by zero when the query attempts to divide the total_sales (2032) of title_id MC2222 by the advance (0.00):
select title_id, total_sales, advance, total_sales/advance from titles
title_id total_sales advance ------- ----------- --------- ------ BU1032 4095 5,000.00 0.82 BU1111 3876 5,000.00 0.78 BU2075 18722 10,125.00 1.85 BU7832 4095 5,000.00 0.82 Divide by zero occurred.
You can use a case expression to avoid this by not allowing the zero to figure in the equation. In this example, when the query comes across the zero, it returns a predefined value, rather than performing the division:
select title_id, total_sales, advance, "Cost Per Book" = case when advance != 0 then convert(char, total_sales/advance) else "No Books Sold" end from titles
title_id total_sales advance Cost Per Book -------- ----------- ---------- ------------- BU1032 4095 5,000.00 0.82 BU1111 3876 5,000.00 0.78 BU2075 18722 10,125.00 1.85 BU7832 4095 5,000.00 0.82 MC2222 2032 0.00 No Books Sold MC3021 22246 15,000.00 1.48 MC3026 NULL NULL No Books Sold . . . TC3218 375 7,000.00 0.05 TC4203 15096 4,000.00 3.77 TC7777 4095 8,000.00 0.51 (18 rows affected)
The division by zero for title_id MC2222 no longer prevents the query from running. Also, the null values for MC3021 do not prevent the query from running.Using rand() functions in case expressions
Expressions that reference the rand function, the getdate function, and so on, produce different values each time they are evaluated. This can yield unexpected results when you use these expressions in certain case expressions. For example, the SQL standard specifies that case expressions with the form:
case expression when value1 then result1 when value2 then result2 when value3 then result3 ... end
are equivalent to the following form of case expression:
case expression when expression=value1 then result1 when expression=value2 then result2 when expression=value3 then result3 ... end
This definition explicitly requires that the expression be evaluated repeatedly in each when clause that is examined. This definition of case expressions affects case expressions that reference functions such as the rand function. For example, the following case expression:
select CASE convert(int, (RAND() * 3)) when 0 then "A" when 1 then "B" when 2 then "C" when 3 then "D" else "E" end
is defined to be equivalent to the following according to the SQL standard:
select CASE when convert(int, (RAND() * 3)) = 0 then "A" when convert(int, (RAND() * 3)) = 1 then "B" when convert(int, (RAND() * 3)) = 2 then "C" when convert(int, (RAND() * 3)) = 3 then "D" else "E" end
In this form, a new rand value is generated for each when clause, and the case expression frequently produce the result "E".case expression results
The rules for determining the datatype of a case expression are based on the same rules that determine the datatype of a column in a union operation. A case expression has a series of alternative result expressions (R1, R2, ..., Rn in the example below) which are specified by the then and else clauses. For example:
case when search_condition1 then R1 when search_condition2 then R2 ... else Rn end
The datatypes of the result expressions R1, R2, ..., Rn are used to determine the overall datatype of case. The same rules that determine the datatype of a column of a union that specifies n tables, and has the expressions R1, R2, ..., Rn as the ith column, also determine the datatype of a case expression. The datatype of case is determined in the same manner as would be determined by the following query:
select...R1...from ... union select...R2...from... union... ... select...Rn...from...
Not all datatypes are compatible, and if you specify two datatypes that are incompatible (for example, char and int), your Transact-SQL query will fail. For more information about the union-datatype rules, see the Reference Manual.case expression requires at least one non-null result
At least one result from the case expression must return a value other than null. The following query:
select price, case when title_id like "%" then NULL when pub_id like "%" then NULL end from titles
returns the error message:
All result expressions in a CASE expression must not be NULLcase
Using case expression, you can test for conditions that determine the result set.
The syntax is:
case when search_condition1 then result1 when search_condition2 then result2 . . . when search_conditionn then resultn else resultx end
where search_condition is a logical expression, and result is an expression.
If search_condition1 is true, the value of case is result1; if search_condition1 is not true, search_condition2 is checked. If search_condition2 is true, the value of case is result2, and so on. If none of the search conditions are true, the value of case is resultx. The else clause is optional. If it is not used, the default is else NULL. end indicates the end of the case expression.
The total sales of each book for each store are kept in the salesdetail table. If you want to show a series of ranges for the book sales, you could track how each book sold at each store, using the following ranges:
Books that sold less than 1000 (low-selling books)
Books that sold between 1000 and 3000 (medium-selling books)
Books that sold more than 3000 (high-selling books)
You would write the following query:
select stor_id, title_id, qty, "Book Sales Catagory" = case when qty < 1000 then "Low Sales Book" when qty >= 1000 and qty <= 3000 then "Medium Sales Book" when qty > 3000 then "High Sales Book" end from salesdetail group by title_id
stor_id title_id qty Book Sales Catagory ------- -------- ---- ------------------ 5023 BU1032 200 Low Sales Book 5023 BU1032 1000 Low Sales Book 7131 BU1032 200 Low Sales Book . . . 7896 TC7777 75 Low Sales Book 7131 TC7777 80 Low Sales Book 5023 TC7777 1000 Low Sales Book 7066 TC7777 350 Low Sales Book 5023 TC7777 1500 Medium Sales Book 5023 TC7777 1090 Medium Sales Book (116 rows affected)
The following example selects the titles from the titleauthor table according to the author's royalty percentage (royaltyer) and then assigns each title with a value of High, Medium, or Low royalty:
select title, royaltyper, "Royalty Category" = case when (select avg(royaltyper) from titleauthor tta where t.title_id = tta.title_id) > 60 then "High Royalty" when (select avg(royaltyper) from titleauthor tta where t.title_id = tta.title_id) between 41 and 59 then "Medium Royalty" else "Low Royalty" end from titles t, titleauthor ta where ta.title_id = t.title_id order by title
title royaltyper royalty Category ------- ---------- ---------------- But Is It User Friendly? 100 High Royalty Computer Phobic and Non-Phobic Ind 25 Medium Royalty Computer Phobic and Non-Phobic Ind 75 Medium Royalty Cooking with Computers: Surreptiti 40 Medium Royalty Cooking with Computers: Surreptiti 60 Medium Royalty Emotional Security: A New Algorith 100 High Royalty . . . Sushi, Anyone? 40 Low Royalty The Busy Executive's Database Guide 40 Medium Royalty The Busy Executive's Database Guide 60 Medium Royalty The Gourmet Microwave 75 Medium Royalty You Can Combat Computer Stress! 100 High Royalty (25 rows affected)case and value comparisons
This form of case is used for value comparisons. It allows only an equality check between two values; no other comparisons are allowed.
The syntax is:
case valueT when value1 then result1 when value2 then result2 . . . when valuen then resultn else resultx end
where value and result are expressions.
If valueT equals value1, the value of the case is result1. If valueT does not equal value1, valueT is compared to value2. If valueT equals value2, then the value of the case is result2, and so on. If valueT does not equal the value of value1 through valuen, the value of the case is resultx.
At least one result must be non-null. All the result expressions must be compatible. Also, all values must be compatible.
The syntax described above is equivalent to:
case when valueT = value1 then result1 when valueT = value2 then result2 . . . when valueT = valuen then resultn else resultx end
This is the same format used for case and search conditions (see "case" for more information about this syntax).
The following example selects the title and pub_id from the titles table and specifies the publisher for each book based on the pub_id:
select title, pub_id, "Publisher" = case pub_id when "0736" then "New Age Books" when "0877" then "Binnet & Hardley" when "1389" then "Algodata Infosystems" else "Other Publisher" end from titles order by pub_id
title pub_id Publisher ----- ------ ------------- Life Without Fear 0736 New Age Books Is Anger the Enemy? 0736 New Age Books You Can Combat Computer 0736 New Age Books . . . Straight Talk About Computers 1389 Algodata Infosystems The Busy Executive's Database 1389 Algodata Infosystems Cooking with Computers: Surre 1389 Algodata Infosystems (18 rows affected)
This is equivalent to the following query, which uses a case and search condition syntax:
select title, pub_id, "Publisher" = case when pub_id = "0736" then "New Age Books" when pub_id = "0877" then "Binnet & Hardley" when pub_id = "1389" then "Algodata Infosystems" else "Other Publisher" end from titles order by pub_idcoalesce
coalesce examines a series of values (value1, value2, ..., valuen) and returns the first non-null value. The syntax of coalesce is:
coalesce(value1, value2, ..., valuen)
Where value1, value2, ..., valuen are expressions. If value1 is non-null, the value of coalesce is value1; if value1 is null, value2 is examined, and so on. The examination continues until a non-null value is found. The first non-null value becomes the value of coalesce.
When you use coalesce, Adaptive Server translates it internally to the following format:
case when value1 is not NULL then value1 when value2 is not NULL then value2 . . . when valuen-1 is not NULL then valuen-1 else valuen end
valuen-1 refers to the next to last value, before the final value, valuen.
The example below uses coalesce to determine whether a store orders a low quantity (more than 100 but less than 1000) or a high quantity of books (more than 1000):
select stor_id, discount, "Quantity" = coalesce(lowqty, highqty) from discounts
stor_id discount Quantity ------- -------- --------- ---- NULL 10.500000 NULL NULL 6.700000 100 NULL 10.000000 1001 8042 5.000000 NULL (4 rows affected)nullif
nullif compares two values; if the values are equal, nullif returns a null value. If the two values are not equal, nullif returns the value of the first value. This is useful for finding any missing, unknown, or inapplicable information that is stored in an encoded form. For example, values that are unknown are sometimes historically stored as -1. Using nullif, you can replace the -1 values with null and get the null behavior defined by Transact-SQL. The syntax is:
If value1 equals value2, nullif returns NULL. If value1 does not equal value2, nullif returns value1. value1 and value2 are expressions, and their datatypes must be comparable
When you use nullif, Adaptive Server translates it internally to the following format:
case when value1 = value2 then NULL else value1 end
For example, the titles table uses the value "UNDECIDED" to represent books whose type category is not yet determined. The following query performs a search on the titles table for book types; any book whose type is "UNDECIDED" is returned as type NULL (the following output is reformatted for display purposes):
select title, "type"= nullif(type, "UNDECIDED") from titles
title type ----- -------- The Busy Executive's Database Guide business Cooking with Computers: Surreptiti business You Can Combat Computer Stress! business . . . The Psychology of Computer Cooking NULL Fifty Years in Buckingham Palace K trad_cook Sushi, Anyone? trad_cook (18 rows affected)
Notice that The Psychology of Computing is stored in the table as "UNDECIDED", but the query returns it as type NULL.