Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    37

    Unanswered: Unexpected rounding - DB2 v9.7.0.2 Fixpack 2

    Hey all,
    What should be very quick DB question but its driving me nuts!.........if we defined a DECIMAL column (28, 6) and I try to set the value as 0.123456 directly using SQuirreL it results in 0.12346 when I select it back out (i.e. its been rounded to 5 places). Surely it should fit as it is since the six decimal places are available?

    However, if the column is DECIMAL (5, 2) and I set the value to 0.49 is works as expected - no rounding?!

    This also seems to happen in Oracle and MS Sql (irrelevant I know, just an FYI)

    Whats going on?! As above, DB2 v9.7.0.2 Fixpack 2.

    cheers
    Last edited by FLANDERS; 10-16-12 at 13:24.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by FLANDERS View Post

    This also seems to happen in Oracle and MS Sql (irrelevant I know, just an FYI)

    Whats going on?!
    I think this question should be addressed to "SQuirreL", whatever that is.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2009
    Posts
    37
    SQuirreL is a DB client written in java.

    Can you confirm my assumption that I should be able to insert and retrieve 0.123456 into a DECIMAL (28, 6) without any truncation/rounding?

    (I dont have db2cmd immediately to hand to test it directly against DB2)

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    No, DB2, Oracle, and MS SQL Server all truncate decimal numbers to 5 decimal places, and you're the first one to notice that in 2012.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2009
    Posts
    37

    Angry

    I love smart a$$ answers to honest questions, theyre great. Thanks alot n_i, you rock

    I'd love to see you be a beginner or struggle with something other people find easy.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by FLANDERS View Post
    I'd love to see you be a beginner or struggle with something other people find easy.
    This is not inconceivable; I'll let you know when that happens.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by n_i View Post
    No, DB2, Oracle, and MS SQL Server all truncate decimal numbers to 5 decimal places
    That's complete and utter nonsense.

    directly using SQuirreL it results in 0.12346 when I select it back out
    I would suspect SQuirrel to do that rounding. Search the manual or the online help for decimal formatting options.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  8. #8
    Join Date
    Nov 2009
    Posts
    37
    Quote Originally Posted by shammat View Post
    That's complete and utter nonsense.

    I would suspect SQuirrel to do that rounding. Search the manual or the online help for decimal formatting options.
    Thanks for the reply, it never struck me that the common denominator was SQuirreL, forgot to check for its options.

  9. #9
    Join Date
    Nov 2009
    Posts
    37
    YEah was SQuirreL setting it 5 places by default. Thanks a mill shammat

Posting Permissions

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