CODEWITHSUNDEEP
db2cobolmainframe
Nilanjan
Nilanjan

Reputation: 1

Decimal Overflow in DB2

I am doing the below SQL in DB2V10.

select
  SUM (ORD_QTY * IFNULL(SELL_AMT,0) * IFNULL(WGT_QTY,0))
  INTO :WS-VAR FROM TABA with ur;

 ORD_QTY  => INTEGER
 SELL_AMT => DECIMAL(13,4)
 WGT_QTY  => DECIMAL(11,4)
 WS-VAR   => PIC S9(13)V9(4) USAGE COMP-3.

I am getting +802 DECIMAL OVERFLOW error.

I am not understanding why exactly I am getting decimal overflow here. Can someone explain me please!

Upvotes: 0

Views: 912

Answers (1)

mustaccio
mustaccio

Reputation: 19005

According to the numeric result rules, the resulting precision of SUM (ORD_QTY * IFNULL(SELL_AMT,0) * IFNULL(WGT_QTY,0)) is DECIMAL(15,4), which can hold a value up to the 1011 range.

At the same time, the actual result could be in the range of up to 1010 * 109 * 107, which adds up to 1026.

Upvotes: 1

Related Questions