Tutorial :Nested notExists joins X++ (Dynamics AX3.0)



Question:

When the following code executes:

   select sum(qty) from inventTrans          index hint TransTypeIdx          where inventTrans.ItemId        == itemId             && inventTrans.TransType     == InventTransType::Sales             && inventTrans.InventDimId   == inventDimId          notExists join custTable          where custTable.AccountNum      == inventTrans.CustVendAC             && custTable.CustGroup       == custGroupId          notExists join salesTable          where salesTable.SalesId        == inventTrans.TransRefId             && salesTable.Extraordinary  == NoYes::Yes;  

The sql generated nests the second notExists join (salesTable) into the where clause of the first notExists join (custTable). ie

SELECT SUM(A.QTY)  FROM      INVENTTRANS A  WHERE      A.DATAAREAID  = 'MyCompany'  AND A.ITEMID      = 'MyItem'  AND A.TRANSTYPE   = 0  AND A.INVENTDIMID = 'SomeValue'  AND NOT EXISTS (SELECT 'x'                  FROM CUSTTABLE C                  WHERE C.DATAAREAID  ='MyCompany'                  AND C.ACCOUNTNUM    =A.CUSTVENDAC                  AND C.CUSTGROUP     ='SomeCustGroup'                  AND NOT EXISTS (SELECT 'x'                                  FROM SALESTABLE B                                  WHERE                                      B.DATAAREAID    ='MyCompany'                                  AND B.SALESID       =A.TRANSREFID                                  AND B.EXTRAORDINARY =1))  

Is there any way to write the X++ select query to notExists join the salesTable to the inventTrans table instead of the custTable, so the SQL generated would be similar to the following?

SELECT SUM(A.QTY)  FROM      INVENTTRANS A  WHERE A.DATAAREAID     = 'MyCompany'  AND A.ITEMID           = 'MyItem'  AND A.TRANSTYPE        = 0  AND A.INVENTDIMID      = 'SomeValue'  AND NOT EXISTS (SELECT 'x'                  FROM CUSTTABLE C                  WHERE C.DATAAREAID  ='MyCompany'                  AND C.ACCOUNTNUM    =A.CUSTVENDAC                  AND C.CUSTGROUP     ='SomeCustGroup')  AND NOT EXISTS (SELECT 'x'                  FROM SALESTABLE B                  WHERE                      B.DATAAREAID='MyCompany'                  AND B.SALESID=A.TRANSREFID                  AND B.EXTRAORDINARY=1)  


Solution:1

The short answer: no!

In your example, you could reformulate using inner join:

select sum(qty) from inventTrans      index hint TransTypeIdx      where inventTrans.ItemId        == itemId         && inventTrans.TransType     == InventTransType::Sales         && inventTrans.InventDimId   == inventDimId      join salesTable      where salesTable.SalesId        == inventTrans.TransRefId         && salesTable.Extraordinary  == NoYes::No;      notExists join custTable      where custTable.AccountNum      == inventTrans.CustVendAC         && custTable.CustGroup       == custGroupId  

It should work provided you do not delete sales orders when invoicing.


Solution:2

You're probably not waiting for an answer anymore, but on Ax 2012 I still experienced the same problem.

Found another solution (aside from using a SQL Statement), not the most performant one, but it works:

  • Create a query: select * from inventTrans notexists join CustTable ....
  • Create a view based on that query
  • do the same for the salesTable
  • change your select statement as follows:

    select inventTrans

    ...

    exists join myCustView

    where myCustView.InventTransRecId == inventTrans.RecId

    exists join mySalesView

    where ...

Hope this helps,

T


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »