Queries are typically used to ask users about ranges and sorting, and then selecting data based on the feedback from the users. A query can consist of one or multiple data sources, and can be created both as static queries in the AOT or as dynamic queries using X++. Most commonly they are used when the ranges or values are not known until runtime. Static queries are defined in the AOT, whereas dynamic queries are defined in X++ code.
Follow these steps to create a static query in the AOT:
You have now created the skeleton of the query. Let’s now look at how to add sorting and ranges to the query.
To add a sorting to the query, just drag the selected field from the Fields node under the data source and drop it under the Order By node. In our example, we’ll use the Mileage field. You can then select to have it sort ascending or descending by changing the direction property on the sort field.
When the query prompt is executed in a report, the user has the ability to change the sort order.
You can also add ranges to the data source by dragging a field from the Fields node and dropping it onto the ranges. A range can be used to narrow down the result returned by the query, or it can be used as a fixed range that the user cannot change. This is done by adding a value to the value property of the range. Values in a range can be used like this:
Range operator | Description | Example |
, | Selects records where the range field matches any of the values listed | BMW, VW, Volvo |
= | Selects records where the range field is a matching value | =VW |
.. | Selects records where the range field is between the values specified including the values used. | 1000..3000 |
< | Selects records where the range field is less than the value specified | <2000 |
> | Selects records where the range field is greater than the value specified | >2000 |
! | Selects records where the range field is not equal to the value specified | !BMW |
? | Selects records where the ? can be any character | Merc??es |
* | Selects records where the range field matches the characters before or after the asterisk | Merc* |
When the Status property is set to Open, the users can change the range value. If it’s set to Lock, the users can see the range value before executing the query, but they are not allowed to change it. If the status property is set to Hide, the users won’t even be allowed to see the range value.
In our example, we add the ModelYear field from the data source CarTable_1:
In order to select data from more than one table, you can join the data sources in your query. Data sources can be joined in a couple of different ways depending on how you would like to link them. This is done by setting the JoinMode property to the different values shown in the following table:
JoinMode | Description |
InnerJoin | Will return the records where the joined data sources have matching values in the joined fields. Ex: By using the CarTable as the primary data source and using RentalTable as the joined data source, the inner join will fetch all records from the CarTable where there is a corresponding record in the RentalTable. The corresponding records in RentalTable will also be fetched. |
OuterJoin | Will return all the records from the joined table even if they don’t match the joined field. Ex: Compared to the example using the InnerJoin, this will return all records from the CarTable, but also records from the RentalTable that does not have a match in the CarTable. |
ExistsJoin | This is just like the InnerJoin, except the records from the joined data source are not returned. They are only used to filter the primary data source. Ex: In our example it will only return records in the CarTable where there is a match in the RentalTable. Records from the RentalTable will not be fetched. |
NoExistsJoin | This is the opposite of ExistsJoin. It will select records from the primary data source when matching records in the joined data source does not exist. Ex: In our example it will return records from the CarTable that did not have any matching records in the RentalTable. (Cars that has never been rented) |
Follow these steps to add a new data source and join it with the first one:
A query can also be built dynamically using X++ code. This can be the only way of creating the query, if you would like the query to work in one way in some cases and in another way in other cases. An example can be where you would like to join one table if one condition is true and another table if the condition is false. To do this, you need to understand how the query object model works.
The most commonly used classes in the query object model are:
The query definition is set up by creating and linking objects from the query object model together. The following example shows how this is done in order to create a similar query as we did in the previous section of this article when we created a query called RentalCarList in the AOT.
static void queryRentalCarList(Args _args)
{
Query query;
QueryBuildDataSource queryBuildDataSource1,
queryBuildDataSource2;
QueryBuildRange queryBuildRange;
QueryBuildLink queryBuildLink;
;
// Create a new query object
query = new Query();
// Add the first data source to the query
queryBuildDataSource1 = query.addDataSource(tablenum(CarTable));
// Add the range to this first data source
queryBuildRange = queryBuildDataSource1.
addRange(fieldnum(CarTable, ModelYear));
// Add the second datasource to the first data source
queryBuildDataSource2 =
queryBuildDataSource1.addDataSource(tablen
um(RentalTable));
// Add the link from the child data source to the
//parent data
source
queryBuildLink = queryBuildDataSource2.addLink(fieldnum(CarTable,
CarId),fieldnum(RentalTable, CarId));
}
Ok, so now we have the query definition. But that doesn’t help us much unless we are able to execute the query, right?
This example uses the previous example and just adds the QueryRun object and loops through the result by using the next() method on the QueryRun object.
static void queryRunRentalCarList(Args _args)
{
Query query;
QueryBuildDataSource queryBuildDataSource1,
queryBuildDataSource2;
QueryBuildRange queryBuildRange;
QueryBuildLink queryBuildLink;
QueryRun queryRun;
CarTable carTable;
RentalTable rentalTable;
;
// Create a new query object
query = new Query();
// Add the first data source to the query
queryBuildDataSource1 = query.addDataSource(tablenum(CarTable));
// Add the range to this first data source
queryBuildRange = queryBuildDataSource1.
addRange(fieldnum(CarTable, ModelYear));
// Set the range
queryBuildRange.value("2008..");
// Add the second datasource to the first data source
queryBuildDataSource2 =
queryBuildDataSource1.addDataSource(tablenum(RentalTable));
// Add the link from the child data source to the parent data
//source
queryBuildLink = queryBuildDataSource2.addLink(
fieldnum(CarTable,CarId),fieldnum(RentalTable, CarId));
// Create a new QueryRun object based on the query definition
queryRun = new QueryRun(query);
// Loop through all the records returned by the query
while (queryRun.next())
{
// Get the table data by using the get() method
carTable = queryRun.get(tablenum(CarTable));
rentalTable = queryRun.get(tablenum(RentalTable));
info (strfmt("CarId %1, RentalId %2", carTable.CarId,
rentalTable.RentalId));
}
}
The following result is obtained after running the query:
The exact same result will show up if we execute the query that was defined in the AOT in the previous section of this article.
The code would then look like this:
static void queryRunRentalCarListAOT(Args _args)
{
Query query;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
QueryRun queryRun;
CarTable carTable;
RentalTable rentalTable;
;
// Create a new query object based on the Query in the AOT called
//RentalCarList
query = new Query(querystr(RentalCarList));
// Find the datasource for the CarTable
queryBuildDataSource = query.dataSourceTable(tablenum(CarTable));
// Find the range that we added to the query in the AOT
queryBuildRange =
queryBuildDataSource.findRange(fieldnum(CarTable, ModelYear));
// Set the value of the range
queryBuildRange.value("2008..");
// Create a new QueryRun object based on the query definition
queryRun = new QueryRun(query);
// Loop through all the records returned by the query
while (queryRun.next())
{
// Get the table data by using the get() method
carTable = queryRun.get(tablenum(CarTable));
rentalTable = queryRun.get(tablenum(RentalTable));
info (strfmt("CarId %1, RentalId %2", carTable.CarId,
rentalTable.RentalId));
}
}
Views in AX are objects that are used to retrieve data from the database that is stored in the memory on the layer in which the view is instantiated. The views are actually stored as database views on the SQL server. This means that there are potentially great performance benefits of using views compared to using an equivalent query. This depends of course on the complexity of the query, but in general the performance benefits of using a view compared to a query will increase along with the complexity of the query.
Views can be used throughout AX in all places where tables can be used. This includes forms, queries, reports, and X++ code.
Views in AX can never be used to write data, only to read data from the database. This differs from the SQL implementation that has write-back possibilities for views.
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…