How to use ORACLE coalesce Functions

Source: Internet
Author: User
Tags case statement

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.

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.