DB2- Use of ‘COALESCE’ Function (1 of 2)

MAINFRAME+JOBS
MAINFRAME+JOBS

The “COALESCE” function is one of the frequent used function in DB2. Before going into detail, I just want to describe it in layman terms.

This function validates for NULL, and secondly, if NULL is there it assumes some value as we give in the function.

Syntax: COALESCE(epression1,expression2)

It is like decision maker. Its uses are many.

Usage-1:

CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END.

In other way we can write:

CASE COALESCE(expression1,expression2) END.

Usage-2:

WHERE COALESCE (MARKS1,0) + MARKS2 > 100

If MARKS1 is null it assumes as zero ‘0’.

Usage-3:

SELECT COALESCE(EMP.DEPT,ROLE.DEPT) AS DEPT

In this case neither of the DEPT of EMP and ROLE tables is NULL.

In my next I will give more on this function.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Comments are closed.