Share via:
Purpose of COALESCE expression

Dear Readers,

In this article you will get to know the Purpose of COALESCE expression

  • It is used for NULL Handling.
  • In the COALESCE expression, a list of arguments is passed. It is used to check if the arguments are NULL.
    • The output is NULL if all arguments evaluate to NULL.
    • Else, it will check for NOT NULL values sequentially in the list and will return the first Non-Null value.


Alternative function to COALESCE ?

Pre-requisite to use the function ?

Is it ANSI Compliance ?

Example ?

Here is how it works – first it shall check if col_1 is null. If yes, then it will check col_2.

If col_2 is also null then it will check for col_3.
If col_3 is also null, then the output will be NULL.

If coalesce encounters any of the column having not null value then the column value will be returned by the query. (Left to right priority)

Additional information :

  • Anexpression_n in the argument list may be evaluated twice: once as a search condition and again as areturn value for that search condition.
  • Using a nondeterministic function, such as RANDOM, in an expression_n may have unexpected results, because if the first calculation of expression_n is not NULL, the second calculation of thatexpression_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.

Default Title ?

  • The default title for a COALESCE expression appears as:

<CASE expression>

Any data types that have restrictions in a COALESCE Expression ?

Yes, there are restrictions on CLOB, BLOB, UDT types when they are to be used in the argument list. These restrictions are :

  • A BLOB should be casted to BYTE or VARBYTE.
  • A CLOB should be casted to CHAR or VARCHAR.
  • Multiple UDTs should be identical types because Teradata Database does not perform implicit type conversion on UDTs in a COALESCE expression.

COALESCE with non-comparison operators ?

When non-comparison operators like || , *, + are to be used in COALESCE function, the entire COALESCE function and its operands must be enclosed in parenthesis.

Thank you for giving your valuable time to read the above information.
Follow us on 
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Add Comment