COALESCE
The COALESCE scalar function takes two or more arguments and returns the first nonnull argument, starting from the left in the expression list.
Syntax
COALESCE ( expression, expression[,...])
expression ::= any valid expression
Returned Value Types
The COALESCE function returns the value of one of the expressions in the list. For a detailed list of returned data types, see
Supported Combination Types and Result Data Types.
Restrictions
The function takes a minimum of two arguments.
COALESCE(10, 20)
Invalid:
COALESCE()
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.
