Coalesce function in Teradata

In Teradata Coalesce function returns NULL if all its arguments evaluate to null. Otherwise, it returns the value of the first non-null argument in the scalar_expression list.

COALESCE is a shorthand expression for the following full CASE expression:

   CASE 
    WHEN scalar_expression_1 IS NOT NULL 
    THEN scalar_expression_1 
    ...
    WHEN scalar_expression_n IS NOT NULL 
    THEN scalar_expression_n 
    ELSE NULL 
   END

Example of Teradata COALESCE function

The following example returns the home phone number of the named individual (if present), or office phone if HomePhone is null, or MessageService if present and both home and office phone values are null. Returns NULL if all three values are null.

   SELECT Name, COALESCE (HomePhone, OfficePhone, MessageService) 
   FROM PhoneDir;

Example of Teradata COALESCE function

The following example uses COALESCE with an arithmetic operator.

   SELECT COALESCE(Boxes,0) * 100
   FROM Shipments;

Example of Teradata COALESCE function

The following example uses COALESCE with a comparison operator.

   SELECT Name
   FROM Directory
   WHERE Organization  COALESCE (Level1, Level2, Level3);