How to Join InventSum and InventDim with group by InventLocationID ??


I am trying to join two tables InventSum and InventDim with InventDimId and groupBy InvetSum.DataAreaId,InventDim.InventLocationID …

Similar to below SQL :
select InventLocationId,SUM(physicalinvent),sum(availphysical),sum(ordered),dataareaid from inventsum inner join InventDIm
group by inventLocationID,DATAAREAID

Thanks in advance … How can we achieve this in X++ ?

Thanks in advance


Hello Shankar,

Use while Select for perform this like,

While select InventSum Join InventDim where inventSum.InventDimId == InventDim.InventDimId

&& inventSum.inventLocationID*==* InventDim.inventLocationID

Group By inventLocationID


write logic here for


You obvoiusly does not need a DataAreaId unless you are using a cross company query.

while select sum(PhysicalInvent) from inventSum
join inventDim group by InventLocationId
where inventSum.InventDimId == inventDim.inventDimId //// you may need an ItemId predicate along as the stock is viewed per item
print inventSum.PhysicalInvent;