CODEWITHSUNDEEP
sqloracle-databaseoracle11g
mefist
mefist

Reputation: 23

Oracle ROUND function returns wrong value

I run this sql statement on Oracle 11g database

select round(79/3 + 87/3 + 86/6 + 95/6) from dual

and it returns 85

select round(79/3 + 87/3 + 86/6 + 95/6,1) from dual

returns 85.5

select round(85.5) from dual

returns a correct value 86

Does anyone know why the first SQL statement doesn't return a correct value 86 but it rounds it down to 85 ??

Upvotes: 2

Views: 6987

Answers (4)

Aparna Devi
Aparna Devi

Reputation: 1

Even I m not sure of why the Oracle is losing its precision, but as a work around you may use it like this

SELECT round(round(79/3 + 87/3 + 86/6 + 95/6,4 ),0) FROM DUAL

Upvotes: 0

Java SE
Java SE

Reputation: 2073

79/3 + 87/3 + 86/6 + 95/6 will return floating point arithmetic which doesn't have the rounding behavior of IEEE 754. See the difference between Number and Float here

To get the correct result back you need to run the statement as:

select round(to_number(79/3 + 87/3 + 86/6 + 95/6)) from dual;

Upvotes: 1

Craton
Craton

Reputation: 1

It looks to be a curious loss of precision.

SELECT 79/3 + 87/3 + 86/6 + 95/6 FROM DUAL

85.49999999999999999999999999999999999999

Obviously 85.4 will then round to 85. As to why Oracle loses the precision in this case, I'm not sure.

Upvotes: -1

Xophmeister
Xophmeister

Reputation: 9211

If you do:

select 79/3 + 87/3 + 86/6 + 95/6 from dual;

Oracle will return 85.49999999999999999999999999999999999999, which when rounded to 0 decimal places is indeed 85. It's an artifact of floating point arithmetic.

Upvotes: 10

Related Questions