Building SQL queries in AX 2012

When it comes to building SQL queries, Microsoft’s ERP AX 2012 offers two basic options:

Building it using AOT(Application Object Tree): Popularly known as AOT queries, these are constructed using the ‘Queries’ node in the AOT.

1. Right click ‘Queries’ and click on ‘New query’
2. Give a name to your query, e.g.,CustomerQuery
3. Define the Data source for your query, use the MorphX Drag-Drop functionality and drag the Table you want to specify as the Data source. e.g., CustTable.
4. Next, Select the Data source> Fields and in the Properties, select Dynamic as ‘No’ if you want only specific fields to be ‘Selected’ as part of this query. Else, if you want all the fields of this data source table to be selected always(not a good practice), then select Yes.
5. You can now delete/ add new fields in the data source.
6. Use this AOT query in code(X++) within the function ‘querystr(Customerquery)’ and it will return the values selected in the data source.
The coding block will look somewhat like this :

Query q = new Query(querystr(Customerquery));
QueryRun qr = new QueryRun(q);
while(qr.next())
{
//
}

Second, and a more on-the-go approach is the Dynamic query option, which uses the following building blocks:
QueryRun
QueryBuildDataSource
QueryBuildRange

These allow you to frame a query in X++ code, specify the data source, add ranges/filters, therefore adding a bit flexibility over the AOT queries.

The coding block for dynamic query would look something like this:

Query q;
QueryRun q;
QueryBuildDataSource qbd;
QueryBuildRange qbr;

q = new Query();
qbd = q.addDataSource(TableNum(CustTable));

qbr = qbd.addRange(FieldNum(CustTable,AccountNum));
qbr.value(“≥4000”);

qbd.addSortField(FieldNum(CustTable,DlvMode));

qr = new QueryRun(q);
while(qr.next())
{
//do additional action here
}

These were the two very basic but fundamental insights into the world of Queries in AX 2012. Hope it was worth your read!