Queries In Dynamics AX 2012

Types Of Queries :

There are three types of queries in dynamics ax 2012:

1. Inline Queries.

2. Aot Queries.

3. Queries objects and API.

1. Inline Queries:

Inline queries are similar to the Linq queries in dot.

In inline queries you need to create the table buffer.

Table Buffer is quite similar to the objects in classes.

In Dynamics Ax tables can also be treat as a classes where you can write your own methods.

Below is the simple example of inline queries.

CustTable custTable;

 select AccountNum from custTable order by AccountNum where custTable.AccountNum>="1000";

info(custTable.AccountNum);

I you want to show all the records in the info box than we use while statement

while   select AccountNum from custTable order by AccountNum where custTable.AccountNum>="1000";

{  

 info(custTable.AccountNum);

 }

We can also use aggregate functions  in  inline queries.

Here is the example given below:

 select count(AccountNum) from custTable where custTable.AccountNum>"1000";

info(custTable.AccountNum);

Joins in Inline Queries:

Lets make it little bit more interesting by using joins:

HcmBenefit hcmBenefit;

HcmBenefitPlan hcmBenefitPlan;

while select * from HcmBenefit join HcmBenefitPlan

where hcmBenefit.BenefitPlan==hcmBenefitPlan.RecId

{

info("%1,%2",hcmBenefit.BenefitPlan,hcmBenefitPlan.RecId);

}


Group By Clause in Inline Queries:


Group by clause is basically use when we use aggregate functions.Lets see an example:

while select count(RecId) from hcmBenefit
join Description from hcmBenefitPlan
 group by hcmBenefit.BenefitPlan,hcmBenefitPlan.Description
where hcmBenefit.BenefitPlan==hcmBenefitPlan.RecId
{
info(strfmt("%1,%2",hcmBenefit.RecId,hcmBenefitPlan.Description));
}


Inline Queries -Select Options :

If you want to explore more than you can refer to the following link:

 

http://msdn.microsoft.com/en-us/library/aa656402.aspx

 

Simple Insert,Update,Delete:

Insert:

Student  student;

student.name="muhammad zahid";

student.insert();

Update:

select forupdate from student where student.name=="muhammad zahid"

if(student.RecId>0)

{

ttsbegin;

student.name="Baber Tareen";

student.update();

ttscommit;

}

note:The if check was only to check that if the record exist or not that i have fetch.You can remove this if check if you want.

Delete:

select forupdate from student where  student.name="Baber Tareen";

if(student.RecId>0)

{

ttsbegin;

student.delete();

ttscommit;

}

Insert And Update multiple records at a Time:

For inserting multiple data at a single time we use insert_recordset and for updating multiple data at a single time we use update_recordset 

update record set

insert_recordset:

insert_recordset myTable(myNum,mySum)

select myNum,sum(myValue) from anotherTable

group by myNum where myNum <= 100

note:here mytable is the table in which we are inserting the bulk record and the fileds are those in which we inserting data and than the select query which fetch the record whic we are inserting in our table.    

update_recordset:

update_recordset student setting

name="zahid";

the above code will update all the names fields to zahid.You can also use where clause to update the selected record set.

The pseudo code for the update record set is:

update_recordset my TableBuffer setting

field1=1,

field2=fieldX + filedY

where field1==0

 

AOT Queries:

AOT Queries are those queries that we made in AOT by the help of the wizard.

below is the image of the AOT Queries.



Calling Aot Queries:

 

HcmBenefit hcmBenefit;

 

HcmBenefitPlan hcmBenefitPlan;

 

Query q=new Query(queryStr(BenefitQuery));

 

QueryRun qr=new QueryRun(q);

 

while(qr.next())

{

hcmbenefit=qr.get(tableNum(hcmBenefit));

 

hcmBenefitPlan=qr.get(tableNum(hcmBenefitPlan));

 

info(strfmt("%1,%2",hcmBenefitPlan.Description,hcmBenefit.RecId));

}

 

Query Build API:

Query:

 

Contains the definition of the query.Can consist of one data source or several data sources if they are related.

 

Query Run:

Class use to execute the query and loop through reuslt.

 

Query Build DataSource:

Links to one datasources in query.

 

Query Build Range:

 

Enables the end user to limit the result by adding a value in the specified query range.

 

QueryBuildFieldList:

 

List of all the fields in data source.

 

Query Build Link:

 

Links two data sources in a join.Is set on the child data source.

 

Using Aggregates and Sorting:

 

 

 

 

 

Using Joins:

 

 

some usefull links so you can deeply explore.

 

http://msdn.microsoft.com/en-us/library/aa883417.aspx

 

http://msdn.microsoft.com/en-us/library/bb394994.aspx

 

http://msdn.microsoft.com/en-us/library/aa638454.aspx

 

Thanks

 

Muhammad Zahid.