SQL Reference

CASE Expressions

 
case-expression
 
|--CASE----+-searched-when-clause-+----------------------------->
           '-simple-when-clause---'
 
      .-ELSE NULL----------------.
>-----+--------------------------+--END-------------------------|
      '-ELSE--result-expression--'
 
searched-when-clause
 
    .-------------------------------------------------------.
    V                                                       |
|------WHEN--search-condition--THEN--+-result-expression-+--+---|
                                     '-NULL--------------'
 
simple-when-clause
 
                .-------------------------------------------------.
                V                                                 |
|---expression-----WHEN--expression--THEN--+-result-expression-+--+->
                                           '-NULL--------------'
 
>---------------------------------------------------------------|
 

CASE expressions allow an expression to be selected based on the evaluation of one or more conditions. In general, the value of the case-expression is the value of the result-expression following the first (leftmost) case that evaluates to true. If no case evaluates to true and the ELSE keyword is present then the result is the value of the result-expression or NULL. If no case evaluates to true and the ELSE keyword is not present then the result is NULL. Note that when a case evaluates to unknown (because of NULLs), the case is not true and hence is treated the same way as a case that evaluates to false.

If the CASE expression is in a VALUES clause, an IN predicate, a GROUP BY clause, or an ORDER BY clause, the search-condition in a searched-when-clause cannot be a quantified predicate, IN predicate using a fullselect, or an EXISTS predicate (SQLSTATE 42625).

When using the simple-when-clause, the value of the expression prior to the first WHEN keyword is tested for equality with the value of the expression following the WHEN keyword. The data type of the expression prior to the first WHEN keyword must therefore be comparable to the data types of each expression following the WHEN keyword(s). The expression prior to the first WHEN keyword in a simple-when-clause cannot include a function that is variant or has an external action (SQLSTATE 42845).

A result-expression is an expression following the THEN or ELSE keywords. There must be at least one result-expression in the CASE expression (NULL cannot be specified for every case) (SQLSTATE 42625). All result-expressions must have compatible data types (SQLSTATE 42804), where the attributes of the result are determined based on the Rules for Result Data Types.

Examples:

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

Table 11. Equivalent CASE Expressions
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)


[ Top of Page | Previous Page | Next Page ]