Thursday, August 7, 2014

Check the SQL statement generated from a regular X++ select statement

So here is another small tip based on me reading Inside Microsoft Dynamics AX 2012 R3.

If you want to know what SQL statement the SQL Server query processor generates based on a regular X++ select statement, you can add to the keyword generateOnly to the statement and afterwards call the getSQLStatement method on the record buffer.

   AccountingEvent         accountingEvent;
   SourceDocumentHeader    sourceDocumentHeader;
   select generateonly accountingEvent
   join sourceDocumentHeader 
       where sourceDocumentHeader.RecId == accountingEvent.SourceDocumentHeader;

   info (accountingEvent.getSQLStatement());

That turns out something like this: