| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Transact-SQL User's Guide |
![[-]](/dwicons/i_colpse.gif)
| Chapter 13 Using Batches and Control-of-Flow Language |
|
| Using control-of-flow language |
|
| case expression |
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 representationUsing 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 titlestitle 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 titlestitle_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 expressionsExpressions 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
...
endare 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
...
endThis 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"
endis 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"
endIn this form, a new rand value is generated for each when clause, and the case expression frequently produce the result "E".
case expression resultsThe 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 endThe 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 resultAt 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 titlesreturns 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
endwhere 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_idstor_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 titletitle 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
endwhere 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
endThis 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_idtitle 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_idcoalescecoalesce 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
endvaluen-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 discountsstor_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:
nullif(value1, value2)
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
endFor 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.
|
|