WHEN clauses are processed sequentially.
The first WHEN clause value_expression_n that equates to value_expression_1 returns the value of its associated scalar_expression_n as its result. The evaluation process then terminates.
If no value_expression_n equals value_expression_1, then scalar_expression_m, the argument of the ELSE clause, is the result.
If no ELSE clause is defined, then the result defaults to NULL.
The data type of value_expression_1 must be comparable with the data types of all of the value_expression_n values.
For information on the result data type of a CASE expression, see Rules for the CASE Expression Result Type.
You can use a scalar subquery in the WHEN clause, THEN clause, and ELSE clause of a CASE expression. If you use a non-scalar subquery (a subquery that returns more than one row), a runtime error is returned.
Recommendation: Do not use the built-in functions CURRENT_DATE or CURRENT_TIMESTAMP in a CASE expression that is specified in a partitioning expression for a partitioned primary index (PPI). All rows are scanned during reconciliation.
Restrictions on the Data Types in a CASE Expression
The following restrictions apply to specific data types in a CASE expression:
| Data Type | Restrictions |
|---|---|
| BLOB | A BLOB can only appear in value_expression_1, value_expression_n, scalar_expression_m, or scalar_expression_n when cast to BYTE or VARBYTE. |
| CLOB | A CLOB can only appear in value_expression_1, value_expression_n, scalar_expression_m, or scalar_expression_n when cast to CHAR or VARCHAR. |