Note An invalid instance results in a parse-time error:
COALESCE must have at least 2 arguments.
The expression list must contain at least one nonnull argument.
Valid:
COALESCE (NULL, NULL, 20)
Invalid:
COALESCE (NULL, NULL, NULL)
Note An invalid instance results in a parse-time error:
All arguments of COALESCE cannot be the NULL function
The function does not support some of the data type combinations in the expression list.
For example, COALESCE cannot have BINARY and VARCHAR types as arguments as neither of them can be implicitly converted to the other.
Supported Combination Types and Result Data Types
The following figure details the various supported combination types and also helps you identify the resultant data type for various combinations in a COALESCE function.
Figure 1 COALESCE Supported Combinations and Resultant Data Types
Chart Element
Description
Types can be used directly in COALESCE function. The result type is that of operand 2.
Types can be used directly in COALESCE function. The result type is that of operand 1.
blank cell
Types are not compatible. The operands cannot be used directly in COALESCE. An explicit CONVERT is required.
D
Result type is SQL_DOUBLE
B
Result type is SIM_BCD
I
Result type is SQL_INTEGER
S
Result type is SQL_SMALLINT
Using any of the unsupported type combinations (those left blank in the chart) in COALESCE function results in a parse-time error:
Error in row Error in assignment Expression evaluation error
Examples
In the following example, 10+2 is treated as a SMALLINT and ResultType (SMALLINT, SMALLINT) is SMALLINT. Hence, the result type is SMALLINT.
SELECT COALESCE(NULL,10 + 2,15,NULL)
The first parameter is NULL. The second expression evaluates to 12, which is not NULL and can be converted to result type SMALLINT. Therefore, the return value of this example is 12.
============
In the following example, ten is treated as a SMALLINT and ResultType (SMALLINT, VARCHAR) is SMALLINT. Hence, the result type is SMALLINT.
SELECT COALESCE(10, 'abc' + 'def')
The first parameter is 10, which can be converted to result type SMALLINT. Therefore, the return value of this example is 10.