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)