If you want to use wild-cards in Ax, you can write a SQL statement with a LIKE keyword SalesTable salestable; select firstonly salestable where salestable.salesId like '123*'; But if you want to use NOT LIKE you can do in 3 different ways : SalesTable salestable; select firstonly salestable where !(salestable.salesId like '123*'); SalesTable salestable, salestableExists; … Continue reading AX / D365FO – Using “Not Like” in X++ select statements
Category: select statement
AX / D365FO – Group by and Aggregate functions on select statement
Thiis simple code shows how to do a Query using a select statement with GROUP BY clause and Aggregate functions like COUNT, SUM, AVG, etc.. CustTable custTable; while select count(RecId), CustGroup from custTable group by CustGroup order by custTable.RecId desc { info(strFmt("%2 - %1", custTable.RecId, custTable.CustGroup)); }
AX / D365FO – “NOT EXISTS” join on an AOT Query
If you need to create a relation between 2 tables using a NOT EXISTS JOIN on an AOT Query you can use the join mode property
AX / D365FO – Conditional ‘Where’ Clauses in ‘Select’ Statements
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
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 