case can be 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.
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_id