How to Use the COALESCE function in Oracle

Welcome to the Oracle community forum and interact with 2 million technical staff. Many times, database application software stores multiple and related entity information in the same table. For example, the purchased parts and locally produced parts are both parts, and managers and workers are employees. Although multiple physical types are slightly different in data storage, they have too many

Welcome to the Oracle community forum and interact with 2 million technical staff> go to the database application software and save multiple and related entity information in the same table. For example, the purchased parts and locally produced parts are both parts, and managers and workers are employees. Although multiple physical types are slightly different in data storage, they have too many

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Database Application Software often saves multiple and related entity information in the same table. For example, the purchased parts and locally produced parts are both parts, and the managers and workers are employees. Although multiple entity types are slightly different in data storage, they have many similarities, therefore, we usually use one table instead of two tables.

Processing such a table usually means performing a condition test on each row to check which object type is being processed, and then returning different result sets of each type. The CASE statement can be used to complete this task.

Since Oracle 9i, COALESCE functions have become a shortcut to replace CASE statements in many cases. The format of COALESCE is as follows:

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

The first non-empty expression in the list is the return value of the function. If all expressions are null, a null value is returned.

The secret to using COALESCE is that most expressions containing null values will eventually return NULL values (the concatenation operator "|" is an exception worth noting ). For example, a null value plus any value is a null value, and a null value multiplied by any value is also a null value, and so on.

In this way, you can construct a series of expressions. The result of each expression is null or non-null. This is like a CASE statement. The expressions are tested in order, the first value that satisfies the condition determines the result.

List A shows A part of A table named PARTS, which stores the purchased part data and the produced part data, the value of the part_type column is 'P'. If it is locally produced or assembled, It is 'B'. In addition, the purchase_cost column shows the purchase cost for the purchased parts, the locally produced parts are null values. In addition, the locally produced parts include material_qty and material_cost columns, and the purchased parts are null values.

List:

SQL> desc partsName Null? TypePART_ID NOT NULL NUMBER(6)PART_TYPE NOT NULL CHAR(1)(other columns)PURCHASE_COST NUMBER(8,2)MATERIAL_COST NUMBER(8,2)MATERIAL_QTY NUMBER(6)LAST_UPDATED TIMESTAMP(6)UPDATED_BY VARCHAR2(30)

You can use a CASE statement to test the value of the part_type column and return the product of the either purchase_cost and material_qty columns and material_cost columns. However, COALESCE can solve this problem with one line of statements:

COALESCE (purchase_cost, material_qty * material_cost)

If a purchased part is stored in the data row, purchase_cost is not a null value and the value of purchase_cost is returned. However, for locally produced parts, purchase_cost is a null value, COALESCE ignores it, then multiply material_qty and material_cost, and return the product as the result.

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

You can use this pattern repeatedly for any number of expressions. COALESCE is a very convenient way to evaluate multiple entities in a unified table.

Finally, let's talk about the advantages of CASE statements, that is, CASE records are automatically recorded, which facilitates understanding and interpreting what is happening.