嵌套notExists加入X ++(Dynamics AX3.0) [英] Nested notExists joins X++ (Dynamics AX3.0)

问题描述

执行以下代码时:

   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联接(salesTable)嵌套到第一个notExists联接(custTable)的where子句中.即

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

是否有任何方法可以将X ++ select查询写为notExists，将salesTable而不是custTable联接到salesTable中，因此生成的SQL类似于以下内容?

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)

推荐答案

您可能不再等待答案，但是在Ax 2012上，我仍然遇到相同的问题.

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

找到了另一种解决方案(除了使用SQL语句之外)，虽然不是性能最高的解决方案，但是它可以起作用:

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

• 创建查询:从不存在的inventTrans中选择*加入CustTable....
• 基于该查询创建视图
• 对salesTable做相同的操作

• 按如下所示更改您的选择语句:

• 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:

选择inventTrans

select inventTrans

...

存在加入myCustView

exists join myCustView

其中myCustView.InventTransRecId == inventTrans.RecId

where myCustView.InventTransRecId == inventTrans.RecId

存在加入mySalesView

exists join mySalesView

哪里...

希望这会有所帮助，

T

这篇关于嵌套notExists加入X ++(Dynamics AX3.0)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！