Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

COALESCE

Applies to: CELONIS 4.5 CELONIS 4.6

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. The data type of the result is the same as the data type of the input columns or values. For example, for input of type INT COALESCE will return the type INT .

The COALESCE function requires at least two expressions. If all values are NULL, then COALESCE will also return NULL.

Syntax
COALESCE (table.column1, ..., table.columnN )
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.

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 :

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 : INT

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'

See also: