coalesce

Supports conditional SQL expressions; can be used anywhere a value expression can be used; alternative for a case expression. coalesce expression simplifies standard SQL expressions by allowing you to express a search condition as a simple comparison instead of using a when...then construct.

Syntax

coalesce(expression, expression [, expression]...)

Parameters

Examples

Usage

  • You can use coalesce expressions anywhere an expression in SQL.

  • At least one result of the coalesce expression must return a non-null value. This example produces the following error message:
    select price, coalesce (NULL, NULL, NULL)
    from titles
    All result expressions in a CASE expression must not be NULL.
  • If your query produces a variety of datatypes, the datatype of a case expression result is determined by datatype hierarchy. If you specify two datatypes that the SAP ASE server cannot implicitly convert (for example, char and int), the query fails.

  • coalesce is an abbreviated form of a case expression. Example 2 describes an alternative way of writing the coalesce statement.

  • coalesce must be followed by at least two expressions. This example produces the following error message:
    select stor_id, discount, coalesce (highqty)
    from discounts
    A single coalesce element is illegal in a COALESCE expression.

See also case, nullif, select, if...else, where clause in Reference Manual: Commands

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute coalesce.

Related concepts
Expressions
Datatypes of Mixed-Mode Expressions