Searching for Data in Microsoft Dynamics AX 2009: Part 2

Exclusive offer: get 50% off this eBook here
Microsoft Dynamics AX 2009 Programming: Getting Started

Microsoft Dynamics AX 2009 Programming: Getting Started — Save 50%

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

$29.99    $15.00
by Erlend Dalen | December 2009 | BPEL Microsoft SOA Web Services

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:

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

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

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
Select your format and quantity:

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:

Microsoft Dynamics AX 2009 Programming: Getting Started

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:

Microsoft Dynamics AX 2009 Programming: Getting Started

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:

Microsoft Dynamics AX 2009 Programming: Getting Started

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

[ 1 | 2 | 3 ]

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

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
Select your format and quantity:

About the Author :


Erlend Dalen

Erlend Dalen started working as a developer with the first version of Axapta in 1998. From 2000 he spent 2 years developing eCommerce, mobile, and integration solutions in Java for a Norwegian IT consultancy company. He has worked for Columbus IT since 2002, first as a senior developer in Norway and in USA and now as the technology manager of the Norwegian branch, where his responsibilities have been to implement new technology areas, creating an eCommerce solution for Dynamics AX, and being the technology solution architect in internal and customer projects.

Books From Packt

RESTful Java Web Services
RESTful Java Web Services

Joomla! 1.5 SEO
Oracle SOA Suite Developer's Guide

SOA Patterns with BizTalk Server 2009
SOA Patterns with BizTalk Server 2009

Programming Microsoft Dynamics NAV 2009
Programming Microsoft Dynamics NAV 2009

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Microsoft Office Live Small Business: Beginner’s Guide
Microsoft Office Live Small Business: Beginner’s Guide

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

WCF Multi-tier Services Development with LINQ
WCF Multi-tier Services Development with LINQ

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
A
e
a
P
f
b
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software