SQL Reference

COALESCE

                                 .------------------.
            (1)                  V                  |
>>-COALESCE-------(--expression-----,--expression---+---)------><
 

Notes:

1. VALUE is a synonym for COALESCE.

The schema is SYSIBM.

COALESCE returns the first argument that is not null.

The arguments are evaluated in the order in which they are specified, and the result of the function is the first argument that is not null. The result can be null only if all the arguments can be null, and the result is null only if all the arguments are null. The selected argument is converted, if necessary, to the attributes of the result.

The arguments must be compatible. See Rules for Result Data Types for what data types are compatible and the attributes of the result. They can be of either a built-in or user-defined data type. ⁴⁰

Examples:

• When selecting all the values from all the rows in the DEPARTMENT table, if the department manager (MGRNO) is missing (that is, null), then return a value of 'ABSENT'.

    SELECT DEPTNO, DEPTNAME, COALESCE(MGRNO, 'ABSENT'), ADMRDEPT
      FROM DEPARTMENT
  

• When selecting the employee number (EMPNO) and salary (SALARY) from all the rows in the EMPLOYEE table, if the salary is missing (that is, null), then return a value of zero.

    SELECT EMPNO, COALESCE(SALARY, 0)
      FROM EMPLOYEE
  

⁴⁰

This function may not be used as a source function when creating a user-defined function. Since it accepts any compatible data types as arguments, it is not necessary to create additional signatures to support user-defined distinct types.