Results 1 to 7 of 7
Thread: Sql  Command Round

052607, 13:13 #1Registered User
 Join Date
 Apr 2007
 Posts
 51
Unanswered: Sql  Command Round
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)Last edited by annamaria; 052607 at 16:13.

052607, 18:18 #2Registered User
 Join Date
 Jan 2003
 Posts
 4,296
Provided Answers: 5Originally Posted by annamaria
I tried 4.867/360*30 and I got a rounded number of 0.406
Andy

052707, 05:43 #3Registered User
 Join Date
 Apr 2007
 Posts
 51
Originally Posted by ARWinner
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)

052707, 07:03 #4Registered User
 Join Date
 Jan 2007
 Location
 Jena, Germany
 Posts
 2,721
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.Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development

052707, 09:48 #5Registered User
 Join Date
 Apr 2007
 Posts
 51
Round Function
Originally Posted by stolze
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)

052707, 11:18 #6Registered User
 Join Date
 Jan 2007
 Location
 Jena, Germany
 Posts
 2,721
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 binaryencoded 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/8063568/ncg_goldberg.htmlLast edited by stolze; 052707 at 11:21.
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development

052907, 02:22 #7Registered User
 Join Date
 Dec 2005
 Posts
 273
simply rewrite your query:
ROUND((FIELD1 * FIELD5 / 360 ),3)
instead of
ROUND((FIELD1 / 360 * FIELD5),3)