axapta - 嵌套 notExists 加入 X++ (Dynamics AX3.0)

Question

当以下代码执行时:

   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;

生成的sql 将第二个notExists join (salesTable) 嵌套到第一个notExists join (custTable) 的where 子句中。 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))

有没有什么办法可以将X++ select查询写成notExists将salesTable连接到inventTrans表而不是custTable，所以生成的SQL类似于下面这样？

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)

Answer 1

简短的回答:不!

在您的示例中，您可以使用内部联接重新制定:

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

如果您在开票时不删除销售订单，它应该可以工作。