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
COALESCEcan be used for Working Capital Optimization.
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: | |||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||
|
[6] If the inputs contain integer and float types the result will be of type float. | ||||||||||||||||||||||
| ||||||||||||||||||||||
|