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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s