Hello!

Please help! My query is giving out an error of ERROR: EXCEPTION ERROR DECIMAL OVERFLOW HAS OCCURRED when executing on DB2I SPUFI. But the error I got when I ran the query on a QMF screen is Arithmetic operation in the SQL command has resulted in an error.

SELECT HLF.DIVISION
, COST_LIFO_LYB
, COST_LIFO_TYB
, (COST_LIFO_TYB/COST_LIFO_LYB-1)*100
, (COST_LIFO_TYB-COST_LIFO_LYB)*STI.ON_HAND*(1/STA.SELECT_RATE)
FROM SQLDAT3.GLHISLFO HLF
, SQLDAT3.SSITMCDS CDS
, SQLDAT3.SSITMWDS WDS
, SQLDAT3.GLSTRITM STI
, SQLDAT3.GLPLSTAT STA
WHERE HLF.CORP_ITEM_CD = CDS.CORP_ITEM_CD
AND HLF.FACILITY = WDS.FACILITY
AND HLF.CORP_ITEM_CD = WDS.CORP_ITEM_CD
AND STI.DIVISION = HLF.DIVISION
AND STI.CORP_ITEM_CD = HLF.CORP_ITEM_CD
AND COST_LIFO_LYB > 0 -- AVOID ZERO DIVIDE
AND STA.SELECT_RATE > 0 -- AVOID ZERO DIVIDE
AND ((COST_LIFO_TYB-COST_LIFO_LYB)*STI.ON_HAND*(1/STA.SELECT_RATE) > 100000
OR (COST_LIFO_TYB-COST_LIFO_LYB)*STI.ON_HAND*(1/STA.SELECT_RATE) < -100000)

When I used 10000 on the last two criteria, query ran ok. I need it to run using 100000 as range.

One of my colleague advised me to use a case statement since he said that the problem is that DB2 is evaluating the last two criteria for every value of sta.select_rate even though you only want the ones greater than zero...but i'm not sure how to do it....

I assume the column is decimal type?

there are pretty complex rules about decimal operands. You can find them out in the 'with arithmetic operators' section in SQL reference book.

anyway, the simply trick is
SET CURRENT PRECISION = ’DEC31’;

there are other ways, such as use decimal() built-in-function to play with the precision and scale

Look at the SELECT columnn list. What happens when COST_LIFO_LYB = 1?

Nothing special. This is not Excel, so Microsoft operator precedence rules do not apply.

Hi nidm,

Thank you!!!! my query worked when I ran the >> SET CURRENT PRECISION = ’DEC31’;

question though, How can I include that line on my current query so I dont' need to run it separately. What I did is, run the set current precision first then ran my query. but it seems like I need to run it first everytime I need my query.

You're the best!!

Thanks,
wachutink

I am thinking 'yes' for dynamic stmts. There is a bind option :decprc (decimal precision), that you can try it out.

for your case, I may like to use 10000.00 instead of 10000. That will help the decimal operations.