COALESCE
Applies to: CELONIS 4.5 CELONIS 4.6 CELONIS 4.7
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.
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.
Examples
[1] COALESCE with four integer columns as arguments with some null values at different positions. |
[2] COALESCE with two columns and a constant as arguments. |
[3] COALESCE with four integer columns as arguments containing only null values. |
[4] COALESCE with four float columns as arguments with some null values at different positions.
|
[5] COALESCE with four string columns as arguments with some null values at different positions.
|