1. Registered User
Join Date
Jun 2009
Posts
1

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. Registered User
Join Date
Dec 2008
Posts
76
DB2 version? How are the columns defined?

3. Registered User
Join Date
Dec 2007
Location
Richmond, VA
Posts
1,328
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. Registered User
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
•