Exploring AOT Query(s)

Dynamics AX offers to create Queries, using MorphX. The concept is same as creating Visual query in SSRS. Tables are dragged and dropped inside a query; Join Types, can be setup as per need.

In certain situations, it may be needed to investigate a query in order to know what a specific query is, in terms of Actual SQL (Query Language)

In order to know this we can write x++ code, which can tell us about SQL of Query.

Lets go with a simplest example of a query named as CustTable, which is located at following path

AOT\Queries\CustTable 

Following job can tell you SQL of this query

static void ExecuteAOTQueryCust_1SMC(Args _args)
 {
     QueryRun queryRun;
     ;
     queryRun = new QueryRun(queryStr(CustTable));//add any name of query here
     info(queryRun.query().toString());
 }

Lets run this job and following is what we get.

Query CustTable object 26b8f420: SELECT * FROM CustTable(CustTable) 
ORDER BY CustTable.AccountNum ASC, CustTable.PaymMode ASC 
JOIN * FROM CustTrans(CustTrans) 
ORDER BY CustTrans.PostingProfile ASC, CustTrans.PaymMode ASC, CustTrans.TransDate ASC ON CustTable.AccountNum = CustTrans.AccountNum AND ((Approved = 1)) 
JOIN * FROM CustTransOpen(CustTransOpen) 
ON CustTrans.RecId = CustTransOpen.RefRecId

As, we can see SQL of query, which was a visual query created by MorphX,

we can use it for analysis; also we may run it on SSMS for testing purpose, with minor modification.

Lets go bit deeper;

Since a query may have multiple Tables, joined to each other, we might be interested to know query with respect to any child table, which is joined with parent table.

Have a look at below image.

This time, as we can see, our query has more than one tables, which are joined to each other. DirPartyTable is joined to CustTable and further Multiple tables are joined to DirPartyTable.

We might be inserted to know SQL for DirPartyTable in this query. Lets see how we can get query with respect to DirPartTable

Below job  enables you get SQL for any query, targeting a specific table which is part of table.

static void ExecuteAOTQueryCust_2MSC(Args _args)
 {
     QueryRun queryRun;
     ;
     queryRun = new QueryRun(queryStr(CustTableListPage));
     info(queryRun.query().dataSourceTable(tablenum(DirPartyTable)).toString());
 }

 Run this job and below is what you get

SELECT Name, RecId, recVersion, PartyNumber 
FROM DirPartyTable(DirPartyTable) 
WHERE CustTable.Party = DirPartyTable.RecId 
OUTER JOIN City, CountryRegionId, County, District, State, ZipCode 
FROM LogisticsPostalAddress(LogisticsPostalAddress_Primary) ON DirPartyTable.PrimaryAddressLocation = LogisticsPostalAddress.Location 
OUTER JOIN Locator, LocatorExtension, CountryRegionCode 
FROM LogisticsElectronicAddress(LogisticsElectronicAddress_Phone) 
ON DirPartyTable.PrimaryContactPhone = LogisticsElectronicAddress.RecId 
OUTER JOIN Locator FROM LogisticsElectronicAddress(LogisticsElectronicAddress_Email) 
ON DirPartyTable.PrimaryContactEmail = LogisticsElectronicAddress.RecId

We can use this query for any purpose, from analysis to test-execution, before, we actually use MorphX/Visual query for any purpose. This makes us sure about our results and what we are doing as end results.

That's all from now. Happy Daxing!