How to Use the SQL CASE Expression with Values
You can use a more compact form of the SQL 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 test_value WHEN value1 THEN result1 WHEN value2 THEN result2 ... WHEN valuen THEN resultn ELSE resultx END
If the test value (test_value) is equal to value1, then the expression takes on the value result1. If tests_value is not equal to value1 but is equal to value2, then the expression 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, then 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 following 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 NULL END, LAST_NAME FROM OFFICERS ;
The result is a list similar to the following example:
Capt. Midnight Col. Sanders Gen. Washington Maj. Disaster 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 doesn’t give him a title.
For another example, suppose 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.
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 NULL END WHERE LAST_NAME = :officer_last_name ;
An alternative syntax for the CASE expression with values is:
CASE WHEN test_value = value1 THEN result1 WHEN test_value = value2 THEN result2 ... WHEN test_value = valuen THEN resultn ELSE resultx END