views:

732

answers:

1

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)
+1  A: 

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.

Jan B. Kjeldsen