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

coalesce – evaluates the listed expressions and returns the first non-null value. If all expressions are null, coalesce returns NULL.
expression – is a column name, a constant, a function, a subquery, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators.

Examples

Example 1 – Returns the first occurrence of a non-null value in either the lowqty or highqty column of the discounts table:
```
select stor_id, discount,
        coalesce (lowqty, highqty)
from discounts
```

Example 2 – An alternative way of writing the previous example:

select stor_id, discount, 
        case
            when lowqty is not NULL then lowqty
            else highqty
        end
from discounts

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.