I HAVE DIFFICULTIES WITH THE COMMAND 'ROUND'.
I HAVE THE FOLLOWING OPERATION:
ROUND((FIELD1 / 360 * FIELD5),3) AS ZEROC (I want the result to be rounded at the third decimal)
BUT THE FUNCTION MAKES MISTAKES IN ROUNDING. IT SEEMS TO ME THAT
THE PROBLEM RELIES IN THE NUMBER OF DECIMALS TAKEN IN EVERY
SINGLE STEP OF THE OPERATION.
Take this example: when it has to calculate
4,866 / 360 * 30 the result is 0,405 instead
of 0,406
(maybe SQL truncates 4,866/360 at the tenth decimal = 0,0135166666
- maybe it truncates because number six recurs (it's periodic);
then it calculates 0,0135166666 * 30 = 0,405499998 which, rounded at the
third decimal is 0,405.

Thank you
Anna - Verona










CAN ANYONE HELP ME?
MAYBE UMAYER?
THANK YOU.
ANNA - VERONA (ITALY)

When I caclulate 4.866/360*30 i get 0.4055 even. That will round to 0.405.

I tried 4.867/360*30 and I got a rounded number of 0.406

Andy

I also get 0,4055 with 4,866/360*30 and it should be rounded to 0,406.
Instead, it is rounded to 0,405 maybe because even though it shows the
number 0,4055, underneath (you know, there is a memory for numbers used in calculations) the real number is 0,4054999998. So first it shows the
number 0,4055, but when it comes to rounding it remembers the number
underneath.

Maybe UMAYER could help me with this matter.

Ciao.
Anna- Verona (Italy)

We should have the value of HEX(field1) first, before assuming what the result should be. The thing is that a value of 4.866 is already rounded due to the binary->string conversion. This is especially true if we have periodic values. So the only thing that makes sense here is to take the internal binary representation and verify DB2's result by performing the calculations in binary system - not in decimal system as was done above.

My guess is that the internal, binary result is something like 0.405499 or so, which gets properly rounded to 0.405. (That's just a guess, though!)

p.s: ROUND is a function and not a command.

I know that the number underneath is 0,4054999998 because when I copy
the query result in Excel the cell shows 0,4055 but in the function line at
the top the number shown is 0,4054999998.
I'd really like to know how SQL works and if the problem is with SQL or with
something else in my application.
I'll try with the verification of binary system calculations.
Bye.
Anna - Verona (Italy)

No, the problem is not with SQL or the round function but simply that floating point numbers are represented in binary format in binary system internally. Every time you retrieve a number in textual form (be it for Excel or at the console doesn't matter at all), it has to be converted to the decimal system. And that conversion is often not lossless because some numbers can be represented exactly in one system but not the other. Unless you verify your results in binary system, you only look at rounding differences at the various points, which are simply inherent. Therefore, I suggest that you write a small C/C++ or Java program (or UDF) that does the same calculations by retrieving the binary-encoded floating point values, and then gives you the result so that you can compare with DB2's output.

I recommend that you read this article because it explains the basics of floating points and what you should be aware of if you use them: http://docs.sun.com/source/806-3568/ncg_goldberg.html

simply rewrite your query:

ROUND((FIELD1 * FIELD5 / 360 ),3)

instead of

ROUND((FIELD1 / 360 * FIELD5),3)