CASE Expressions

The CASE expression allows an SQL statement such as the SELECT statement to return one of several possible results, depending on which of several condition evaluates to true. The CASE expression has two forms: generic CASE expressions and linear CASE expressions.

CASE Expressions:
 
                                 (1)
|--+-| Generic CASE Expression |------------+-------------------|
   |  (2)                              (3)  |
   '--------| Linear CASE Expression |------'
 

1. See page Generic CASE Expressions
2. Dynamic Server only
3. See page Linear CASE Expressions (IDS)

You must include at least one WHEN clause in the CASE expression. Subsequent WHEN clauses and the ELSE clause are optional. You can use a generic or linear CASE expression wherever you can use a column expression in an SQL statement (for example, in the Projection clause a SELECT statement).

Expressions in the search condition or the result value expression can contain subqueries, and you can nest a CASE expression in another CASE expression. When a CASE expression appears in an aggregate expression, you cannot use aggregate functions in the CASE expression.

Generic CASE Expressions

A generic CASE expression tests for a true condition in a WHEN clause. If it finds a true condition, it returns the result specified in the THEN clause.

Generic CASE Expression:
 
         .------------------------------------------.
         V                     (1)                  |
|--CASE----WHEN--| Condition |-------THEN--+-expr-+-+----------->
                                           '-NULL-'
 
>--+----------------+--END--------------------------------------|
   '-ELSE--+-expr-+-'
           '-NULL-'
 

1. See page Condition

The database server processes the WHEN clauses in the order that they appear in the statement. If the search condition of a WHEN clause evaluates to TRUE, the database server uses the value of the corresponding THEN expression as the result, and stops processing the CASE expression.

If no WHEN condition evaluates to TRUE, the database server uses the ELSE expression as the overall result. If no WHEN condition evaluates to TRUE, and no ELSE clause was specified, the returned CASE expression value is NULL. You can use the IS NULL condition to handle NULL results. For information on how to handle NULL values, see IS NULL Condition.

The next example shows a generic CASE expression in the Projection clause.

In this example, the user retrieves the name and address of each customer as well as a calculated number that is based on the number of problems that exist for that customer:

SELECT cust_name,
   CASE
   WHEN number_of_problems = 0
      THEN 100
   WHEN number_of_problems > 0 AND number_of_problems < 4
      THEN number_of_problems * 500
   WHEN number_of_problems >= 4 and number_of_problems <= 9
      THEN number_of_problems * 400
   ELSE
      (number_of_problems * 300) + 250
   END,
   cust_address
FROM custtab

In a generic CASE expression, all the results should be of the same data type, or they should evaluate to a common compatible data type. If the results in all the WHEN clauses are not of the same data type, or if they do not evaluate to values of mutually compatible types, an error occurs.

Linear CASE Expressions (IDS)

A linear CASE expression compares the value of the expression that follows the CASE keyword with an expression in a WHEN clause.

Linear CASE Expression:
 
               .----------------------------.
               V                            |
|--CASE--expr----WHEN--expr--THEN--+-expr-+-+------------------->
                                   '-NULL-'
 
>--+----------------+--END--------------------------------------|
   '-ELSE--+-expr-+-'
           '-NULL-'
 

The database server evaluates the expression that follows the CASE keyword, and then processes the WHEN clauses sequentially. If an expression after the WHEN keyword returns the same value as the expression that follows the CASE keyword, the database server uses the value of the expression that follows the THEN keyword as the overall result of the CASE expression. Then the database server stops processing the CASE expression.

If none of the WHEN expressions return the same value as the expression that follows the CASE keyword, the database server uses the expression of the ELSE clause as the overall result of the CASE expression (or, if no ELSE clause was specified, the returned value of the CASE expression is NULL).

The next example shows a linear CASE expression in the projection list of the Projection clause of a SELECT statement. For each movie in a table of movie titles, the query returns the title, the cost, and the type of the movie. The statement uses a CASE expression to derive the type of each movie:

SELECT title, CASE movie_type
      WHEN 1 THEN 'HORROR'
      WHEN 2 THEN 'COMEDY'
      WHEN 3 THEN 'ROMANCE'
      WHEN 4 THEN 'WESTERN'
      ELSE 'UNCLASSIFIED'
   END,
   our_cost FROM movie_titles

In linear CASE expressions, the data types of WHEN clause expressions must be compatible with that of the expression that follows the CASE keyword.