Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 |
|
|
View PDF |
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.
If all occurrences of expr
are numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.
See Also: Table 2-11, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence " for information on numeric precedence |
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), for n>=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