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