Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: SQL0802N Arithmetic overflow error

    Hi Everybody!

    Please, I need a genius...

    DB2 V5R2.

    I'm doing a very simple query through an adhoc SQL tool. The problem is not dependent on the tool, I've used several and get the same error. The connection is through ODBC.

    Here is the data:

    USRNUM INCEXC1 BEGFLD1 ENDFLD1 INCEXC2 BEGFLD2 ENDFLD2
    BENFOC60
    BENFOC61 E 000002 000002 E 008557 008558
    BENFOC62 E 000002 000002 E 008557 008558
    BENFOC63 E 000002 000002 E 008557 008558

    Here is the problem statement:

    SELECT
    SECFLPR.SCUMAS.USRNUM,
    SECFLPR.SCUMAS.INCEXC1,
    SECFLPR.SCUMAS.BEGFLD1,
    SECFLPR.SCUMAS.ENDFLD1,
    SECFLPR.SCUMAS.INCEXC2,
    SECFLPR.SCUMAS.BEGFLD2,
    SECFLPR.SCUMAS.ENDFLD2
    FROM
    SECFLPR.SCUMAS
    WHERE
    SECFLPR.SCUMAS.USRNUM LIKE 'BENFOC6%'
    AND (
    (SECFLPR.SCUMAS.INCEXC1 = 'E'
    AND decimal(SECFLPR.SCUMAS.BEGFLD1)<= 2
    AND decimal(SECFLPR.SCUMAS.ENDFLD1)>= 2)
    OR
    (SECFLPR.SCUMAS.INCEXC002 = 'E'
    AND decimal(SECFLPR.SCUMAS.BEGFLD2)<= 8558
    AND decimal(SECFLPR.SCUMAS.ENDFLD2)>= 8558))

    Error: SQL0802N Arithmetic overflow or other arithmetic exception occurred. SQLSTATE=22023
    State:22023,Native:-802,Origin:[IBM][CLI Driver][AS]


    Change the OR to an AND and it works fine (but of course, is not what I need!):

    SELECT
    SECFLPR.SCUMAS.USRNUM,
    SECFLPR.SCUMAS.INCEXC1,
    SECFLPR.SCUMAS.BEGFLD1,
    SECFLPR.SCUMAS.ENDFLD1,
    SECFLPR.SCUMAS.INCEXC2,
    SECFLPR.SCUMAS.BEGFLD2,
    SECFLPR.SCUMAS.ENDFLD2
    FROM
    SECFLPR.SCUMAS
    WHERE
    SECFLPR.SCUMAS.USRNUM LIKE 'BENFOC6%'
    AND (
    (SECFLPR.SCUMAS.INCEXC001 = 'E'
    AND decimal(SECFLPR.SCUMAS.BEGFLD1)<= 2
    AND decimal(SECFLPR.SCUMAS.ENDFLD1)>= 2)
    AND
    (SECFLPR.SCUMAS.INCEXC002 = 'E'
    AND decimal(SECFLPR.SCUMAS.BEGFLD2)<= 8557
    AND decimal(SECFLPR.SCUMAS.ENDFLD2)>= 8557))

    It also works fine if you use either one of the clauses. It's just the combination of them with OR that doesn't work. I'm attempting to convert the BEGFLD and ENDFLD character values into numbers and then test for a value either equal to or between the two. There may be Nulls in the any of the fields, as in Usernum 'BENFOC60'.

    Help, please! Thanks in advance!

    StephanieL

  2. #2
    Join Date
    Nov 2004
    Posts
    374

    arithmatic

    Is the version really 5.2 ? (udb ?? / platform ?)
    this version is not supported anymore or is this 5.2 on i-series ? (eq. to 8)
    if so have an odbc/cli trace and open a pmr ?
    check if not a known problem.
    Hope this will help
    Best Regards, Guy Przytula
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

Posting Permissions

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