MULTI CASE WHEN
Description
The MULTI CASE WHEN operator evaluates a list of conditions and returns a new column containing result expressions based on these conditions. In contrast to the CASE WHEN operator, the result expressions for all conditions that evaluate to true are returned. Furthermore, the MULTI CASE WHEN operator creates a new table that is joined back to the common table of all inputs.
A MULTI CASE WHEN statement consists of one or more conditions with associated result expressions.
Syntax
MULTI CASE WHEN condition THEN result_expression [ WHEN condition THEN result_expression ]* [ ELSE result_expression ] END
The result expression for each condition that evaluates to true is returned. If no condition holds, the expression in the ELSE part is returned. If no ELSE is specified, and none of the conditions hold, NULL is returned.
All result expressions must be of the same type. The result type of the MULTI CASE WHEN statement is equal to the type of the result expressions.
NULL handling
If a condition evaluates to NULL, it is treated as if it was false. However, it is not allowed to explicitly write WHEN NULL as a condition.
Examples
[1] MULTI CASE WHEN with multiple conditions: | ||||||||||||||||||||
| ||||||||||||||||||||
|
[2] | |||||||||||||||||||
| |||||||||||||||||||
|
[3] MULTI CASE WHEN with expression input for THEN: | |||||||||||||||||||
| |||||||||||||||||||
|
[4] The MULTI CASE WHEN result can be pulled back to the original input table since they are joined N:1, e.g., we can count how many WHEN conditions were satisfied for each row via PU_COUNT. | |||||||||||||||||||
| |||||||||||||||||||
|