CODEWITHSUNDEEP
sqldb2sql-update
user1527185
user1527185

Reputation:

DB2 SQL Updating a table value

I'm trying to update a table value that is initially set to 0, I'm working with DB2. However, when I go to execute my SQL I get the following error: 

DSNT408I SQLCODE = -406, ERROR:  A CALCULATED OR DERIVED NUMERIC VALUE IS NOT
         WITHIN THE RANGE OF ITS OBJECT COLUMN                               
DSNT418I SQLSTATE   = 22003 SQLSTATE RETURN CODE

I understand what the error means, but I do not understand why I am getting it. Here is my SQL:

UPDATE INTTABLE                                      
  SET PAYMENT = DECIMAL((MONTHIRATE*OMA)/(1-POWER(1+MONTHIRATE,-420)),8,2);

Where PAYMENT is defined as DECIMAL(8,2)

Could someone please explain to me why the above UPDATE statement will not work?

Upvotes: 3

Views: 3218

Answers (1)

bhamby
bhamby

Reputation: 15440

Probably what is happening is that the calculation you are doing is somewhere getting a result with more than 6 digits before the decimal place.

DB2 will handle having more numbers after the decimal place than you have defined in the SCALE, but it will error (with the -406 you are seeing) when there are more digits than allowed with the PRECISION defined.

Just as an aside, do make sure you realize that a DECIMAL(8,2) will give you 6 places before the decimal and 2 after.

Edit: I think this query will show you the offending row(s):

SELECT * FROM ( 
    SELECT 
         A.*
        ,(MONTHIRATE*OMA)/(1-POWER(1+MONTHIRATE,-420)) AS CALC
    FROM INTTABLE A
) B
WHERE CALC > 999999

Upvotes: 1

Related Questions