Applies to: CELONIS 4.5 CELONIS 4.6
Description
The COALESCE operator 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 operator 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 operator might be a good alternative to CASE WHEN statements for it's 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")
[3] COALESCE with four integer columns as arguments containing only null values:
Query
Column1
COALESCE("Table1"."Column1", "Table1"."Column2", "Table1"."Column3", "Table1"."Column4")
[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")
[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")
Output
Result
| Column1 : STRING |
|---|
| 'Text1' |
| 'Text5' |
| 'Text10' |
| 'Text15' |
| 'Text17' |
| 'Text24' |
| null |
| 'Text29' |