Re: ROUND Function

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 15 Mar 2003 09:47:18 GMT
Message-ID: <b4usr5$23f31s$1@ID-82536.news.dfncis.de>

> 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.00*7.00,2)
> ---------------------------
> 1159.6
>
> 2.
> SQL> select ROUND(2319.21*7/14,2) from dual;
>
> ROUND(2319.21*7/14,2)
> ---------------------
> 1159.61

This seems to be a floating point calculation error:

SQL> select
to_CHAR(2319.21/14*7,

'9990.0000000000000000000000000000000000000000000000000000000000')

FROM DUAL;

TO_CHAR(2319.21/14*7,'9990.0000000000000000000000000000000000000
----------------------------------------------------------------
 1159.6049999999999999999999999999999999990000000000000000000000

SQL> select
to_CHAR(2319.21*7/14,

'9990.0000000000000000000000000000000000000000000000000000000000')

FROM DUAL;

TO_CHAR(2319.21*7/14,'9990.0000000000000000000000000000000000000
----------------------------------------------------------------
 1159.6050000000000000000000000000000000000000000000000000000000

What seems to be equal on first sight, is internally a different number and thus rounded differently.

If you calculate 2319.21/14, the result is (at least on 9i on Windows);

SQL> select
to_CHAR(2319.21/14,

'9990.0000000000000000000000000000000000000000000000000000000000')

FROM DUAL;

TO_CHAR(2319.21/14,'9990.000000000000000000000000000000000000000
----------------------------------------------------------------
  165.6578571428571428571428571428571428570000000000000000000000

but, it actually should be:
165.6578571428571428571428571428571428571428571428571428571428571428571428.....

hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Sat Mar 15 2003 - 03:47:18 CST