Results 1 to 7 of 7
  1. #1
    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; 05-26-07 at 16:13.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by annamaria
    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

  3. #3
    Join Date
    Apr 2007
    Posts
    51
    Quote Originally Posted by ARWinner
    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)

  4. #4
    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

  5. #5
    Join Date
    Apr 2007
    Posts
    51

    Round Function

    Quote Originally Posted by stolze
    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)

  6. #6
    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 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
    Last edited by stolze; 05-27-07 at 11:21.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Dec 2005
    Posts
    273
    simply rewrite your query:

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

    instead of

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •