Skip to main content
Post a question

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id : QN97yUBeYxpt1eQAEpWRQm
Dynamics 365 Community / Forums / Supply chain | Supply Chain Management, Commerce / while select sum group...
Supply chain | Supply Chain Management, Commerce
Suggested answer

while select sum group by with multiple tables

Like (1) ShareShare
ReportReport
Posted on 15 Mar 2025 03:32:11 by akshi 89
Hello experts,
I would like to convert x++ query the following SQL query.
 
SELECT 
ReqTrans.ITEMID,ReqTrans.RefType, ReqTrans.PlanVersion, SUM(ReqTrans.QTY) AS total_qty,ReqTrans.ReqDate,ReqTrans.CovInventDimId
FROM ReqPlan
    JOIN ReqPlanVersion 
    ON ReqPlanVersion.ReqPlanId = ReqPlan.ReqPlanId 
    AND ReqPlanVersion.REQPLANDATAAREAID = ReqPlan.DATAAREAID
    JOIN ReqTrans ON ReqTrans.PlanVersion = ReqPlanVersion.RecId
    JOIN reqTransCov on reqTransCov.ReceiptRecId = ReqTrans.RecId and ReqTrans.Direction = 1
    JOIN ReqTrans AS reqTransSettled on reqTransSettled.RecId = reqTransCov.IssueRecId
    where ReqTrans.reftype = 34
    and (reqTransSettled.RefTYpe = 12 OR reqTransSettled.RefTYpe = 45)
    and ReqPlan.ReqPlanId = 'MRP_TF14'
    GROUP BY ReqTrans.ITEMID,ReqTrans.RefType, ReqTrans.PlanVersion,ReqTrans.ReqDate,ReqTrans.CovInventDimId
 
I write the following. But error occurred. Any advice would be appreciated.
while select reqTrans.ItemId,reqTrans.RefType,
            reqTrans.PlanVersion,
            SUM(reqTrans.QTY) AS total_qty,reqTrans.ReqDate,reqTrans.CovInventDimId
    from reqPlan 
    where reqPlan.ReqPlanId == MRPId
    join reqPlanVersion
        where reqPlanVersion.ReqPlanId == reqPlan.ReqPlanId
            && reqPlanVersion.reqPlanDataAreaId == reqTrans.dataAreaId
    join reqTrans                 
        GROUP BY reqTrans.ItemId,reqTrans.RefType, reqTrans.PlanVersion,reqTrans.ReqDate,reqTrans.CovInventDimId
    where reqTrans.PlanVersion == reqPlanVersion.RecId
            && reqTrans.reftype == ReqRefType::TransferPlannedOrder
    join reqTransCov 
        where reqTransCov.ReceiptRecId == reqTrans.RecId 
            && reqTrans.Direction == InventDirection::Receipt
    join reqTransSettled 
        where reqTransSettled.RecId == reqTransCov.IssueRecId
            && (reqTransSettled.RefTYpe == ReqRefType::ProdLine || reqTransSettled.RefTYpe == ReqRefType::PmfFormulaLine)
 

 
Categories:
Dynamics 365 Supply Chain Management
  • akshi Profile Picture
    akshi 89 on 15 Mar 2025 at 09:55:29
    while select sum group by with multiple tables
    I decided to split the query, and then I was able to get the total Qty.
    select ReqPlanId from reqPlan
    where reqPlan.ReqPlanId == MRPId
join reqPlanVersion
    where reqPlanVersion.ReqPlanId      == reqPlan.ReqPlanId
    && reqPlanVersion.reqPlanDataAreaId == reqTrans.dataAreaId;

RecId planVersionRecId = reqPlanVersion.RecId;

while select ItemId,RefType,PlanVersion,ReqDate,CovInventDimId,sum(Qty)
    from reqTrans
    GROUP BY reqTrans.ItemId,reqTrans.RefType, reqTrans.PlanVersion,reqTrans.ReqDate,reqTrans.CovInventDimId
    where reqTrans.PlanVersion == planVersionRecId
    && reqTrans.reftype == ReqRefType::TransferPlannedOrder
    join reqTransCov
    where reqTransCov.ReceiptRecId == reqTrans.RecId
    && reqTrans.Direction == InventDirection::Receipt
    join reqTransSettled
    where reqTransSettled.RecId == reqTransCov.IssueRecId
    && (reqTransSettled.RefTYpe == ReqRefType::ProdLine || reqTransSettled.RefTYpe == ReqRefType::PmfFormulaLine)
     
  • akshi Profile Picture
    akshi 89 on 15 Mar 2025 at 08:08:16
    while select sum group by with multiple tables
    while select reqPlanId
