I am trying to get an extended price from a JDE file were the quantity and unit price columns must first be divided by 100 and 10000 respectively. But the SQL statement passes the integrity check but gives me an Arithmetic overflow error in the result.
Message: SQL0802 Data conversion or data mapping error. Cause . . . . . : Error type 1 has occurred. Error types and their meanings are: 1  Arithmetic overflow.
I've tried these two ways and get the same error.
SELECT F4211.SDDOCO, F4211.SDDCTO, F4211.SDLNID, F4211.SDLITM, DECIMAL(sdsoqs/100,7,2), decimal(sduprc/10000,8,2), (sdsoqs/100)*(sduprc/10000), F4211.SDTRDJ, F4211.SDLTTR, F4211.SDNXTR, F4211.SDUPMJ, F4211.SDTDAY
FROM STCJDE.F4211 F4211
WHERE (SDDOCO=432645) OR (SDDOCO=425397)
ORDER BY SDDOCO, SDLNID
;
SELECT F4211.SDDOCO, F4211.SDDCTO, F4211.SDLNID, F4211.SDLITM, DECIMAL(sdsoqs/100,7,2), decimal(sduprc/10000,8,2), decimal((sdsoqs/100)*(sduprc/10000),9,2), F4211.SDTRDJ, F4211.SDLTTR, F4211.SDNXTR, F4211.SDUPMJ, F4211.SDTDAY
FROM STCJDE.F4211 F4211
WHERE (SDDOCO=432645) OR (SDDOCO=425397)
ORDER BY SDDOCO, SDLNID
;
If I try to name the calculated QtyShp and UnitPrice they are not recognized as column names.
SELECT F4211.SDDOCO, F4211.SDDCTO, F4211.SDLNID, F4211.SDLITM, DECIMAL(sdsoqs/100,7,2) ShpQty, decimal(sduprc/10000,8,2) UnitPrice, decimal(ShpQty*UnitPrice,9,2), F4211.SDTRDJ, F4211.SDLTTR, F4211.SDNXTR, F4211.SDUPMJ, F4211.SDTDAY
FROM STCJDE.F4211 F4211
WHERE (SDDOCO=432645) OR (SDDOCO=425397)
ORDER BY SDDOCO, SDLNID
;
QL State: 42703
Vendor Code: 206
Message: SQL0206 Column SHPQTY not in specified tables.
I have to missing something simple in the documentation.
Thank you for any help.
Topic

Re: creating calculation column that has a calculated value inside overflows
20120816T20:02:34ZThis is the accepted answer. This is the accepted answer.I thought I had tried this earlier. Must of had a syntax error I couldn't spot. This worked. Using the decimal casting for each part of the calculation.
SELECT F4211.SDDOCO, F4211.SDDCTO, F4211.SDLNID, F4211.SDLITM, DECIMAL(sdsoqs/100,7,2), decimal(sduprc/10000,8,2), decimal(decimal(sdsoqs/100,7,2)*decimal(sduprc/10000,8,2),9,2), F4211.SDTRDJ, F4211.SDLTTR, F4211.SDNXTR, F4211.SDUPMJ, F4211.SDTDAY
FROM STCJDE.F4211 F4211
WHERE (SDDOCO=432645) OR (SDDOCO=425397)
ORDER BY SDDOCO, SDLNID
;
Hopefully this will help someone else.