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 conditions evaluates to true.

The CASE expression has two forms: generic CASE expressions and linear CASE expressions.

CASE Expressions

Notes:

¹ See Generic CASE Expressions
² See Linear CASE Expressions

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.

You can specify a trigger-type Boolean operator (DELETING, INSERTING, SELECTING, or UPDATING) as a condition in a CASE expression only within a trigger routine.

The following query fragment declares aliases for two aggregate column expressions:

SELECT . . . 
   SUM(orders.ship_weight) as o2,
   COUNT(DISTINCT 
         CASE WHEN orders.backlog MATCHES 'n' 
              THEN orders.order_num END ) AS o3, 
   . . .

Here the argument to SUM is a DECIMAL(8,2) column value, and the COUNT DISTINCT aggregate takes a CASE expression as its argument.

Do not confuse CASE expressions with the CASE statement of SPL, which supports different syntax and functionality.