Read Part One of Searching for Data in Microsoft Dynamics AX 2009: Part 1 here.

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

Microsoft Dynamics AX 2009 Programming: Getting Started

Get to grips with Microsoft Dynamics AX 2009 development quickly to build reliable and robust business applications with this book and eBook

Published: December 2009
eBook Price: $29.99
Book Price: $49.99
See more

close

Select your format and quantity:

Book 01234567891011121314151617181920

eBook 01234567891011121314151617181920

Using sorting in select statements

By default, a select statement that returns multiple rows will sort the result in ascending order by the primary index on the table (which can be seen in the first two examples of 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 the 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 statement index instead of order by. The following example will return the all the records in the CarTable sorted in descending order by the 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 to the Infolog:

Using joins in a select statement

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 next.

Inner join

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

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 to the Infolog:

Outer join

An outer join is not only used to join two tables, but also to 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 to the Infolog:

>> Continue Reading Searching for Data in Microsoft Dynamics AX 2009: Part 3

If you have read this article you may be interested to view :

• Working with Microsoft Dynamics AX and .NET: Part 2
• Working with Microsoft Dynamics AX and .NET: Part 1
• Customizing Headers and Footers with MS Office Live Small Business
• Searching for Data in Microsoft Dynamics AX 2009: Part 1
• Searching for Data in Microsoft Dynamics AX 2009: Part 3

Microsoft Dynamics AX 2009 Programming: Getting Started

Get to grips with Microsoft Dynamics AX 2009 development quickly to build reliable and robust business applications with this book and eBook

Published: December 2009
eBook Price: $29.99
Book Price: $49.99
See more

close

Select your format and quantity:

Book 01234567891011121314151617181920

eBook 01234567891011121314151617181920

About the Author :

Books From Packt