Queries

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.

Creating a static query using the AOT

Follow these steps to create a static query in the AOT:

1. Open the AOT, expand the Queries node, right-click on Queries, and select New Query. A new query is then created in the AOT.
2. Right-click on the query, select Properties, and change the name to CarList (or in other cases, to something that describes what kind of data the query is returning).
3. Open a new AOT window, expand the DataDictionary node, and then expand the Tables node.
4. Drag the CarTable and drop it onto the Data Sources node of the new query. You can also drag maps or views to the data source of a query.

You have now created the skeleton of the query. Let’s now look at how to add sorting and ranges to the query.

Adding a sort order 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.

Adding a range to the query

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:

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:

Joining data sources in a query

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:

Follow these steps to add a new data source and join it with the first one:

1. First, we will create a duplicate of the query that we have created so far, as we would like to use the original query in the Reporting Services report. To duplicate any AOT object, right-click on the object and select Duplicate. A duplicate is then created with the prefix CopyOf.
2. Now rename the new query to RentalCarList.
3. Also, change the range under the CarTable_1 data source to ModelYear instead of Model. This range will be used later in this article.
4. Drag another table, map, or view, and drop it onto the Data Sources node below the first data source. In our example, we will add the RentalTable. Therefore, open a new AOT window and browse to Data Dictionary | Tables | RentalTable. Drag the RentalTable and drop it onto the Data Sources node under the CarTable data source in the query.
5. Open the properties of the RentalTable data source in the query and change the Relations property to Yes.
6. If you expand the Relations node under the RentalTable data source, you should now see that the CarTable data source is linked to the RentalTable data source by the CarId. Your AOT should look like this:

Creating a dynamic query using X++

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:

• Query: Contains the definition of the query. Can consist of one data source or several data sources if they are related.
• QueryRun: Class used to execute the query and loop through the result.
• QueryBuildDataSource: Links to one data source in the query. Can be linked to another QueryBuildDataSource object to join linked data sources.
• QueryBuildRange: Enables the end user to limit the result by adding a value in the specified query range.
• QueryBuildFieldList: List of all the fields in data source. OneQueryBuildFieldList object for each QueryBuildDataSource. By default the property Dynamic is set to true so that all fields are returned.
• QueryBuildLink: Links two data sources in a join. Is set on the child data source.

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));
}

Using a query

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

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.