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=?)))))

Finding out which tables were updated by an operation in AX

★★★★ 

• 0
• 0

At times when troubleshooting Microsoft Dynamics AX you need to know which tables were updated by a particular operation, for example after performing a posting. This could be if you want to be sure that all the right tables are updated correctly after an upgrade, if you have unexpected results on one customer/vendor etc or if you have made an update the system and you want to be sure that everything is ok.

It is possible to use the SQL tracer for this, but for functional people it can be at times be a bit challenging to interpret these results, so here is a tip for how you can easily find out which tables were updated when performing an operation in AX. In this example we will show the effect on tables from a Sales Invoice posting in Microsoft Dynamics AX 2012 as this is quite a complex process that affects many tables.

The whole technique here relies on using a standard report ‘Size of company accounts’ in a novel way. This report returns an output of all the tables in AX that have data in them. In this article we will see how we can use this report to print a snapshot of the tables before and after an operation so that when we print it ‘after’ we will see which tables got data populated into them from an AX operation.

The steps are as follows

1.  Go to the AOT and locate the Class ‘SysCompanySizeDP’ and open up the method called ‘insertIntoTempTable’. Change the highlighted function from ‘tableid2Pname’ to instead ‘tableid2name’. The reason we are changing the function is that it is easier to locate the tables in the AOT when the report prints the tables using the technical names. If we didn’t do this, the report would for example print the name of a table like ‘Customer Invoice Journal’ instead of ‘CustInvoiceJour’

2. Now go to System Administration\Reports\Database\Size of company accounts and run the report. Note the technical names

3. Export this report to Excel

4. Open up the saved Excel sheet and notice that the report content is copied over

5. Now let’s make a sales order invoice posting

6. We go back to System Administration and run the company size report again and once more save it, this time under a different name, for example ‘Company Size 2’

7. Now we copy the tables in the sheet from the last run ‘Company size report’ to the first saved Company size Excel sheet

So that we have a sheet with 2 different ‘Company size’ report executions – Before and after the Sales Order invoice posting

8. Now add a new column ‘Difference’ with a simple calculation extracting cell ‘C’ from cell ‘H’ and copy it across the rows as highlighted on
the screenshot in red below. Notice also the black box, that shows already some tables that have been affected

9. Now in order to easily identify which tables were affected by the posting we can do this trick, apply a filter in the ‘Difference’ column
and unmark ‘blanks’ in the display of the cells’ values

RESULT

We can now see easily which tables were updated by the Sales Order posting. The same technique can naturally be used by any other kind of operation in AX, such as a Journal posting or a Master Planning run – all it relies on is just tracing which tables were updated since the last run of the report. Have fun !

Free Text Invoices in Microsoft Dynamics AX 2012

Parameter Setup:

• Navigate to Accounts Receivables > Setup > AR Parameters.
• Click Project link/tab.

• Check mark the “Display project-related fields on free text invoices” check box.
• Check mark the “Allow ledger account number to be edited” check box if you want to be able to change the revenue ledger account, which gets defaulted when you choose the project and category in the free text invoice.

Having done that required parameter setup, let us now go and post a free text invoice.

Create and Post a Free Text Invoice:

• Navigate to Accounts Receivables > Common > Free Text Invoices > All free text invoices and create a new FTI.

• Enter Customer account = 1102. Rest all customer related data will get loaded automatically.
• Click Line details fast tab and click the Project tab.
• Enter the required project and category.

• Notice that, as soon as you enter the project and category, the main account field gets populated with the main account which is setup in the project posting profile.
• If you want to change the account, you can do so, as we have marked the check box in AR parameters forms.
• Now, fill in the invoice description, Quantity and the unit price and finally post the FTI.
• You can then verify the project revenue and see that this gets recorded in the project.