sum and grup

Dynamics 365 for Finance and Operations/AX Developer Forum
1

Hi all, does not work the query written with the new syntax of AX 2012 and I do not know how to rewrite it: below is written in SQL and X ++ you tell me where am I wrong?
Thank you for your help
Mak5s

example SQL

select InventTable.BFItemLineId,
InventSum.ItemId,
InventDim.InventSizeId,
InventDim.InventSiteId,
InventDim.InventLocationId,
sum(PostedQty + Received- Deducted + Registered - Picked) as stockQty
sum(PostedValue),
sum(PostedQty),
sum(PhysicalValue),
sum(Received),
sum(Deducted)
from InventTable
inner
join InventSum
on InventSum.ItemId = InventTable.ItemId
inner
join InventDim
on InventDim.InventDimId = InventSum.InventDimId
group by InventTable.BFItemLineId,InventSum.ItemId, InventDim.InventSizeId, InventDim.InventSiteId, InventDim.InventLocationId
having sum(InventSum.PostedQty + InventSum.Received - InventSum.Deducted) <> 0

example X++

select InventSum.ItemId,

InventDim.InventSizeId,
InventDim.InventSiteId,
InventDim.InventLocationId,
sum(locInventSum.PostedValue),
sum(locInventSum.PostedQty),
sum(locInventSum.PhysicalValue),
sum(locInventSum.Received),
sum(locInventSum.Deducted)
from locInventSum
where locInventSum.ItemId == locInventTable.ItemId
join locInventDim
where locInventDim.InventDimId == locInventSum.InventDimId
group by locInventTable.BFItemLineId, locInventSum.ItemId, locInventDim.InventSizeId, locInventDim.InventSiteId, locInventDim.InventLocationId

2

Of course it “does not work”, because your X++ is not syntactically correct and don’t even compile. Unless you write code that can be executed, it makes no sense to discuss its behavior. Select Statement Syntax is the part of documentation you need in this moment.

Also, you forgot to include InventTable in your X++ query (you probably overlooked it because the code is too messy).

You probably tried to write something like this:

select locInventTable
    group by BFItemLineId
    
    join ItemId, sum(PostedValue), sum(PostedQty), sum(PhysicalValue), sum(Received), sum(Deducted)            
        from locInventSum
        group by ItemId 
        where locInventSum.ItemId == locInventTable.ItemId
        
    join InventSizeId, InventSiteId, InventLocationId from locInventDim
        group by InventSizeId, InventSiteId, InventLocationId
        where locInventDim.InventDimId == locInventSum.InventDimId;
3

Martin Dráb, Thank you for attention

See you soon.

Mak5s