Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
Purpose
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.
Oracle Database uses short-circuit evaluation. That is, the database evaluates each expr
value and determines whether it is NULL
, rather than evaluating all of the expr
values before determining whether any of them is 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-10, "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
Examples
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