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 :
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:
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.
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.
Thanks
Muhammad Zahid.
Hi Muhammad!
ReplyDeleteI am newbie in AX. I need to know what exactly is the difference between these three types apart from the way they are implemented? Thanks in advance!
Hi,
DeleteIn most of the scenarios we have choice of using any one of type of queries from these three types.But if we are creating SSRS Report than it will be good to create the AOT query because the ranges that we apply on AOT query are automatically appears on the report dialog as a filter.On the other hand if we are creating the list page than we need to use AOT query and on the form level or on the report if there is need to modify the query on run time like adding some range or adding any new datasource than in this scenario Query Build API will be use.
let me know if you need any further help related to this.
Delete