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!
*This post is locked for comments