DWHPro Insights

Teradata COALESCE Function: Purpose, Syntax, Alternative and Data Types

By Roland Wenzlofsky ·

Teradata COALESCE Function: Purpose, Syntax, Alternative and Data Types

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?

Scalar fixtures returned 42, NULL, fallback and 12.34 as expected. They do not validate BLOB/CLOB casts or UDT restrictions; consult the documented type rules for the actual expression. The scalar-name examples are templates requiring real columns, not complete executable tests.

Is the COALESCE function ANSI Compliant?

Yes, it is ANSI SQL:2011 compliant.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam