Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
coalesce::=
COALESCE
returns the first non-null expr
in the expression list. At least one expr must not be the literal NULL
. If all occurrences of expr
evaluate to null, then the function returns null.
This function is a generalization of the NVL
function.
You can also use COALESCE
as a variety of the CASE
expression. For example,
COALESCE (expr1, expr2)
is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END
Similarly,
COALESCE (expr1, expr2, ..., exprn
), forn
>=3
is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1
ELSE COALESCE (expr2, ..., exprn
) END
The following example uses the sample oe.product_information
table to organize a "clearance sale" of products. It gives a 10% discount to all products with a list price. If there is no list price, then the sale price is the minimum price. If there is no minimum price, then the sale price is "5":
SELECT product_id, list_price, min_price, COALESCE(0.9*list_price, min_price, 5) "Sale" FROM product_information WHERE supplier_id = 102050; PRODUCT_ID LIST_PRICE MIN_PRICE Sale ---------- ---------- ---------- ---------- 2382 850 731 765 3355 5 1770 73 73 2378 305 247 274.5 1769 48 43.2