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-'

Notes:

See Condition

Element	Description	Restrictions	Syntax
expr	Expression that returns some data type	Data type of expr in a THEN clause must be compatible with data types of expressions in other THEN clauses	Expression

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 and IS NOT NULL Conditions.

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. For more information on the compatibility of returned data types, see CASE expressions data type compatibility.