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

The “COALESCE” function is one of the frequently used functions 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.