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);