How to see the SQL expression of an X++ select sta

问题:

I have below statement in a job in AX:

select RecId from pjiTable 
    join pID, Type, PrId from sjTable
    where pjiTable.Prid == sjTable.PrId &&
         (sjTable.Type == PjType::TimeMaterial || sjTable.Type == PjType::FixedPrice);

I have to use it in SQL Server. How can I convert this select statement into SQL and use it in SQL Server Management Studio?

回答1:

For debugging purposes you can change your select query and use getSQLStatement method on your table:

select generateOnly forceLiterals RecId from pjiTable 
    join pID, Type, PrId from sjTable 
    where pjiTable.Prid == sjTable.PrId  &&
         (sjTable.Type == PjType::TimeMaterial || sjTable.Type == PjType::FixedPrice);    
info(pjiTable.getSQLStatement());

This will show you SQL statement without actually executing the statement.