Icon View Incident Report

Serious Serious
Reported By: Alan Questell
Reported On: 10/1/2001
For: Version 3.01 Build 1
# 867 Using SQL SUM Aggregate Function on 32-bit Integers Does Not Work Properly

Run the following SQL statement and you will see a problem with some of the totals. After playing around, I found that if you divided by 1 within the SUM function, the expected answer was calculated.

SELECT      Model,
                    COUNT(WhSkids.Model) AS RecordCount,
                    SUM(CurrentQty) AS CurrentQtyTotal,
                    SUM(CurrentQty/CasePack) AS CurrentQtyCartonsTotal,
                    SUM(ReservedQty) AS ReservedQtyTotal,
                    SUM(ReservedQty/CasePack) AS ReservedQtyCartonsTotal,
                    SUM(CurrentQty-ReservedQty) AS AvailableQtyTotal,
                    SUM((CurrentQty/CasePack)-(ReservedQty/CasePack)) AS 
FROM          WhSkids
JOIN            WhActiveInventoryTest
ON               WhSkids.Model = WhActiveInventoryTest.Model
WHERE       WhSkids.Model = 'L12/925/3099006'
GROUP BY  WhSkids.Model

Comments Comments
There was a problem with the way that the column for the sum was being defined in the result set. It was being defined as a 32-bit integer when it should have been defined as a 64-bit integer.

Resolution Resolution
Fixed Problem on 10/1/2001 in version 3.02 build 1