Get the T-SQL query from X++

As you might know MSDyn365FO’s data access layer pretty different from T-SQL. This means that if you copy a query on AX and paste it in SSMS it won’t validate in the 99% of the cases (the other 1% being a select * from table).

The differences are specially obvious in the join clauses, for example in AX’s (not) exists join which translates to T-SQL as a WHERE NOT EXISTS (SELECT statement).

To helps us when we want to check data in SQL Server we can use xRecord’s class getSQLStatement method.

CustTable ct;

select generateonly ct
    where ct.AccountNum == 'US-001';

var sql = ct.getSQLStatement();

If we run this code in Dynamics 365 we’ll get the SQL query in the sql variable. Note the generateonly keyword in the select statement which will allow this, without it there’s no SQL 🙂

This is the T-SQL query we get:

SELECT T1.PAYMTERMID,T1.LINEDISC,T1.TAXWITHHOLDGROUP_TH,T1.PARTYCOUNTRY,T1.ACCOUNTNUM,T1.ACCOUNTSTATEMENT,T1.AFFILIATED_RU,T1.AGENCYLOCATIONCODE,
T1.BANKACCOUNT,T1.BANKCENTRALBANKPURPOSECODE,T1.BANKCENTRALBANKPURPOSETEXT,T1.BANKCUSTPAYMIDTABLE,T1.BIRTHCOUNTYCODE_IT,T1.BIRTHPLACE_IT,T1.BLOCKED,
T1.CASHDISC,T1.CASHDISCBASEDAYS,T1.CCMNUM_BR,T1.CLEARINGPERIOD,T1.CNAE_BR,T1.CNPJCPFNUM_BR,T1.COMMERCIALREGISTER,T1.COMMERCIALREGISTERINSETNUMBER,
T1.COMMERCIALREGISTERSECTION, (MORE FIELDS) ,T1.RECVERSION,T1.PARTITION,T1.RECID,T1.MEMO FROM CUSTTABLE T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'usmf')) AND (ACCOUNTNUM=?))

I’ve removed some fields to allow better reading, but you’ll always get all the fields in the SQL query whwn using the getSQLstatement method, even if you select a single field in AX. As you see we have a ? as a parameter in the where clause instead of its value. If you want to show the value you need to use forceliterals in the select statement in D365:

CustTable ct;

select generateonly forceliterals ct
    where ct.AccountNum == 'US-001';

var sql = ct.getSQLStatement();

Then you’ll get this:

SELECT T1.PAYMTERMID,T1.LINEDISC,T1.TAXWITHHOLDGROUP_TH,T1.PARTYCOUNTRY,T1.ACCOUNTNUM, (MORE FIELDS) ,T1.RECVERSION,T1.PARTITION,T1.RECID,T1.MEMO FROM CUSTTABLE T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'usmf')) AND (ACCOUNTNUM=N'US-001'))

Keep in mind that if you use generateonly in a select statement you won’t have any data in your table buffer:

This means that this functionality is only for testing or debugging purposes, if you forget removing this before checking in your code… well, you can imagine.