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

Mandalika

Published on 14-Sep-2020 15:10:08

• Related Questions & Answers
• What is the use of the VALUE function in a DB2? Explain with the help of an example
• Explain the concept of LOCK PROMOTION with the help of an example
• Purpose and usage of subqueries in DB with the help of an example
• What is the difference between DB2 JOIN and UNION? Explain with the help of an example
• Explain join operations with the help of an example in DBMS
• What is Fixed-list SELECT? Give the syntax and explain with help of an example
• What are COLUMN functions in DB2? Explain with the help of an example
• What is CLUSTERED INDEX in DB2? Explain with the help of practical example.
• Explain the concept of DYNAMIC SQL in DB2 with the help of an example
• Explain SHARED, UPDATE and EXCLUSIVE locks with the help of an example
• What is NON CLUSTERED INDEX in DB2? Explain with the help of practical example
• What is the significance of the StringTokenizer class of the Java? Explain with an example?
• What is a use of SQLWARN3 in a SQLCA? Explain with the help of a practical example?
• What is the character count? Explain with an example?
• What is the resemblance of COALESCE() function with IF-THEN-ELSE statement?