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

DB2DatabaseBig Data Analytics

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.

ORDER_IDORDER_DATEORDER_DESCRIPTIONINVOICE_ID
55678122020-07-28NULLA112786

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
raja
Mandalika
Published on 14-Sep-2020 15:10:08
Previous Page Print Page
Next Page  
Advertisements