Advanced SQL Value Expressions
The performance penalty exacted by SQL’s limitations prompts the addition of new features to SQL every time a new version of the international specification is released. One of those new features, the CASE expression, provides a long-sought conditional structure. A second feature, the CAST expression, facilitates data conversion in a table from one type of data to another. A third feature, the row value expression, enables you to operate on a list of values where, previ-ously, only a single value was possible. For example, if your list of values is a list of columns in a table, you can now perform an operation on all those columns by using a very simple syntax.
CASE Conditional Expressions
Every complete computer language has some kind of conditional statement
or command. In fact, most have several kinds. Probably the most common con-ditional statement or command is the IF...THEN...ELSE...ENDIF structure. If the condition following the IF keyword evaluates to True, the block of com-mands following the THEN keyword executes. If the condition doesn’t evaluate to True, the block of commands after the ELSE keyword executes. The ENDIF keyword signals the end of the structure. This structure is great for any deci-sion that goes one of two ways. The structure is less applicable to decisions that can have more than two outcomes.
Most complete languages have a CASE statement that handles situations in which you may want to perform more than two tasks based on more than two conditions.
SQL:2003 has a CASE statement and a CASE expression. A CASE expression is only part of a statement — not a statement in its own right. In SQL, you can place a CASE expression almost anywhere a value is legal. At run time, a CASE expression evaluates to a value. SQL’s CASE statement doesn’t evaluate to a value; rather, it executes a block of statements.
You can use the CASE expression in the following two ways:
Use the expression with search conditions. CASE searches for rows in a table where specified conditions are True. If CASE finds a search condition to be True for a table row, the statement containing the CASE expression makes a specified change to that row.
Use the expression to compare a table field to a specified value.
The outcome of the statement containing the CASE expression depends on which of several specified values in the table field is equal to each table row.
The next two sections, “Using CASE with search conditions” and “Using CASE with values,” help make these concepts more clear. In the first section, two examples use CASE with search conditions. One example searches a table and makes changes to table values, based on a condition. The second section explores two examples of the value form of CASE.
Using CASE with search conditions
One powerful way to use the CASE expression is to search a table for rows in which a specified search condition is True. If you use CASE this way, the expression uses the following syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionn THEN resultn
ELSE resultx
END
CASE examines the first qualifying row (the first row that meets the conditions of the enclosing WHERE clause, if any) to see whether condition1 is True. If it is, the CASE expression receives a value of result1. If condition1 is not
True, CASE evaluates the row for condition2. If condition2 is True, the CASE expression receives the value of result2, and so on. If none of the stated conditions are True, the CASE expression receives the value of resultx. The ELSE clause is optional. If the expression has no ELSE clause and none of the specified conditions are True, the expression receives a null value. After the SQL statement containing the CASE expression applies itself to the first quali-fying row in a table and takes the appropriate action, it processes the next row. This sequence continues until the SQL statement finishes processing the entire table.
Updating values based on a condition
Because you can embed a CASE expression within an SQL statement almost anywhere a value is possible, this expression gives you tremendous flexibil-ity. You can use CASE within an UPDATE statement, for example, to make changes to table values — based on certain conditions. Consider the following example:
UPDATE FOODS
SET RATING = CASE
WHEN FAT < 1
THEN ‘very low fat’
WHEN FAT < 5
THEN ‘low fat’
WHEN FAT < 20
THEN ‘moderate fat’
WHEN FAT < 50
THEN ‘high fat’
ELSE ‘heart attack city’
END ;
This statement evaluates the WHEN conditions in order until the first True value is returned, after which the statement ignores the rest of the conditions.
Table 7-2 in Chapter 7 shows the fat content of 100 grams of certain foods. A database table holding this information can contain a RATING column that gives a quick assessment of the fat content’s meaning. If you run the preced-ing UPDATE on the FOODS table in Chapter 7, the statement assigns asparagus a value of very low fat, gives chicken a value of low fat, and puts roasted almonds into the heart attack city category.
Avoiding conditions that cause errors
Another valuable use of CASE is exception avoidance — checking for condi-tions that cause errors.
Consider a case that determines compensation for salespeople. Companies that compensate their salespeople by straight commission often pay their new employees by giving them a “draw” against commission. In the following example, new salespeople receive a draw against commission that’s phased out gradually as their commissions rise:
UPDATE SALES_COMP
SET COMP = COMMISSION + CASE
WHEN COMMISSION <> 0
THEN DRAW/COMMISSION
WHEN COMMISSION = 0
THEN DRAW
END ;
If the salesperson’s commission is zero, the structure in this example avoids a division by zero operation, which causes an error. If the salesperson has a nonzero commission, total compensation is the commission plus a draw that is reduced proportionately to the size of the commission.
All of the THEN expressions in a CASE expression must be of the same type — all numeric, all character, or all date. The result of the CASE expression is also of the same type.
Using CASE with values
You can use a more compact form of the CASE expression if you’re comparing a test value for equality with a series of other values. This form is useful within a SELECT or UPDATE statement if a table contains a limited number of values in a column and you want to associate a corresponding result value to each of those column values. If you use CASE in this way, the expression has the following syntax:
CASE valuet
WHEN value1 THEN result1
WHEN value2 THEN result2
...
WHEN valuen THEN resultn
ELSE resultx
END
If the test value (valuet) is equal to value1, the expression takes on the value result1. If valuet is not equal to value1 but is equal to value2, the expres-sion takes on the value result2. The expression tries each comparison value in turn, all the way down to valuen, until it achieves a match. If none of the comparison values equal the test value, the expression takes on the value resultx. Again, if the optional ELSE clause isn’t present and none of the comparison values match the test value, the expression receives a null value.
To understand how the value form works, consider a case in which you have a table containing the names and ranks of various military officers. You want to list the names preceded by the correct abbreviation for each rank. The fol-lowing statement does the job:
|
SELECT CASE RANK
|
|
|
WHEN
‘general’
|
THEN
‘Gen.’
|
|
WHEN
‘colonel’
|
THEN
‘Col.’
|
|
WHEN
‘lieutenant colonel’
|
THEN
‘Lt. Col.’
|
|
WHEN
‘major’
|
THEN
‘Maj.’
|
|
WHEN
‘captain’
|
THEN
‘Capt.’
|
|
WHEN
‘first lieutenant’
|
THEN
‘1st. Lt.’
|
|
WHEN
‘second lieutenant’
|
THEN
‘2nd. Lt.’
|
|
ELSE
‘Mr.’
|
|
|
END,
|
|
|
LAST_NAME
|
|
|
FROM OFFICERS ;
|
|
Capt. Midnight
Col. Sanders
Gen. Schwarzkopf
Maj. Disaster
Mr. Nimitz
Chester Nimitz was an admiral in the United States Navy during World War II. Because his rank isn’t listed in the CASE expression, the ELSE clause deter-mines his title.
For another example, suppose that Captain Midnight gets a promotion to major and you want to update the OFFICERS database accordingly. Assume that the variable officer_last_name contains the value ‘Midnight’ and that the variable new_rank contains an integer (4) that corresponds to Midnight’s new rank, according to the following table.
|
new_rank
|
Rank
|
|
1
|
general
|
|
|
|
|
2
|
colonel
|
|
|
|
|
3
|
lieutenant colonel
|
|
|
|
|
4
|
major
|
|
|
|
|
5
|
captain
|
|
|
|
|
6
|
first lieutenant
|
|
|
|
|
7
|
second lieutenant
|
|
|
|
|
8
|
Mr.
|
You can record the promotion by using the following SQL code:
UPDATE OFFICERS
SET RANK = CASE :new_rank
WHEN 1 THEN ‘general’
WHEN 2 THEN ‘colonel’
WHEN 3 THEN ‘lieutenant colonel’
WHEN 4 THEN ‘major’
WHEN 5 THEN ‘captain’
WHEN 6 THEN ‘first lieutenant’
WHEN 7 THEN ‘second lieutenant’
WHEN 8 THEN ‘Mr.’
END
WHERE LAST_NAME = :officer_last_name ;
An alternative syntax for the CASE with values is as follows:
CASE
WHEN valuet = value1 THEN result1
WHEN valuet = value2 THEN result2
...
WHEN valuet = valuen THEN resultn
ELSE resultx
END
A special CASE — NULLIF
The one thing you can be sure of in this world is change. Sometimes things change from one known state to another. Other times, you think that you know something but later you find out that you didn’t know it after all. Classical ther-modynamics, as well as modern chaos theory, tells us that systems naturally migrate from a well-known, ordered state into a disordered state that no one can predict. Anyone who has ever monitored the status of a teenager’s room for a one-week period after the room is cleaned can vouch for the accuracy of these theories.
Database tables have definite values in fields containing known contents. Usually, if the value of a field is unknown, the field contains the null value. In SQL, you can use a CASE expression to change the contents of a table field from a definite value to a null. The null indicates that you no longer know the field’s value. Consider the following example.
Imagine that you own a small airline that offers flights between southern California and Washington state. Until recently, some of your flights stopped at San Jose International Airport to refuel before continuing on. Unfortunately, you just lost your right to fly into San Jose. From now on, you must make your refueling stop at either San Francisco International or Oakland International. At
this point, you don’t know which flights stop at which airport, but you do know that none of the flights are stopping at San Jose. You have a FLIGHT database that contains important information about your routes, and now you want to update the database to remove all references to San Jose. The following example shows one way to do this:
UPDATE FLIGHT
SET RefuelStop = CASE
WHEN RefuelStop = ‘San Jose’
THEN NULL
ELSE RefuelStop
END ;
Because occasions like this one, in which you want to replace a known value with a null, frequently arise, SQL offers a shorthand notation to accomplish this task. The preceding example, expressed in this shorthand form, appears as follows:
UPDATE FLIGHT
SET RefuelStop = NULLIF(RefuelStop, ‘San Jose’) ;
You can read this expression as, “Update the FLIGHT table by setting column
RefuelStop to null if the existing value of RefuelStop is ‘San Jose’.
Otherwise, make no change.”
NULLIF is even handier if you’re converting data that you originally accumu-lated for use with a program written in a standard programming language such as COBOL or FORTRAN. Standard programming languages don’t have nulls, so a common practice is to represent the “not known” or “not applicable” concept by using special values. A numeric –1 may represent a not known value for SALARY, for example, and a character string “***” may represent a not known or not applicable value for JOBCODE. If you want to represent these not known and not applicable states in an SQL-compatible database by using nulls, you need to convert the special values to nulls. The following example makes this conversion for an employee table, in which some salary values are unknown:
UPDATE EMP
SET Salary = CASE Salary
WHEN -1 THEN NULL
ELSE Salary
END ;
You can perform this conversion more conveniently by using NULLIF, as follows:
UPDATE EMP
SET Salary = NULLIF(Salary, -1) ;
Another special CASE — COALESCE
COALESCE, like NULLIF, is a shorthand form of a particular CASE expression. COALESCE deals with a list of values that may or may not be null. If one of the values in the list is non-null, the COALESCE expression takes on that value. If more than one value in the list is non-null, the expression takes on the value of the first non-null item in the list. If all the values in the list are null, the expression takes on the null value.
A CASE expression with this function has the following form:
CASE
WHEN value1 IS NOT NULL
THEN value1
WHEN value2 IS NOT NULL
THEN value2
...
WHEN valuen IS NOT NULL
THEN valuen
ELSE NULL
END
The corresponding COALESCE shorthand appears as follows:
COALESCE(value1, value2, ..., valuen)
You may want to use a COALESCE expression after you perform an OUTER JOIN operation. In such cases, COALESCE can save you a lot of typing.
No comments:
Post a Comment