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

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.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google photo

Stai commentando usando il tuo account Google. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...