

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
ORDER_ID | ORDER_DATE | ORDER_DESCRIPTION | INVOICE_ID |
---|---|---|---|
5567812 | 2020-07-28 | NULL | A112786 |
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
- 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?
Advertisements