COALESCE

The COALESCE scalar function takes two or more arguments and returns the first nonnull argument, starting from the left in the expression list.

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.

The function takes a minimum of two arguments.

COALESCE(10, 20)

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 non-null argument.

COALESCE (NULL, NULL, 20)

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:

ODBC Error: SQLSTATE = 01S01, Native error code = 0

Error in row
Error in assignment
Expression evaluation error

In the following example, 10+2 is treated as a SMALLINT and ResultType (SQL_SMALLINT, SQL_SMALLINT) is SQL_SMALLINT. Hence, the result type is SQL_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 SQL_SMALLINT. Therefore, the return value of this example is 12.

============

In the following example, ten is treated as a SMALLINT and ResultType (SQL_SMALLINT, SQL_VARCHAR) is SQL_SMALLINT. Hence, the result type is SQL_SMALLINT.

SELECT COALESCE(10, 'abc' + 'def')

The first parameter is 10, which can be converted to result type SQL_SMALLINT. Therefore, the return value of this example is 10.