A SQL query to find all wmsLocations that have zero stock in AX 2012 R3

Question

I have been trying this for a day now, and I can't seem to find the correct way to do this. I need a SQL query that returns me all locations with zero physical stock ( inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked <= 0) I think it should be as simple as below but this is returning me locations where ( inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked > 0). Can someone please help me figure out what is wrong here?

select
wmslocationid
from wmsLocation
order by wmsLocation.wMSLocationId
where
(wmsLocation.inventLocationId == inventLocationId) //default warehouse
exists join inventDim
where (inventDim.InventSiteId == inventSiteId) &&//default site
(inventDim.InventLocationId == inventLocationId) &&
(inventDim.WMSLocationId == wmsLocation.wMSLocationId)
exists join inventSum
where (inventDim.InventDimId == inventSum.InventDimId) &&
(inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked <= 0);

Have you tried putting `generateOnly` in the AX statement then doing `info(wmsLocation.getSQLStatement())`? That'll tell you the `TSQL` query and you can drop it in SSMS to figure out what's wrong. I don't have an AX environment in front of me at the moment or I'd play around with it. — Alex Kwitny, Mar 14 '18 at 03:21
Also add `forceLiterals`. So the first part of your X++ statement would be `select generateonly forceLiterals wmslocationid from wmsLocation` and then at the very end add `info(wmsLocation.getSQLStatement());` then you should be able to figure out what's going on in SQL — Alex Kwitny, Mar 14 '18 at 16:41

score 2 · Answer 1 · answered Mar 14 '18 at 09:20

Some items can have zero physical stock while other items can have positive physical stock in the same location, have you considered that? Do you also need to filter by items? If you need to find locations where all items have zero stock then try using notexists instead of exists.

I have nowhere to test it at the moment, but you can try replacing

exists join inventSum
where (inventDim.InventDimId == inventSum.InventDimId) &&
(inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked <= 0);

from your statement with

notexists join inventSum
where (inventDim.InventDimId == inventSum.InventDimId) &&
(inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked > 0);

Also, you could try

select wmslocationid from inventDim
    group by wmslocationid
    where inventDim.InventSiteId        == inventSiteId         //default site
       && inventDim.InventLocationId    == inventLocationId     //default warehouse
notexists join inventSum   
    where inventSum.InventDimId == inventDim.InventDimId
       && inventSum.Closed      == NoYes::No
       && inventSum.PostedQty + inventSum.Received - inventSum.Deducted + inventSum.Registered - inventSum.Picked > 0;

user2184523 · Accepted Answer · 2018-06-07T15:51:13.580

I tried it but it still gave me all the records. When I try generateonly and forceliteral, it looked like the sql query is correct but the result data was still giving me unrelated values. I ended up fixing it by creating an AOT Query object. The solution is very long so I have added it to blog https://locus90.blogspot.co.uk/2018/05/a-sql-query-to-find-all-wmslocations.html.

In a nutshell I ended up fixing it by creating an AOT Query object With wmsLocations where I am grouping by location ID, inner join with InventDim but InventDim has an outer Join with InventSum and having a view on the query where syscomputed column on it finds the physical Stock for that group of wmsLocations which we can use as a range in another query giving us all locations. This only works with inner join, so another non exist join was required for every remaining location without inventdim.

Thanks again for all your help, and let me know if you can recommend a simple solution for this.

A SQL query to find all wmsLocations that have zero stock in AX 2012 R3

2 Answers2