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

I struggled with this for more than 2 days as it was not as straightforward as I thought. Because we cannot use "having" in a simple AX SQL statement, I had to rely on AOT Query objects to get this. I hope my solutions is not very performance intensive but I dont know enough about AOT Query objects to confirm. It does look like it is a lot better than doing a while select and using INVENTSUM class to calculate so I went with this.

Here is the solution

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 to find physical Stock for that group of wmsLocations. Here is an image trying to explain the query design

The code in the physical stock is copied below.

public static server str physicalStock()

 {

 //  Description : taken from InventSum.physicalInventCalculated

  return SysComputedColumn::add(SysComputedColumn::add(

           SysComputedColumn::add(SysComputedColumn::sum(SysComputedColumn::returnField(tableStr(ABCLocationsInventDimQty), identifierStr(InventSum), fieldStr(InventSum, PostedQty))),

                 SysComputedColumn::sum(SysComputedColumn::returnField(tableStr(ABCLocationsInventDimQty), identifierStr(InventSum), fieldStr(InventSum, Received)))),

           SysComputedColumn::add(SysComputedColumn::negative(SysComputedColumn::sum(SysComputedColumn::returnField(tableStr(ABCLocationsInventDimQty), identifierStr(InventSum), fieldStr(InventSum, Deducted)))),

                 SysComputedColumn::sum(SysComputedColumn::returnField(tableStr(ABCLocationsInventDimQty), identifierStr(InventSum), fieldStr(InventSum, Registered))))),

           SysComputedColumn::negative(SysComputedColumn::sum(SysComputedColumn::returnField(tableStr(ABCLocationsInventDimQty), identifierStr(InventSum), fieldStr(InventSum, Picked)))));


  }
Then, I used this view and added it to the query where I use the having clause to take the sum into account. The value in the properties is said to be '<=0'. Which sums up the total quantity for a particular inventdim and filters out anything that is greater than 0. This can also be tested by putting in a view and looking and wmslocationID and calculatedPhysStock.

This view gives all the locations where the physical stock in zero but does not give any location where an inventory dimension might not have been generated yet. So, it will miss any new location created or if for some reason all related dimensions of a particular location was deleted, it will miss those too.

To add that, I created another query where I do WMSLocationTable NOTEXISTS InventDIM. By the current table design, this query will always return all locations where there is no physical stock as you cannot have a physical stock without inventDIM record.
Combining both queries as a union gives all the locations that do not have a stock.

I think this is an overkill for something that should be simpler so in case someone has a shorter solution, please let me know in the comments.