from reqPlan
where reqPlan.ReqPlanId == MRPId
join reqPlanVersion
    where reqPlanVersion.ReqPlanId == reqPlan.ReqPlanId
        && reqPlanVersion.reqPlanDataAreaId == reqTrans.dataAreaId        
    join reqTrans
    GROUP BY reqTrans.ItemId,reqTrans.RefType, reqTrans.PlanVersion,reqTrans.ReqDate,reqTrans.CovInventDimId
where reqTrans.PlanVersion == reqPlanVersion.RecId
        && reqTrans.reftype == ReqRefType::TransferPlannedOrder
join reqTransCov
    where reqTransCov.ReceiptRecId == reqTrans.RecId
        && reqTrans.Direction == InventDirection::Receipt
join reqTransSettled
    where reqTransSettled.RecId == reqTransCov.IssueRecId
        && (reqTransSettled.RefTYpe == ReqRefType::ProdLine || reqTransSettled.RefTYpe == ReqRefType::PmfFormulaLine)
    The above compiles fine. However, I can't get the sum of reaTran.qty. I need to group by reqTran's ItemId, RefType, PlanVersion, sum(Qty), ReqDate, and CovInventDimId to get the sum of Qty.
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 231,760 Most Valuable Professional on 15 Mar 2025 at 07:24:23
    while select sum group by with multiple tables
    Don't include the table buffer name in the field list. Instead, use field list for each table buffer with its respective fields.
     
    You're currently trying to select reqTrans.ItemId when selecting reqPlan:
    select reqTrans.ItemId from reqPlan
    but the correct approach is selecting reqTrans fields when joining reqTrans:
    join ItemId from reqTrans
  • akshi Profile Picture
    akshi 89 on 15 Mar 2025 at 06:30:01
    while select sum group by with multiple tables
    Hello Martin,
    Thanks for your advise.
    The error is 'join' expected. that compiled error.
    I removed AS total_qty. But still same error.
     while select reqTrans.ItemId,reqTrans.RefType,
     reqTrans.PlanVersion,
     sum(reqTrans.Qty),reqTrans.ReqDate,reqTrans.CovInventDimId
 from reqPlan
 where reqPlan.ReqPlanId == MRPId
 join reqPlanVersion
     where reqPlanVersion.ReqPlanId == reqPlan.ReqPlanId
         && reqPlanVersion.reqPlanDataAreaId == reqTrans.dataAreaId
 join reqTrans
     GROUP BY reqTrans.ItemId,reqTrans.RefType, reqTrans.PlanVersion,reqTrans.ReqDate,reqTrans.CovInventDimId
 where reqTrans.PlanVersion == reqPlanVersion.RecId
         && reqTrans.reftype == ReqRefType::TransferPlannedOrder
 join reqTransCov
     where reqTransCov.ReceiptRecId == reqTrans.RecId
         && reqTrans.Direction == InventDirection::Receipt
 join reqTransSettled
     where reqTransSettled.RecId == reqTransCov.IssueRecId
         && (reqTransSettled.RefTYpe == ReqRefType::ProdLine || reqTransSettled.RefTYpe == ReqRefType::PmfFormulaLine)
     
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 231,760 Most Valuable Professional on 15 Mar 2025 at 05:12:33
    while select sum group by with multiple tables
    Ah, I see a bug: AS total_qty isn't valid syntax in X++, therefore you must be getting a compilation error complaining about invalid syntax at that place.
     
    Remove this statement; you'll find the result of the aggregation in the Qty field.
  • Martin Dráb Profile Picture
    Martin Dráb 231,760 Most Valuable Professional on 15 Mar 2025 at 05:10:15
    while select sum group by with multiple tables
    Please, never say just "error occurred". It's important to know what error you got, not just that you got an error. Tell us whether it's a compilation error or a runtime error, what the error message says and where is occurred.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,884 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,760 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans