Pages

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.

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

   info (accountingEvent.getSQLStatement());

That turns out something like this:


3 comments:

Jan B. Kjeldsen said...

It works with all version of AX 2012. Super usefull, especially when combined with forceLiterals, when you can use it in an SQL Query.

http://dynamicsaxgyan.wordpress.com/2012/05/11/get-underlying-sql-query-using-getsqlstatement-dynamics-ax-2012/

Palle Agermark said...

Good addition to this tip. Thanks.

Rainy Huynh said...

good...thanks!!!!