Decimal Overflow in DB2

Question

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!

Here's another link: ibm.com/support/knowledgecenter/SSEPEK_10.0.0/… . It's very simple, you've calculated a number which is too big. — Bill Woodger
– Bill Woodger, Commented Apr 28, 2016 at 22:31

mustaccio · Accepted Answer · 2016-04-28 16:13:25Z

1

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 10¹¹ range.

At the same time, the actual result could be in the range of up to 10¹⁰ * 10⁹ * 10⁷, which adds up to 10²⁶.

answered Apr 28, 2016 at 16:13

mustaccio

19.1k16 gold badges51 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Decimal Overflow in DB2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related