Dynamics AX Hints: How joins in X++ select statement are translated into T-SQL

23 May 2015

How joins in X++ select statement are translated into T-SQL

It is not always obvious what request is actually executed on SQL Server. The most confusing is probably exists join in X++. Let's analyze how joins in select statement in X++ are translated into T-SQL statement sent to SQL Server.

1. join in X++:

select AccountNum from custTable
    join TaxGroupId from custGroup
    where custGroup.CustGroup == custTable.CustGroup;

CROSS JOIN in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID, T2.TAXGROUPID, T2.RECID
FROM CUSTTABLE T1 CROSS JOIN CUSTGROUP T2
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
  AND (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) 
   AND (T2.CUSTGROUP=T1.CUSTGROUP))

2. outer join in X++:

select AccountNum from custTable
    outer join AccountID from custBankAccount
    where custBankAccount.CustAccount == custTable.AccountNum;

LEFT OUTER JOIN in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID, T2.ACCOUNTID, T2.RECID
FROM CUSTTABLE T1 LEFT OUTER JOIN CUSTBANKACCOUNT T2
ON (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
AND (T1.ACCOUNTNUM=T2.CUSTACCOUNT))
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))

3. exists join in X++:

select AccountNum from custBankAccount
    exists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum;

EXISTS (SELECT 'x'...) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
AND EXISTS (SELECT 'x'
            FROM CUSTTABLE T2
            WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
              AND  (T1.CUSTACCOUNT=T2.ACCOUNTNUM)))

4. notexists join in X++:

select AccountNum from custBankAccount
    notexists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum;

NOT (EXISTS (SELECT 'x'...)) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
AND NOT (EXISTS (SELECT 'x'
                 FROM CUSTTABLE T2
                 WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?))
                   AND  (T1.CUSTACCOUNT=T2.ACCOUNTNUM))))

5. join after exists join in X++:

select AccountNum from custBankAccount
    exists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum
    join TaxGroupId from custGroup
    where custGroup.CustGroup == custTable.CustGroup;

EXISTS (SELECT 'x'...CROSS JOIN...) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))
AND EXISTS (SELECT 'x'
            FROM CUSTTABLE T2 CROSS JOIN CUSTGROUP T3
            WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) 
               AND (T1.CUSTACCOUNT=T2.ACCOUNTNUM)) 
              AND (((T3.PARTITION=?) AND (T3.DATAAREAID=?)) 
               AND (T3.CUSTGROUP=T2.CUSTGROUP)))

6. exists join after exists join in X++:

select AccountNum from custBankAccount
    exists join custTable
    where custBankAccount.CustAccount == custTable.AccountNum
    exists join custGroup
    where custGroup.CustGroup  == custTable.CustGroup
       && custGroup.TaxGroupId == "Std";

EXISTS (SELECT 'x'... EXISTS (SELECT 'x'...)) in T-SQL:

SELECT T1.ACCOUNTNUM, T1.RECID
FROM CUSTBANKACCOUNT T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?))

AND EXISTS (SELECT 'x' FROM CUSTTABLE T2 
            WHERE (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) 
               AND (T1.CUSTACCOUNT=T2.ACCOUNTNUM)) 
               AND EXISTS (SELECT 'x' 
                           FROM CUSTGROUP T3 
                           WHERE (((T3.PARTITION=?) AND (T3.DATAAREAID=?)) 
                              AND ((T3.CUSTGROUP=T2.CUSTGROUP) AND (T3.TAXGROUPID=?)))))

Conclusion
each exists join is considered as a separate statement, but any join after exists join is coupled or nested.

5 comments:

Jerico5/2/16 22:28
For exists join, if it returns false, it should not affect the result of my main query right? Then why does it wipe out the result of my main query when it returns false?
ReplyDelete
Replies
Mustafa12/2/18 13:02
I would like to convert this into SQL, finding difficult as it contains mix of exist and not exists

update_recordset vendAccruedPurchasesTmp setting
ReceivedQuantity = vendPackingSlipTransHistoryView.ReceivedQty,
Qty = vendPackingSlipTransHistoryView.Qty,
CostAmountPhysical = vendPackingSlipTransHistoryView.CostAmountPhysical,
ValueMST = vendPackingSlipTransHistoryView.ValueMST
join vendPackingSlipTransHistoryView
where vendPackingSlipTransHistoryView.SourceDocumentLine == vendAccruedPurchasesTmp.PackingSlipSourceDocumentLine &&
vendPackingSlipTransHistoryView.AccountingDate <= cutOffDate
exists join vendPackingSlipVersionCorrection
where vendPackingSlipVersionCorrection.VendPackingSlipJour == vendPackingSlipTransHistoryView.VendPackingSlipJour &&
vendPackingSlipVersionCorrection.AccountingDate > cutOffDate;

delete_from vendAccruedPurchasesTmp
where vendAccruedPurchasesTmp.DatePhysical > cutOffDate
exists join qtyMatched where
qtyMatched.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp.PackingSlipSourceDocumentLine
join vendInvoiceTrans where
vendInvoiceTrans.SourceDocumentLine == qtyMatched.InvoiceSourceDocumentLIne
&& vendInvoiceTrans.InvoiceDate > cutOffDate
notexists join qtyMatchedNotExists where
qtyMatchedNotExists.PackingSlipSourceDocumentLine == vendAccruedPurchasesTmp.PackingSlipSourceDocumentLine
join SourceDocumentLine, InvoiceDate from vendInvoiceTransNotExists where
vendInvoiceTransNotExists.SourceDocumentLine == qtyMatchedNotExists.InvoiceSourceDocumentLIne
&& vendInvoiceTransNotExists.InvoiceDate <= cutOffDate;
ReplyDelete
Replies

Add comment