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:
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:
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:
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:
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:
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:
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:
select carTable; // Is the same as select * from carTable
Executing this Job will result in the following output to the Infolog:
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.
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.
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…