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.
- 1 See Generic CASE Expressions
- 2 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.
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.