What is the purpose of COALESCE function? Explain with the help of an example.

We can use COALESCE function to replace the NULL value in any column with the user provided value. The COALESCE function takes the argument of column name and the value which should override the NULL value.

For example, if we have the row below in a DB2 table.

Since COBOL cannot handle NULL values, we can use below query which can replace the NULL value as “NA” string. Please note that this will work only if the ORDER_DESCRIPTION column has NULL value else the original value of ORDER_DESCRIPTION will be fetched.

A010-CHECK-ORDER.
   EXEC SQL
      SELECT INVOICE_ID, COALESCE(ORDER_DESCRIPTION, ‘NA’)
         INTO :INVOICE_ID,
            :ORDER_DESCRIPTION
   FROM ORDERS
   WHERE ORDER_ID = ‘5567812’
END-EXEC