Re: Round function

On 14 Mar 2003 23:07:16 -0800, kmbase_at_rediffmail.com (Bala) wrote:

>Hi all,
>I execute the following two sql statement for ROUND
>function. It gives me two different result. The expected result is of
>No 2 statement output. what might be the problem, is it with
>round function or with the expression.
>
>1.
>SQL> select ROUND(2319.21/14*7,2) from dual;
>
>ROUND(2319.21/14*7,2)
>---------------------------
> 1159.6
>
>2.
>SQL> select ROUND(2319.21*7/14,2) from dual;
>
>ROUND(2319.21*7/14,2)
>---------------------
> 1159.61
>
>regards,
>Bala

Hi Bala,

This has to do with the limitation in Oracle's internal decimal precision (38 digits).

select ROUND(2319.21/14*7,2) from dual

is logicallly equal to

select ROUND(2319.21/2*1,2) from dual;

But the give different results. The expression is evaluated from right to left, so

1. 2319.21/14 = 165.65785714285714285714285714286...??

but

2) 2319.21/2 = 1159.605.

1. has a lot of decimals (maybe infinitely many), certainly more than 38. What does Oracle do then ? It throws away the extra digts, and then the result drops to, say, 1159.6049999 when you finally multiply with 7.
2. has only 7 digits, which is within Oracle's precision range.

You can try

select round(2319.21/a*b,2);
with a = 2 * b

for multiple values of a and thus b.

You will find that when the expression 2319,21/a contains more than 38 digits, the result may become incorrect.

• Kenneth Koenraadt