web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

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

while select sum group by with multiple tables

(3) ShareShare
ReportReport
Posted on by akshi 152
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
I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,948 Most Valuable Professional on at
    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.
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,948 Most Valuable Professional on at
    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.
  • akshi Profile Picture
    akshi 152 on at
    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 237,948 Most Valuable Professional on at
    Don't include the table buffer name in the field list.
     
    Instead of
    select reqTrans.ItemId from reqPlan
    do this:
    select ItemId from reqPlan
  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,948 Most Valuable Professional on at
    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 152 on at 
    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.
  • Verified answer
    akshi Profile Picture
    akshi 152 on at
    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)
     

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Supply chain | Supply Chain Management, Commerce

#1
Laurens vd Tang Profile Picture

Laurens vd Tang 298 Super User 2025 Season 2

#2
Siv Sagar Profile Picture

Siv Sagar 183 Super User 2025 Season 2

#3
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 118 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans