IBM SQL/400 Update Statement Fails with SQL0406

Resolving The Problem

The following example illustrates how a seemingly correct IBM SQL/400 statement involving numeric calculations can fail with the following message: SQL0406 - Conversion error on assignment to column. This is the correct behavior based on scale and precision rules for SQL decimal operations.

1	Create the table: CREATE TABLE QTEMP/XVALUE (XVALUE NUMERIC ( 30, 9) NOT NULL WITH DEFAULT)
2	Insert a value: INSERT INTO QTEMP/XVALUE VALUES(0)
3	Attempt to update the value with the following statement: UPDATE QTEMP/XVALUE SET XVALUE = (1 + (10.25 /100)) * 69.50

The SQL statement fails with message SQL0406. However, the following statement will successfully insert the value:

UPDATE QTEMP/XVALUE SET XVALUE = (1 + (10.25 /100.00)) * 69.50

Why?

The following Decimal Arithmetic in SQL rules are documented in the SQL Reference under the section on Expressions.

The following formulas define the precision and scale of the result of decimal operations in SQL. The symbols p and s denote the precision and scale of the first operand and the symbols p' and s' denote the precision and scale of the second operand. Addition and Subtraction The scale of the result of addition and subtraction is max (s,s'). The precision is min(31,max(p-s,p'-s') +max(s,s')+1). Multiplication The precision of the result of multiplication is min (31,p+p') and the scale is min(31,s+s'). Division The precision of the result of division is 31. The scale is 31-p+s-s'. The scale must not be negative.

Now, apply these rules to the calculation in question:
(1 + (10.25 / 100)) * 69.50

The first evaluated is 10.25/100:

p=4, s=2, p'=3, s'=0
so p,s = 31, (31-p+s-s')
       = 31, (31-4+2+0)
       = 31, 29

The next evaluated is 1+(above result):

so p=1, s=0, p'=31, s'=29
so p,s = min(31,max(p-s,p'-s') +max(s,s')+1), max(s,s')
       = min(31,max(1-0,31-29) +max(0,29)+1), max(0,29)
       = min(31,max(1,2) + 29 +1), 29
       = min(31,2 + 30), 29
       = min(31,32), 29
       = 31, 29

Finally evaluated is the above result * 69.50:

so p=31, s=29, p'=4, s'=2
so p,s = min(31,p+p'), min(31,s+s')
       = min(31,31+4), min(31,29+2)
       = min(31,35), min(31,31)
       = 31,31

Therefore, the final precision and scale of the operation is (31,31). But, the actual result of the calculation, 76.62375, cannot go into (31,31) because it does not allow for any positions left of the decimal point.

By specifying the 100.00, the calculation changes such that the final (p,s) is (31,29), which will hold the result.

Another option is to use the decimal function; for example:

UPDATE QTEMP/XVALUE SET XVALUE = (1 + DECIMAL((10.25/100),10,6)) * 69.50

which would yield a final p,s of (20,8).