15.00 - COALESCE Expression - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

COALESCE Expression

Purpose

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

Syntax  

where:

 

Syntax element …

Specifies …

scalar_expression_n

an argument list.

Each COALESCE function must have at least two operands.

ANSI Compliance

This is ANSI SQL:2011 compliant.

Usage Notes

A scalar_expression_n in the argument list may be evaluated twice: once as a search condition and again as a return value for that search condition.

Using a nondeterministic function, such as RANDOM, in a scalar_expression_n may have unexpected results, because if the first calculation of scalar_expression_n is not NULL, the second calculation of that scalar_expression_n, which is returned as the value of the COALESCE expression, might be NULL.

You can use a scalar subquery in a COALESCE expression. However, if you use a non-scalar subquery (a subquery that returns more than one row), a runtime error is returned.

For additional information, such as the rules for evaluation and result data type, see “CASE” on page 559.

Default Title

The default title for a COALESCE expression appears as:

   <CASE expression>

Restrictions on the Data Types in a COALESCE Expression

The following restrictions apply to CLOB, BLOB, and UDT types in a COALESCE expression:

 

Data Type

Restrictions

BLOB

A BLOB can only appear in the argument list when it is cast to BYTE or VARBYTE.

CLOB

A CLOB can only appear in the argument list when it is cast to CHAR or VARCHAR.

UDT

Multiple UDTs can appear in the argument list only when they are identical types because Teradata Database does not perform implicit type conversion on UDTs in a COALESCE expression.

Example  

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  

The following example uses COALESCE with an arithmetic operator.

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

Example  

The following example uses COALESCE with a comparison operator.

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