The DB2 COALESCE function returns the value of the first non-NULL expression. The COALESCE function takes a comma separated list of arguments which are evaluated in the order in which they are specified and returns the first non-NULL value found.
The syntax for the COALESCE function is shown here:
-- NUMERIC EXAMPLE
SELECT COALESCE({field1}, {default_value}) FROM {table_name};
-- CHARACTER EXAMPLE
SELECT COALESCE({field1}, '{default_value}') FROM {table_name};
To show an example statement in use:
SELECT COALESCE(MFPARTNUMBER, PARTNUMBER) AS PART_NUM FROM CATENTRY;
The above query will first try to retrieve the MFPARTNUMBER column value from the CATENTRY table. If the MFPARTNUMBER value is NULL, then it will attempt to retrieve the PARTNUMBER column value from the CATENTRY table. If both columns are NULL, the COALESCE function will return NULL.
Now if we want to ensure we never get a NULL value to return, we can manipulate the above query as shown below to provide a default value in the instance that both MFPARTNUMBER and PARTNUMBER columns are NULL.
SELECT COALESCE(MFPARTNUMBER, PARTNUMBER, 'UNKNOWN') AS PART_NUM FROM CATENTRY;
The above query will return the value ‘UNKNOWN’ if the values in both the MFPARTNUMBER and PARTNUMBER columns are NULL.
NOTE: The COALESCE function is also supported on many other database platforms; however, this post was typed strictly to DB2.
