Data Management

Using Oracle's COALESCE function as a quick CASE statement

In this Oracle tip, Bob Watkins explains how you can use the COALESCE function in lieu of the more wordy CASE statement when testing for null results in multiple expressions.

Database applications sometimes store information about multiple, related entities in the same table. For example, purchased parts and parts built in-house are both parts. Managers and employees are both employees. Although the multiple entity types may have slight differences in the data stored about them, they have so many columns in common that a single table is used instead of two or more.

Processing such a table often means conditional testing of each row to see which entity type is being processed, then returning different results for each type. Not surprisingly, the CASE statement can be used for this.

Since Oracle version 9i, the SQL Server COALESCE function can be used as a shortcut for CASE in some situations. The general form of COALESCE is:

COALESCE (expression_1, expression_2, ...,expression_n)

The first non-null expression in the list is returned as the value of the function. If all expressions evaluate to NULL, NULL is returned.

The secret to using COALESCE as a form of a CASE statement is that most expressions involving NULL data will return NULL as a result. (The concatenation operator, ||, is a notable exception.) For example, NULL plus anything is NULL; NULL multiplied by anything is NULL, and so on.

Weekly Oracle tips in your inbox
TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more.
Automatically sign up today!

This gives you the ability to construct a set of expressions that each calculates a result that is either NULL or non-NULL. Like a CASE statement, the expressions are tested in order, and the first one that meets the condition (in this case, a non-NULL result) determines the result.

Listing A shows an excerpt of 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 any number of expressions, making COALESCE a handy shortcut to evaluate multiple entity types in the same table.

Finally, one argument in favor of the CASE statement for this application is that it's self-documenting, making it much easier to read and understand what is going on.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.


Editor's Picks