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, Adaptive Server 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)
