Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    5

    Unanswered: Help: ERROR: EXCEPTION ERROR DECIMAL OVERFLOW HAS OCCURRED

    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....
    Last edited by wachutink; 11-27-07 at 07:40.

  2. #2
    Join Date
    May 2003
    Posts
    113
    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

  3. #3
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    Look at the SELECT columnn list. What happens when COST_LIFO_LYB = 1?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jsharon1248
    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.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2007
    Posts
    5
    Quote Originally Posted by nidm
    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
    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
    Last edited by wachutink; 11-22-07 at 08:55.

  6. #6
    Join Date
    May 2003
    Posts
    113
    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.

    Quote Originally Posted by wachutink
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •