One of the really great features of Dynamics AX as a development tool is the possibility to write embedded SQL. This basically means that you can write select statements that are controlled by the compiler and get results back directly to table variables.
The following list is taken from the SDK and shows the syntax for the select statement and the parameters allowed with the it:
| ,
Description |
Syntax |
|---|---|
|
SelectStatement |
|
|
Parameters |
[[FindOptions] [FieldList |
|
FindOptions |
|
|
FieldList |
Field { , Field} | |
|
Field |
Aggregate (FieldIdentifier) | FieldIdentifier |
|
Aggregate |
|
|
Options |
[ |
|
IndexClause |
|
|
WhereClause |
|
|
JoinClause |
[ |
Check out the SDK for a more in-depth explanation of all the different keywords.
In the following examples, we will have a look at how to create different select statements depending on what data we would like to have available for the rest of the code.
To give you a better understanding of how the different select statements work and what data is returned, we will use the data from the following subsections.
The following list shows the test data for the CarTable:
|
CarId |
ModelYear |
CarBrand |
Model |
Mileage |
|---|---|---|---|---|
|
1 |
2007 |
BMW |
320 |
2299 |
|
2 |
2007 |
Mercedes |
C220 |
2883 |
|
3 |
2008 |
Toyota |
Corolla |
4032 |
|
4 |
2006 |
Volkswagen |
Golf |
49902 |
|
5 |
2002 |
Jeep |
Grand Cherokee |
65662 |
|
6 |
2003 |
BMW |
Z3 |
11120 |
|
7 |
2000 |
Volkswagen |
Golf |
76322 |
The following list shows the test data for the RentalTable:
|
RentalId |
CustAccount |
FromDate |
ToDate |
CarId |
|---|---|---|---|---|
|
1 |
1101 |
24.03.2009 |
25.03.2009 |
1 |
|
2 |
1103 |
23.03.2009 |
25.03.2009 |
3 |
|
3 |
1103 |
02.05.2009 |
11.05.2009 |
1 |
|
4 |
1102 |
10.05.2009 |
17.05.2009 |
5 |
|
5 |
1104 |
10.12.2009 |
20.12.2009 |
6 |
The following list shows the test data for the CustTable:
|
AccountNum |
Name |
CustGroup |
Blocked |
|---|---|---|---|
|
1101 |
Forest Wholesales |
10 |
No |
|
1102 |
Sunset Wholesales |
20 |
No |
|
1103 |
Cave Wholesales |
10 |
No |
|
1104 |
Desert Wholesales |
30 |
Yes |
A select statement can be written specifically to return only one record or to return many records. If we expect the select statement to return multiple records and we would like to loop through these records, we simply embed the select statement within a while loop.
The following examples will demonstrate how to write simple select statements that return different data from the same table.
The first example will select all columns from all records in the CarTable:
static void selectAllRecordsStatic(Args _args)
{
CarTable carTable;
int records;
info("------------------START-------------------");
while select carTable
{
info("--------------NEW RECORD--------------");
info (strfmt("CarId: %1", carTable.CarId));
info (strfmt("CarBrand: %1", carTable.CarBrand));
info (strfmt("Model: %1", carTable.Model));
info (strfmt("ModelYear: %1", carTable.ModelYear));
info (strfmt("Mileage: %1", carTable.Mileage));
records++;
}
info("------------------END-------------------");
info(strfmt("%1 records was selected", records));
}Executing this job will result in the following output to the infolog. Note that only the first records are shown in the following infolog window. When executing it yourself, you can scroll down to see the other records and the END line:
The next example actually does pretty much the same as the first example, but some code has been added to be able to dynamically write the fields in the table. On the other hand, it will also print all the system fields for each record, but it can be a nice exercise for you to understand how you can use the Dict classes (Dict classes are system classes that enable developers to query AOT element metadata at runtime) to create dynamic functionality as follows:
static void selectAllRecordsDynamically(Args _args)
{
CarTable carTable;
DictField dictField;
DictTable dictTable;
int field;
int fieldId;
int records;
str header, line;
// Create a new object of type DictTable based on the carTable
dictTable = new DictTable(tablenum(carTable));
// Loop through the fields on the table.
// For each field, store the field-label in the header variable.
for (field=1; field <= dictTable.fieldCnt(); field++)
{
fieldId = dictTable.fieldCnt2Id(field);
dictField = new DictField(tablenum(carTable), fieldId);
header += strfmt("%1, ", dictField.label());
}
info(strupr(header)); // strupr changes the string to UPPERCASE
// Loop through all the records in the carTable
while select carTable
{
line = "";
// For each record in the carTable, loop through all the fields
// and store the value of the field for this record in the line variable.
for (field=1; field <= dictTable.fieldCnt(); field++)
{
fieldId = dictTable.fieldCnt2Id(field);
dictField = new DictField(carTable.TableId, fieldId);
// Instead of referencing to the fieldname, I reference to the fieldId
// the get the fields value.
line += strfmt("%1, ", carTable.(fieldId));
}
info(line);
records++;
}
info(strfmt("%1 records were selected", records));
}Executing this job will result in the following output in the infolog:
The next example will select all columns from the record in CarTable where the CarId equals 1. This means that we will only select one (the first) record and so we do not need the while loop:
static void selectOneRecord(Args _args)
{
CarTable carTable;
select firstonly carTable
where carTable.CarId == "1";
info (strfmt("Car Brand: %1", carTable.CarBrand));
info (strfmt("Car Model: %1", carTable.Model));
info (strfmt("Model Year: %1", carTable.ModelYear));
info (strfmt("Mileage: %1", carTable.Mileage));
}Executing this job will result in the following output in the infolog:
The next example will select only the CarBrand and Model columns from all records in the CarTable where the ModelYear value is higher than 2005:
static void selectWhereStatement(Args _args)
{
CarTable carTable;
info(strupr("CarBrand, Model"));
while select CarBrand, Model from carTable
where carTable.ModelYear > 2005
{
info (strfmt("%1, %2 ", carTable.CarBrand, carTable.Model));
}
}Executing this job will result in the following output to the infolog:
By default, a while select statement that returns multiple rows will sort the result ascending by the primary index on the table. You can see this in the first two examples in the previous section.
If you would like to have a statement return the rows in a different order, you have to use the order by parameter in the select statement and specify which fields you would like to sort the result by. If you have an index that corresponds with the sorting, you can use the name of the index to order by as well, but then you will have to use the index statement instead of order by. The following example will return all the records in the CarTable sorted in a descending order of mileage:
static void selectRecordsSortedDesc(Args _args)
{
CarTable carTable;
int records;
info("------------------START-------------------");
while select carTable
order by Mileage desc
{
info("--------------NEW RECORD--------------");
info (strfmt("CarId: %1", carTable.CarId));
info (strfmt("CarBrand: %1", carTable.CarBrand));
info (strfmt("Model: %1", carTable.Model));
info (strfmt("ModelYear: %1", carTable.ModelYear));
info (strfmt("Mileage: %1", carTable.Mileage));
records++;
}
info("------------------END-------------------");
info(strfmt("%1 records was selected", records));
}Executing this job will result in the following output in the infolog:
If you would like to retrieve data from several tables or at least use ranges from different tables in the select statement, you should use one of the join parameters listed in the following sections.
The inner join is the most common join as it joins two tables that are linked together typically by a one-to-many relationship.
The first table used in the select statement, should be the "many" part of the relationship, so in our example, we can say that a record from the CarTable can exist many times in the RentalTable making the RentalTable being used first.
As you might notice, the sorting in a joined select is done first with the innermost table; in this case, the CarTable. When no sorting has been specified, AX uses the primary index set on the table. In this case, it uses the CardIdx index on the CarTable:
static void selectInnerJoin(Args _args)
{
CarTable carTable;
RentalTable rentalTable;
while select rentalTable
join carTable // same as writing inner join
where carTable.CarId == rentalTable.CarId
{
info(strfmt("RentalId %1 is a %2 %3", rentalTable.RentalId, carTable.CarBrand, carTable.Model));
}
}Executing this job will result in the following output in the infolog:
An outer join is used to join two tables but to also include the records that do not have a corresponding match in the joined table. In the following example, you will see that all records in the CarTable are selected even though some of the cars have never been rented:
static void selectOuterJoin(Args _args)
{
CarTable carTable;
RentalTable rentalTable;
while select carTable
outer join rentalTable
where rentalTable.CarId == carTable.CarId
{
if (!rentalTable.RecId)
info(strfmt("No rentals for the car with carId %1", carTable.CarId));
else
info(strfmt("RentalId %1 is a %2 %3", rentalTable.RentalId, carTable.CarBrand, carTable.Model));
}
}Executing this job will result in the following output in the infolog:
The exists join does pretty much the same as the inner join except for 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 in the infolog:
Obviously, the notexists join is the opposite of the exists join in terms of how it works. 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 in the infolog:
In a lot of 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.
The following examples show how the different aggregate options can be used.
The sum aggregate option is used as follows:
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 in the infolog:
The avg aggregate option is used as follows:
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 in the infolog:
The count aggregate option is used as follows:
static void selectCountRentals(Args _args)
{
RentalTable rentalTable;
select count(recId) from rentalTable;
info(strfmt("There are %1 rentals registered in the system", rentalTable.RecId));
}Executing this job will result in the following output in the infolog:
The minof and maxof aggregate options are used as follows:
static void selectMinofMileage(Args _args)
{
CarTable minCarTable, maxCarTable;
select minof(Mileage) from minCarTable;
select maxof(Mileage) from maxCarTable;
info(strfmt("The car with the lowest mileage has a mileage of %2", minCarTable.CarId, minCarTable.Mileage));
info(strfmt("The car with the highest mileage has a mileage of %2", maxCarTable.CarId, maxCarTable.Mileage));
}Executing this job will result in the following output in the infolog:
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 who 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 frequently see the while select statement being used in standard AX, but in case you see the next command, at least now you know it does the same as a while select statement.
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 in the infolog:
