case and Value Comparisons

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
Related concepts
Determining the Result Set