Question & Answer
Question
Why are the correct results returned for one order of the operands and not the other? The DDL is: CREATE TABLE VORKCJ.TESTCALC (NUM1 DECIMAL(18,3) NOT NULL , NUM2 DECIMAL(18,3) NOT NULL ) IN DATABASE DSNDB04 AUDIT NONE CCSID EBCDIC; The SELECT statement is: SELECT NUM1*NUM2,NUM1,NUM2 FROM VORKCJ.TESTCALC; The data in the table is: NUM1 NUM2 1300.000 54.070
Cause
This problem is a decimal precision issue.
Answer
The decimal arithmetic is working as it should.
Consider the following two cases:
a) num1 * num2 (1300.000 * 54.070)
b) num2 * num1 (54.070 * 1300.000)
From the SQL Reference, section 2.20.6.2 Decimal multiplication:
If both operands have a precision greater than 15, the operation is performed using a temporary copy of the operand with the smaller precision. If the operands have the same precision, the second operand is selected.
In both cases (a and b), both operands have a precision of 18. Therefore, the second operand is selected to be used in the temporary copy.
The SQL Reference then states the following information:
If more than 15 significant digits are needed for the integral part of the copy, the statement's execution is ended and an error occurs. Otherwise, the copy is converted to a number with precision 15, by truncating the copy on the right.
Therefore, DB2® handles cases a and b as follows:
a) num2 is truncated (54.070)
b) num1 is truncated (1300.000)
The SQL Reference further states:
The truncated copy has a scale of MAX(0,S-(P-15)), where P and S are the original precision and scale. If, in the process of truncation, one or more nonzero digits are removed, SQLWARN7 in SQLCA is set to W, indicating loss of precision.
In both cases, the truncated copy has a scale of MAX(0,3-(18-15))=0
Therefore, after truncation the following actions occur:
a) num2 = 54 ---> If the fractional digits are lost, as is the case here, the sqlwarn7 flag is set.
b) num1=1300----> The factional digits are not lost.
Then the following actions occur:
a) we multiply num1*num2(1300*54) = 70200.000
b) we multiply num2*num1(54.070*1300) = 70291.000
Note: You can find the latest version of the SQL Reference for your version of DB2 at the DB2 for z/OS® and OS/390® library web page.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21137115