4 min read

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:

Desciption

Syntax

SelectStatement

select Parameters

Parameters

[ [ FindOptions ] [ FieldList from ] ] TableBufferVariable [ IndexClause ] [ Options ] [ WhereClause ] [ JoinClause ]

FindOptions

crossCompany | reverse | firstFast | [ firstOnly | firstOnly10 | firstOnly100 | firstOnly1000 ] | forUpdate | noFetch | [forcePlaceholders | forceLiterals] | forceselectorder | forceNestedLoop | repeatableRead

FieldList

Field { , Field } | *

Field

Aggregate ( FieldIdentifier ) | FieldIdentifier

Aggregate

sum | avg | minof | maxof | count

Options

[ order by , group by , FieldIdentifier [ asc | desc ] { , FieldIdentifier [ asc | desc ] }] | [ IndexClause ]

IndexClause

index IndexName | index hint IndexName

WhereClause

where Expression

JoinClause

[exists | notexists | outer ] join Parameters

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:

CarId

ModelYear

CarBrand

Model

Mileage

1

2007

BMW

320

2299

2

2007

Mercedes

C220

2883

3

2008

Toyota

Corolla

4032

4

2006

Vokswagen

Golf

49902

5

2002

Jeep

Grand Cherokee

65662

6

2003

BMW

Z3

11120

7

2000

Volkswagen

Golf

76322

RentalTable

The following table 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

CustTable

The following table shows the test data for the CustTable:

Account Num

Name

CustGroup

Blocked

1101

Forest Wholesales

10

No

1102

Sunset Wholesales

20

No

1103

Cave Wholesales

10

No

1104

Desert Wholesales

30

Yes

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:

Microsoft Dynamics AX 2009 Programming: Getting Started

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 {
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 {
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:

Microsoft Dynamics AX 2009 Programming: Getting Started

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:

Microsoft Dynamics AX 2009 Programming: Getting Started

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


Subscribe to the weekly Packt Hub newsletter

* indicates required

LEAVE A REPLY

Please enter your comment!
Please enter your name here