Skip to main content

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.

Note: The output of this operator is stable but the order may be indeterministic, meaning that the same input data and query string will always produce the same output values while there is no guarantee of the exact order of outputs if the input is altered.

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 

"Table"."ID"

Column2 

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

ID : int

Column1 : string

Column2 : int

1

'X'

1

2

'Y'

0

3

'Z'

2

4

'Y'

3

Result

Column1 : int

Column2 : string

1

'has X'

1

'has 1 or 3'

4

'has 1 or 3'

2

'has neither X nor 1 or 3'

3

'has neither X nor 1 or 3'

[2]

MULTI CASE WHEN with no ELSE statement:

Query

Column1 

"Table"."ID"

Column2 

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

Input

Output

Table

ID : int

Column1 : string

Column2 : int

1

'X'

1

2

'Y'

0

3

'Z'

2

4

'Y'

3

Result

Column1 : int

Column2

3

has Z

1

null

2

null

4

null

[3]

MULTI CASE WHEN with column input for THEN:

Query

Column1 

"Table"."ID"

Column2 

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

Input

Output

Table

ID : int

Column1 : string

Column2 : int

1

'X'

1

2

'Y'

0

3

'Z'

2

4

'Y'

3

Result

Column1 : int

Column2 : int

1

5

4

7

2

0

3

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 

"Table"."ID"

Column2 

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

ID : int

Column1 : string

Column2 : int

1

'X'

1

2

'Y'

0

3

'Z'

2

4

'Y'

3

Result

Column1 : int

Column2

1

2

2

0

3

0

4

1

See also: