Home arrow Oracle arrow Page 2 - Retrieving Data with String and Arithmatic Expressions

How to code arithmetic expressions - Oracle

In this third part of a nine-part series on retrieving data from tables with the SELECT statement, you will learn how to code both string and arithmetic expressions. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

TABLE OF CONTENTS:
  1. Retrieving Data with String and Arithmatic Expressions
  2. How to code arithmetic expressions
By: Murach Publishing
Rating: starstarstarstarstar / 0
July 28, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Figure 3-6 shows how to code arithmetic expressions. To start, it summarizes the four arithmetic operators you can use in this type of expression. Then, it presents two examples that show how to use these operators.

The SELECT statement in the first example includes an arithmetic expression that calculates the balance due for an invoice. This expression subtracts the payment_total and credit_total columns from the invoice_total column. The resulting column is given the name balance_due.

When Oracle evaluates an arithmetic expression, it performs the operations from left to right based on the order of precedence. This order says that multiplication and division are done first, followed by addition and subtraction. If thatís not what you want, you can use parentheses to specify how you want an expression evaluated. Then, the expressions in the innermost sets of parentheses are evaluated first, followed by the expressions in outer sets of parentheses. Within each set of parentheses, the expression is evaluated from left to right in the order of precedence.

To illustrate how parentheses and the order of precedence affect the evaluation of an expression, consider the second example in this figure. Here, the expressions in the second and third columns both use the same operators. However, when Oracle evaluates the expression in the second column, it performs the multiplication operation before the addition operation because multiplication comes before addition in the order of precedence. In contrast, when Oracle evaluates the expression in the third column, it performs the addition operation first because itís enclosed in parentheses. As you can see in the result set, these two expressions result in different values.

Unlike some other databases, Oracle doesnít provide a modulo operator that can be used to return the remainder of a division operation. Instead, you must use the MOD function as described in the next figure.

The arithmetic operators in order of precedence

A SELECT statement that calculates the balance due

SELECT invoice_total, payment_total, credit_total,
invoice_total - payment_total - credit_total AS balance_due
FROM invoices

A SELECT statement that uses parentheses to control the sequence of operations

SELECT invoice_id,
invoice_id + 7 * 3 AS order_of_precedence,
(invoice_id + 7) * 3 AS add_first
FROM invoices
ORDER BY invoice_id

Description

  1. Unless parentheses are used, the operations in an expression take place from left to right in the order of precedence. For arithmetic expressions, multiplication and division are done first, followed by addition and subtraction.
  2. Whenever necessary, you can use parentheses to clarify or override the sequence of operations. Then, the operations in the innermost sets of parentheses are done first, followed by the operations in the next sets, and so on.

--------------------------------------------Figure 3-6 How to code arithmetic expressions

Please check back for the continuation of this series.



 
 
>>> More Oracle Articles          >>> More By Murach Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: