loop select sum()

(0) Share

Report

Posted on by Community Member

Hi all, I need to do a SELECT using a sum function and also get one more field in the SELECT, but I have some problem that prevents the second field from being displayed in the result. Here is an example:

while select sum(AvailPhysical), ItemId
        from inventSum
        where (!inventSum.Closed) && (!inventSum.ClosedQty) &&
              (inventSum.AvailPhysical != 0)

        join sumInventDim
        group by InventLocationId, wMSLocationId, InventBatchId, InventSerialId
        where (sumInventDim.InventDimId == inventSum.InventDimId) &&
              (sumInventDim.InventLocationId == '004') &&
              (sumInventDim.wMSLocationId == '5-A-01/1')

        exists join wmsLocation
        where (wmsLocation.InventLocationId == sumInventDim.InventLocationId) &&
              (wmsLocation.wMSLocationId == sumInventDim.wMSLocationId) &&
              ((wmsLocation.locationType == WMSLocationType::Buffer) ||
              (wmsLocation.locationType == WMSLocationType::Pick))
    {
        info(strfmt("Item: %1 - Qtd: %2",inventSum.ItemId, inventSum.AvailPhysical));
    }

2654.log.PNG

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report
You are missing a group by itemId, since you have to group with aggregate functions.

static void Job123(Args _args)
{
    InventSum           inventSum;
    InventDim           sumInventDim;
    WMSLocation         wmsLocation;

    while select sum(AvailPhysical), ItemId
        from inventSum
            group by ItemId
            where inventSum.ItemId         == 'myitem123'
                && inventSum.Closed         == NoYes::No
                && inventSum.ClosedQty      == NoYes::No
                && inventSum.AvailPhysical != 0
        join sumInventDim
            group by InventLocationId, wMSLocationId, InventBatchId, InventSerialId
            where sumInventDim.InventDimId == inventSum.InventDimId
                //&& (sumInventDim.InventLocationId == '004')
                //&& (sumInventDim.wMSLocationId == '5-A-01/1')
        exists join wmsLocation
            where wmsLocation.InventLocationId == sumInventDim.InventLocationId
                && wmsLocation.wMSLocationId    == sumInventDim.wMSLocationId
                && (wmsLocation.locationType    == WMSLocationType::Buffer
                    || wmsLocation.locationType == WMSLocationType::Pick)
    {
        info(strfmt("Item: %1 - Qty: %2",inventSum.ItemId, inventSum.AvailPhysical));
    }
}

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Error: The InventLocationId column has been specified more than one time in the ORDER BY list. The columns in the ORDER BY list must be unique.

Was this reply helpful? Yes No
Suggested answer

Vilmos Kintera 46,149 on at

Like (0)

Report

See the code above, works for me fine. I have added ItemId filter so it does not loop through all items.

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

Thanks to everyone, I really needed to add the ItemId, but after that I needed to put the names of the tables before each field name in the groupby clause.

Problem solved, thank you!

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

Please mark all helpful answers as verified, especially that I took the time to fix and format your code, thanks :)

Was this reply helpful? Yes No

Community site session details

loop select sum()

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

loop select sum()

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates