Skip to main content

Celonis Product Documentation

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:

Query

Column1

         MULTI CASE WHEN "Table"."Column1" = 'X' THEN 'has X' WHEN "Table"."Column2" IN ( 1 , 3 ) THEN 'has 1 or 3' ELSE 'has neither X nor 1 or 3' END
        

Input

Output

Table

Column1 : string

Column2 : int

'X'

1

'Y'

0

'Z'

2

'Y'

3

Result

Column1 : string

'has X'

'has 1 or 3'

'has 1 or 3'

'has neither X nor 1 or 3'

'has neither X nor 1 or 3'

[2]

Query

Column1

         MULTI CASE WHEN "Table"."Column1" = 'Z' THEN 'has Z' END
        

Input

Output

Table

Column1 : string

Column2 : int

'X'

1

'Y'

0

'Z'

2

'Y'

3

Result

Column1

has Z

null

null

null

[3]

MULTI CASE WHEN with expression input for THEN:

Query

Column1

         MULTI CASE WHEN "Table"."Column1" = 'X' THEN "Table"."Column2" + 4 ELSE 0 END
        

Input

Output

Table

Column1 : string

Column2 : int

'X'

1

'Y'

0

'Z'

2

'Y'

3

Result

Column1 : int

5

0

0

0

[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.

Query

Column1

         PU_COUNT ( "Table" , MULTI CASE WHEN "Table"."Column1" = 'X' THEN 'has X' WHEN "Table"."Column2" IN ( 1 , 3 ) THEN 'has 1 or 3' END )
        

Input

Output

Table

Column1 : string

Column2 : int

'X'

1

'Y'

0

'Z'

2

'Y'

3

Result

Column1

2

0

0

1

See also: