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
This topic has been locked.
1 reply
Latest Post
 20120816T20:02:34Z by SystemAdmin
ACCEPTED ANSWER
Pinned topic creating calculation column that has a calculated value inside overflows
20120816T19:57:27Z

Answered question
This question has been answered.
Unanswered question
This question has not been answered yet.
Updated on 20120816T20:02:34Z at 20120816T20:02:34Z by SystemAdmin

ACCEPTED ANSWER
Re: creating calculation column that has a calculated value inside overflows
20120816T20:02:34Z in response to SystemAdminI 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.