Exists join

An exists join does pretty much the same as the inner join, except one important thing; it does not fetch the records from the joined table. This means that the RentalTable variable cannot be used within the while loop in the following example, as it will never have any data:

static void selectExistsJoin(Args _args)
{
CarTable carTable;
RentalTable rentalTable;
;
while select carTable
exists join rentalTable
where rentalTable.CarId == carTable.CarId
{
info(strfmt("CarId %1 has a matching record in rentalTable",
CarTable.CarId));
}
}

Executing this Job will result in the following output to the Infolog:

NotExists join

Obviously the notexists join is the opposite of the exists join. This means that it will return all records from the main table where there does not exist a record in the joined table as described by the where clause. This means that the following example will produce the opposite result from the previous example:

static void selectNotExistsJoin(Args _args)
{
CarTable carTable;
RentalTable rentalTable;
;
while select carTable
notexists join rentalTable
where rentalTable.CarId == carTable.CarId
{
info(strfmt("CarId %1 does not has a matching record in
rentalTable", CarTable.CarId));
}
}

Executing this job will result in the following output to the Infolog:

Writing aggregate select statements

In many cases, you would like to write select statements that return aggregate data like the sum or average of a field in a set of data. You can also use the count aggregate option to count the number of records in a table matching a where statement (if any). The minof and maxof options can be used in the same way to find the minimum or maximum value of a field in a record set that corresponds to the where statement.

These examples show how the different aggregate options can be used:

• sum

  static void selectSumMileage(Args _args)
  {
  CarTable carTable;
  ;
  select sum(Mileage) from carTable;
  info(strfmt("The total mileage of all cars is %1",
  carTable.Mileage));
  }
  

  Executing this Job will result in the following output to the Infolog:

  

• avg

  static void selectAvgModelYear(Args _args)
  {
  CarTable carTable;
  ;
  select avg(ModelYear) from carTable;
  info(strfmt("The average ModelYear is %1",
  carTable.ModelYear));
  }
  

  Executing this Job will result in the following output to the Infolog:

  

• count

  static void selectCountRentals(Args _args)
  {
  RentalTable rentalTable;
  ;
  select count(recId) from rentalTable;
  info(strfmt("There are %1 rentals registerred in the system",
  rentalTable.RecId));
  }
  

  Executing this Job will result in the following output to the Infolog:

  

• minof and maxof

  static void selectCountRentalsPerCustomer(Args _args)
  {
  RentalTable rentalTable;
  ;
  // Normal while select to loop data
  info ("Using while select:");
  // The result of the count operation is put
  // into the recId field of the tableBuffer
  // since it is an integerfield.
  while select count(recId) from rentalTable
  group by rentalTable.CustAccount
  {
  info(strfmt(" Customer %1 has rented cars %2 times",
  rentalTable.CustAccount, rentalTable.RecId));
  }
  // Looping the rentalTable cusrsor using the next command
  info ("Using next command:");
  select count(recId) from rentalTable
  group by rentalTable.CustAccount;
  while (rentalTable.RecId)
  {
  info(strfmt(" Customer %1 has rented cars %2 times",
  rentalTable.CustAccount, rentalTable.RecId));
  next rentalTable;
  }
  }
  

                                                                        

  Executing this Job will result in the following output to the Infolog:

  

• Group by
  In many cases, aggregate options are used together with the group by parameter in order to list the aggregate for each subpart of a table. In the next example we will find the number of rentals for each customer that has rented cars. I will also demonstrate how to use the next command together with the select statement instead of the while select statement to loop through the records in the result. You will most often see the while select statement being used in standard AX, but in case you see the next command, you will know it does the same as a while select statement. The following example shows how the group by aggregate option can be used:

  select carTable; // Is the same as select * from carTable
  

  Executing this Job will result in the following output to the Infolog:

  

Optimizing the data retrieval

There can be several different steps that you, as a developer, should take in order to optimize the process of fetching data from the database to the application. I will try to cover the most important things for you to keep in mind here.

Using the correct data retrieval method

One important issue, not only for optimization but also for usability, is to select the correct data retrieval method based on what you would like to achieve.

Use queries when you want the users to be able to change the range of data to be retrieved and when the selection criteria are simple enough for the query to handle. If the selection criteria are complex, and there is no need for the users to be able to change the selection criteria, you should opt for a select statement.

If you would like to be able to use the query definition multiple places, you should create a query in the AOT instead of writing it in X++ every time you need to use it. It can also be easier to get a visual overview of a query created in the AOT compared to a query written in X++.

If the selection criteria is complex, there is no need for updating or deleting the data selected and if you would like to be able to use the same selection in many places in the application, then you should consider creating a view in the AOT instead of writing the select statement every time.