CASE expression

CASE expressions allow an expression to be selected based on the evaluation of one or more conditions.


                                   .-ELSE NULL---------------.        
>>-CASE--+-searched-when-clause-+--+-------------------------+--END-><
         '-simple-when-clause---'  '-ELSE--result-expression-'        

searched-when-clause

   .-----------------------------------------------------.   
   V                                                     |   
|----WHEN--search-condition--THEN--+-result-expression-+-+------|
                                   '-NULL--------------'     

simple-when-clause

|--expression--------------------------------------------------->

   .-----------------------------------------------.   
   V                                               |   
>----WHEN--expression--THEN--+-result-expression-+-+------------|
                             '-NULL--------------'

In general, the value of the case-expression is the value of the result-expression following the first (leftmost) when-clause that evaluates to true. If no when-clause evaluates to true and the ELSE keyword is present then the result is the value of the ELSE result-expression or NULL. If no when-clause evaluates to true and the ELSE keyword is not present then the result is NULL. Note that when a when-clause evaluates to unknown (because of nulls), the when-clause is not true and hence is treated the same way as a when-clause that evaluates to false.

searched-when-clause: Specifies a search-condition that is applied to each row or group of table data presented for evaluation, and the result when that condition is true.
simple-when-clause: Specifies that the value of the expression prior to the first WHEN keyword is tested for equality with the value of the expression that follows each WHEN keyword. It also specifies the result when that condition is true.
The data type of the expression prior to the first WHEN keyword must be compatible with the data types of the expression that follows each WHEN keyword.
result-expression or NULL: Specifies the value that follows the THEN keyword and ELSE keywords. There must be at least one result-expression in the CASE expression with a defined data type. NULL cannot be specified for every case.
All result-expressions must have compatible data types, where the attributes of the result are determined based on the Rules for result data types.
search-condition: Specifies a condition that is true, false, or unknown about a row or group of table data.
The search-condition must not include a subquery in an EXISTS or IN predicate.

There are two scalar functions, NULLIF and COALESCE, that are specialized to handle a subset of the functionality provided by CASE. The following table shows the equivalent expressions using CASE or these functions.

Table 1. Equivalent CASE Expressions
CASE Expression	Equivalent Expression
CASE WHEN e1=e2 THEN NULL ELSE e1 END	NULLIF(e1,e2)
CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END	COALESCE(e1,e2)
CASE WHEN e1 IS NOT NULL THEN e1 ELSE COALESCE(e2,...,eN) END	COALESCE(e1,e2,...,eN)

Examples

If the first character of a department number is a division in the organization, then a CASE expression can be used to list the full name of the division to which each employee belongs:

  SELECT EMPNO, LASTNAME,
    CASE SUBSTR(WORKDEPT,1,1)
    WHEN 'A' THEN 'Administration'
    WHEN 'B' THEN 'Human Resources'
    WHEN 'C' THEN 'Accounting'
    WHEN 'D' THEN 'Design'
    WHEN 'E' THEN 'Operations'
    END
  FROM EMPLOYEE

The number of years of education are used in the EMPLOYEE table to give the education level. A CASE expression can be used to group these and to show the level of education.

  SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME,
    CASE
    WHEN EDLEVEL < 15 THEN 'SECONDARY'
    WHEN EDLEVEL < 19 THEN 'COLLEGE'
    ELSE 'POST GRADUATE'
    END
  FROM EMPLOYEE

Another interesting example of CASE statement usage is in protecting from division by 0 errors. For example, the following code finds the employees who earn more than 25% of their income from commission, but who are not fully paid on commission:
```
  SELECT EMPNO, WORKDEPT, SALARY+COMM
  FROM EMPLOYEE
  WHERE (CASE WHEN SALARY=0 THEN NULL
              ELSE COMM/SALARY
         END) > 0.25
 
```

The following CASE expressions are equivalent:

  SELECT LASTNAME,
   CASE
   WHEN LASTNAME = 'Haas' THEN 'President'
   ...
   ELSE 'Unknown'
   END
  FROM EMPLOYEE
 
  SELECT LASTNAME,
   CASE LASTNAME
   WHEN 'Haas' THEN 'President'
   ...
   ELSE 'Unknown'
   END
  FROM EMPLOYEE