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

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