Skip to main content

Celonis Product Documentation

COALESCE
Description

The COALESCE function returns the first element that is not NULL of a set of values.

It supports all PQL types (including table columns) as input. All arguments must be of the same data type except for integers and floats which can be mixed. The data type of the result is the same as the data type of the input columns or values and float if integer and float input expressions are mixed. For example, for input of type INT, COALESCE will return the type INT. And if some inputs are of type INT and some other inputs are of type FLOAT, COALESCE will return the type FLOAT

The COALESCE function requires at least two expressions.

Syntax
 COALESCE (table.column1, ..., table.columnN )
NULL handling

If all values are NULL, then COALESCE will also return NULL.

Tips
  • In the argument list, you can mix columns with constants. This allows you to define a constant default value for the case that all column values of a row are NULL.

  • The COALESCE function might be a good alternative to CASE WHEN statements for its reduced syntax.

Use Cases
Examples

[1]

COALESCE with four integer columns as arguments with some null values at different positions:

Query

Column1

         COALESCE ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )
        

Input

Output

Table1

Column1 : int

Column2 : int

Column3 : int

Column4 : int

1

2

3

4

5

null

7

8

null

10

11

12

null

null

15

16

17

18

19

20

null

null

null

24

null

null

null

null

29

30

31

32

Result

Column1 : int

1

5

10

15

17

24

null

29

[2]

COALESCE with two columns and a constant as arguments:

Query

Column1

         COALESCE ( "Table1"."Column1" , "Table1"."Column2" , 7 )
        

Input

Output

Table1

Column1 : int

Column2 : int

null

1

2

3

null

null

4

5

6

null

Result

Column1 : int

1

2

7

4

6

[3]

COALESCE with four integer columns as arguments containing only null values:

Query

Column1

         COALESCE ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )
        

Input

Output

Table1

Column1 : int

Column2 : int

Column3 : int

Column4 : int

null

null

null

null

null

null

null

null

null

null

null

null

Result

Column1

null

null

null

[4]

COALESCE with four float columns as arguments with some null values at different positions:

Query

Column1

         COALESCE ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )
        

Input

Output

Table1

Column1 : float

Column2 : float

Column3 : float

Column4 : float

1.0

2.0

3.0

4.0

5.0

null

7.0

8.0

null

10.0

11.0

12.0

null

null

15.0

16.0

17.0

18.0

19.0

20.0

null

null

null

24.0

null

null

null

null

29.0

30.0

31.0

32.0

Result

Column1 : float

1.0

5.0

10.0

15.0

17.0

24.0

null

29.0

[5]

COALESCE with four string columns as arguments with some null values at different positions:

Query

Column1

         COALESCE ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )
        

Input

Output

Table1

Column1 : string

Column2 : string

Column3 : string

Column4 : string

'Text1'

'Text2'

'Text3'

'Text4'

'Text5'

null

'Text7'

'Text8'

null

'Text10'

'Text11'

'Text12'

null

null

'Text15'

'Text16'

'Text17'

'Text18'

'Text19'

'Text20'

null

null

null

'Text24'

null

null

null

null

'Text29'

'Text30'

'Text31'

'Text32'

Result

Column1 : string

'Text1'

'Text5'

'Text10'

'Text15'

'Text17'

'Text24'

null

'Text29'

[6]

If the inputs contain integer and float types the result will be of type float.

Query

Column1

         COALESCE ( "Table1"."Column1" , "Table1"."Column2" , 7 )
        

Input

Output

Table1

Column1 : float

Column2 : int

null

1

2.0

3

null

null

4.0

5

6.0

null

Result

Column1 : float

1.0

2.0

7.0

4.0

6.0

See also: