The Teradata COALESCE Function As Shortcut For CASE Statements

The Teradata COALESCE Function As Shortcut For CASE Statements

What is the purpose of the Teradata COALESCE function?

COALESCE receives a list of scalars as arguments and returns the first value that is not NULL. If all scalars are NULL COALESCE returns NULL.

What is the syntax of the Teradata COALESCE function?

SELECT COALESCE(skalar1, skalar2, skalar3,…,skalarN) FROM Customer;

Is there an alternative to the Teradata COALESCE function?

The COALESCE function is a simplified option for the following CASE statement:

SELECT
CASE
WHEN skalar1 IS NOT NULL THEN skalar1
WHEN skalar2 IS NOT NULL THEN skalar2
..
WHEN skalarN IS NOT NULL THEN skalarN
ELSE NULL
END
FROM Customer;

Which data types cannot be used as scalars in Teradata COALESCE or can only be used to a limited extent?

BLOB scalars must be cast to BYTE or VARBYTE.
Scalars of the type CLOB must be cast to CHAR or VARCHAR.
Several UDT scalars must all be of the same type.

Is the COALESCE function ANSI Compliant?

Yes, it is ANSI SQL:2011 compliant.

Teradata Data Type Considerations
Teradata Data Types And Data Modeling
Use Teradata Recursions to build a Calendar
DWH Pro Admin
 

>