Quick Search:
 The Oracle COALESCE Function      [Return To Index] Jump to:  


The Oracle COALESCE function returns the first non-NULL expression in the list. If all expressions in the list evaluate to NULL, then the COALESCE function will return NULL. The Oracle COALESCE function makes use of "short-circuit evaluation". The database evaluates each expression's value and determines whether it is NULL, rather than evaluating all of the expressions before determining if any of them are NULL.

Example Syntax:

COALESCE( expression_1, expression_2, ... expression_n )

Using COALESCE Instead of CASE, courtesy of Bob Watkins
The COALESCE function can be used instead of the longer CASE statement when testing for NULL results in multiple expressions. The key to using the COALESCE function as a more concise form of a CASE statement is that most expressions involving NULL data will return NULL as a result. (Note that the concatenation operator, '||' is an exception.) For example, NULL plus or minus anything is NULL; NULL multiplied by anything is NULL, and so on.

For example, assume a 'Parts' table that is designed to hold both purchased and built parts. The part_type column will contain 'P' for purchased, and 'B' for parts built or assembled in-house. In addition, for purchased parts, there is a purchase_cost column that tells how much we pay for the part; this is NULL for built parts. On the other hand, built parts have material_qty and material_cost columns; these are NULL in the case of purchased parts.

You could use a CASE statement that tests the value of the part_type column and return either purchase_cost or material_qty times the material_cost, but COALESCE can do this for you in one step:

COALESCE(purchase_cost, material_qty * material_cost)

If a row describes a purchased part, purchase_cost is not NULL, and purchase_cost will be returned. However, if the part is built in-house, purchase_cost will be NULL and COALESCE will skip over it. The material_qty is then multiplied by the material_cost; since neither is NULL, the result is non-null and is returned.

SELECT part_id "Part", part_type "Type",
COALESCE(purchase_cost, material_qty * material_cost) "Cost"
FROM parts;

You can repeat this pattern for as many expressions as needed, using COALESCE as a shortcut to evaluate multiple entity types in the same table.

Related Links:
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org