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.
It is like decision maker. Its uses are many.
CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END.
In other way we can write:
CASE COALESCE(expression1,expression2) END.
WHERE COALESCE (MARKS1,0) + MARKS2 > 100
If MARKS1 is null it assumes as zero ‘0’.
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.