Skip to main content

Celonis Product Documentation

CASE WHEN
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.

All result expressions must be of the same type except for integers and floats which can be mixed. The result type of the CASE WHEN statement is equal to the type of the result expressions and float if integers and floats are mixed in the result expression.

NULL handling

If a condition evaluates to NULL, it is treated as if it was false.

Examples

[1]

CASE WHEN with one condition:

Query

Column1

         CASE WHEN "Table1"."Column1" = 1 THEN 2 ELSE 5 END
        

Input

Output

Table1

Column1 : int

1

2

1

2

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

Output

Table1

Column1 : int

1

2

1

2

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

Output

Table1

Column1 : int

null

5

null

5

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 "Table1"."Column1" IS NULL THEN NULL WHEN MODULO ( "Table1"."Column1" , 2 ) = 0 THEN 'even' ELSE 'odd' END
        

Input

Output

Table1

Column1 : int

1

2

null

4

5

null

Result

Column1 : string

'odd'

'even'

null

'even'

'odd'

null

[5]

CASE WHEN with two conditions:

Query

Column1

         CASE WHEN "Table1"."Column1" = 1 THEN 10 WHEN ISNULL ( "Table1"."Column1" ) = 1 THEN 0 ELSE - 1 END
        

Input

Output

Table1

Column1 : int

1

null

2

null

Result

Column1 : int

10

0

-1

0

[6]

CASE WHEN with an integer THEN statement and a float ELSE statement. The result is a float column.

Query

Column1

         CASE WHEN "Table1"."Column1" = 1 THEN 2 ELSE 5.0 END
        

Input

Output

Table1

Column1 : int

1

2

1

2

Result

Column1 : float

2.0

5.0

2.0

5.0

See also: