Topic
1 reply Latest Post - ‏2012-08-16T20:02:34Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic creating calculation column that has a calculated value inside overflows

‏2012-08-16T19:57:27Z |
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.
Updated on 2012-08-16T20:02:34Z at 2012-08-16T20:02:34Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3129 Posts
    ACCEPTED ANSWER

    Re: creating calculation column that has a calculated value inside overflows

    ‏2012-08-16T20:02:34Z  in response to SystemAdmin
    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.