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

Finance and Operations/AX User Forum
1

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);
2

It more sounds like an X++ select (Not a direct SQL query). You may need to use the not exists join (see the example below)

while select wmslocationid from wmsLocation
where wmsLocation.inventLocationId == “22” //default warehouse
notexists join inventSum
where inventSum.AvailPhysical > 0
exists join inventDim
where inventDim.WMSLocationId == wmsLocation.wMSLocationId
&& inventDim.InventLocationId == wmsLocation.inventLocationId
&& inventDim.inventDimId == inventSum.InventDimId
{
info(wmsLocation.wMSLocationId);
}

3

For reference, I replied to the duplicate thread in another forum.