coalesce

coalesce examines a series of values (value1, value2, ..., valuen) and returns the first non-null value.

The syntax of coalesce is:

coalesce(value1, value2, ..., valuen)

where value1, value2, ..., valuen are expressions. If value1 is non-null, the value of coalesce is value1; if value1 is null, value2 is examined, and so on. The examination continues until a non-null value is found. The first non-null value becomes the value of coalesce.

When you use coalesce, SAP ASE translates it internally to:

case
    when value1 is not NULL then value1
    when value2 is not NULL then value2
    . . . 
    when valuen-1 is not NULL then valuen-1
    else valuen
end

valuen-1 refers to the next to last value, before the final value, valuen.

The example below uses coalesce to determine whether a store orders a low quantity (more than 100 but less than 1000) or a high quantity of books (more than 1000):

select stor_id, discount, "Quantity" =
    coalesce(lowqty, highqty)
from discounts

stor_id discount    Quantity
------- --------   ---------   ----
NULL               10.500000   NULL 
NULL                6.700000    100 
NULL               10.000000   1001 
8042                5.000000   NULL 
 
(4 rows affected)