COALESCE

The COALESCE function returns the value of the first nonnull expression.


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

The schema is SYSIBM.

The arguments must be compatible. For more information on compatibility, refer to the compatibility matrix in Table 1. The arguments can be of either a built-in or distinct type.

The COALESCE function cannot be used as a source function when creating a user-defined function.

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 arguments can be null. The result is null only if all arguments are null.

The selected argument is converted, if necessary, to the attributes of the result. The attributes of the result are determined using the Rules for result data types. If the COALESCE function has more than two arguments, the rules are applied to the first two arguments to determine a candidate result type. The rules are then applied to that candidate result type and the third argument to determine another candidate result type. This process continues until all arguments are analyzed and the final result type is determined.

Start of change If there are any mixed character string or graphic string and numeric arguments, the string value is implicitly cast to a DECFLOAT(34) value. End of change

The COALESCE function can also handle a subset of the functions provided by CASE expressions. The result of using COALESCE(e1,e2) is the same as using the expression:

   CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END

VALUE can be specified as a synonym for COALESCE.

Example 1: Assume that SCORE1 and SCORE2 are SMALLINT columns in table GRADES, and that nulls are allowed in SCORE1 but not in SCORE2. Select all the rows in GRADES for which SCORE1 + SCORE2 > 100, assuming a value of 0 for SCORE1 when SCORE1 is null.

   SELECT * FROM GRADES
     WHERE COALESCE(SCORE1,0) + SCORE2 > 100;

Example 2: Assume that a table named DSN8A10.EMP contains a DATE column named HIREDATE, and that nulls are allowed for this column. The following query selects all rows in DSN8A10.EMP for which the date in HIREDATE is either unknown (null) or earlier than 1 January 1960.

   SELECT * FROM DSN8A10.EMP
     WHERE COALESCE(HIREDATE,DATE('1959-12-31')) < '1960-01-01';

The predicate could also be coded as COALESCE(HIREDATE,'1959-12-31') because, for comparison purposes, a string representation of a date can be compared to a date.

Example 3: Assume that for the years 1993 and 1994 there is a table that records the sales results of each department. Each table, S1993 and S1994, consists of a DEPTNO column and a SALES column, neither of which can be null. The following query provides the sales information for both years.

   SELECT COALESCE(S1993.DEPTNO,S1994.DEPTNO) AS DEPT, S1993.SALES, S1994.SALES
     FROM S1993 FULL JOIN S1994 ON S1993.DEPTNO = S1994.DEPTNO
     ORDER BY DEPT;

The full outer join ensures that the results include all departments, regardless of whether they had sales or existed in both years. The COALESCE function allows the two join columns to be combined into a single column, which enables the results to be ordered.