Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6 

Description

CASE WHEN evaluates a list of conditions and returns result expressions based on these conditions.

A CASE WHEN statement consists of one or more conditions with associated result expressions.

Syntax

CASE WHEN condition THEN result_expression
[ WHEN condition THEN result_expression ]*
[ ELSE result_expression ]
END

The result expression of the first 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 (Since:  CELONIS 4.5). If a condition cannot be evaluated because an input column is NULL, NULL is returned.

All result expressions must be of the same type. The result type of the CASE WHEN statement is equal to the type of the result expressions.

Examples


[1] CASE WHEN with one condition:

Query
Column1
CASE WHEN "Table1"."Column1" = 1 THEN 2 ELSE 5 END
Input
Table1
Column1 : INT
1
2
1
2
Output
Result
Column1 : INT
2
5
2
5



[2] CASE WHEN with one condition, without ELSE:

Query
Column1
CASE WHEN "Table1"."Column1" = 1 THEN 3 END
Input
Table1
Column1 : INT
1
2
1
2
Output
Result
Column1 : INT
3
null
3
null



[3] Use CASE WHEN to replace NULL values with 0. This can also be done with the COALESCE operator.

Query
Column1
CASE WHEN ( ISNULL("Table1"."Column1") = 1 ) THEN 0 ELSE "Table1"."Column1" END
Input
Table1
Column1 : INT
null
5
null
5
Output
Result
Column1 : INT
0
5
0
5



[4] Return 'even' if the input value is even, and 'odd' otherwise. NULL input values result in NULL output values:

Query
Column1
CASE WHEN MODULO("Table1"."Column1", 2) = 0 THEN 'even' ELSE 'odd' END
Input
Table1
Column1 : INT
1
2
null
4
5
null
Output
Result
Column1 : STRING
'odd'
'even'
null
'even'
'odd'
null


See also:

  • No labels