Ever found yourself in a situation where you conditionally want to apply a 'where' clause in a select statement in Dynamics AX? Here's how you can do it: There are several scenarios we run into everyday where we are writing a select statement to query data but we only want to apply the 'where' clause … Continue reading AX / D365FO – Conditional ‘Where’ Clauses in ‘Select’ Statements
Category: select statement
AX – D365FO – Using Like in select statement in X++
In SQL it is common to use like when querying data. For example, the following SQL statement queries the CustTable table for all customers that begin with ’12’ select * from CustTable where AccountNum like '12%' It is also possible to use like in X++ syntax. The following X++ statement queries the CustTable table for … Continue reading AX – D365FO – Using Like in select statement in X++
AX – D365FO – Select TOP n rows in select statement – X++
You can only retrieve 1, 10, 100, 1000 records from a select statement using firstonly syntax (like shown below) while select firstonly100 rent order by PostedDate desc where (rent.PostedDate > td && rent.RentalOrderStatus == z_RentalOrderStatus::Posted) || rent.RentalOrderStatus == z_RentalOrderStatus::Approved { info("orders"); } If you want to get different number of rows you can implement a … Continue reading AX – D365FO – Select TOP n rows in select statement – X++
AX – D365FO – Cross-Company in Select statement in X++ and AOT
You can create a cross-company query by using X++ code. There are two ways to do this: crossCompany keyword on the select statementQuery class methods A cross-company query can also be created under the Query node in the Application Object Tree (AOT). A cross-company query cannot return data for companies that the user does not … Continue reading AX – D365FO – Cross-Company in Select statement in X++ and AOT
D365FO – AX – Order By in Select Statements
SalesTable salesTable; select salesTable index hint CustIdx order by CustAccount where salesTable.CustAccount >= '3000' && salesTable.CustAccount <= '4000' && salesTable.FixedDueDate >= 12\12\2004 && salesTable.FixedDueDate <= 05\05\2009;
AX / Dynamics 365 For Finance and Operations blog 