Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Unanswered: Calculation Error

    I am using DB2 QMF 9.1 on my company's mainframe. We also have QMF for Windows version 8.1 fixpack 14, same problem is happening there.

    I have a table where I have stored very simple data from previous queries using the COUNT function. One row of data, col1 = 285; col2 = 217. In a query, I am trying a very simple math function:
    SELECT COL1/COL2
    FROM T_GROUP_NL_X3

    So it should be 285/217 = 1.3133. However, db2 is giving me a result of 1.3. When I multiply by 100, the answer is 130.0. That answer is incorrect. It seems to only want to read to the first decimal place. I have also tried using the DECIMAL command to expand to two decimal places. but then I get an error message 'Numeric value is outside allowable range for column.'

    I was able to change the data in the [T_GROUP_NL_X3]table to store it with two decimals; however, this has no effect when I try to do the calculation.

    Help! Thanks.

  2. #2
    Join Date
    Dec 2008
    Posts
    76
    DB2 version? How are the columns defined?

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think you just have to cast the numbers with the precision you want.
    such as the following:
    SELECT DECIMAL( DECIMAL(285,9,2) / DECIMAL(217,9,2),9,4)
    FROM SYSIBM.SYSDUMMY1

    COL1
    ------------
    1.3133
    Dasve Nance

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the data types of col1 and col2?

    I don't know the rules in QMF.
    But, result data type of decimal division in DB2 is clear.
    Decimal arithmetic in SQL
    ...
    ...
    Division: The precision of the result of division is 31. The scale is 31-p+s-s'. The scale must not be negative.
    Data type of the result you want(1.3133) woud be DECIMAL(31,4).

    To get the result DECIMAL(31,4), 31-p+s-s' = 4.
    Then, p-s+s' = 27

    Example:
    DECIMAL(31,4)/DECIMAL(p',0) = DECIMAL(31,4) (where 1 <= p' <= 31)

Posting Permissions

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