DB2 COALESCE Function

DB2 COALESCE Function

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.

Author: daharveyjr

I’m a solution architect responsible for the design, development, implementation, testing, and maintenance of e-commerce operations and applications using the Hybris and WebSphere Commerce product suites and other web technologies such as Java, J2EE/JEE, Spring, PHP, WordPress and more. Twitter | Facebook | LinkedIn

Leave a Reply

Your email address will not be published.