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[,...])

Arguments

expression

Is 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.

Valid:

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 non-null 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 3-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 COALESE. 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

Note
Using any of the unsupported type combinations (those marked 'x' 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

Examples

SELECT COALESE(NULL,10 + 2,15,NULL)

10+2 is treated as a SMALLINT and ResultType (SQL_SMALLINT, SQL_SMALLINT) is SQL_SMALLINT. Hence, the result type is SQL_SMALLINT.

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.

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

Ten is treated as a SMALLINT and ResultType (SQL_SMALLINT, SQL_VARCHAR) is SQL_SMALLINT. Hence, the result type is SQL_SMALLINT.

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