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.
One thought
Comments are closed.