Select statement

One of the 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 select statement:

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 have a better understanding of how the different select statements work and what data is returned, we will use the following data:

CarTable

The following table shows the test data for the CarTable:

RentalTable

The following table shows the test data for the RentalTable:

CustTable

The following table shows the test data for the CustTable:

Writing a simple select statement

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 as shown in the following Job:

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 Infolog window. When executing it yourself, you can scroll down to see the other records at the end line. The Infolog screen is shown in the following screenshot:

The next example actually does pretty much the same as the first example, but I have added some code to be able to dynamically write the fields in the table. It will also print all the systems fields for each record, but it can be a nice exercise for you to understand how you can use the Dict classes to create dynamic functionality, as shown in the following Job:

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
//field ID
// to 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 to 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 record and hence, 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 to the Infolog:

The next example will select only the CarBrand and the Model columns from all records in the CarTable where the ModelYear is greater 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:

Executing this Job will result in the following output to the Infolog: Microsoft Dynamics AX 2009 Programming: Getting Started