Dynamic Query Using Aggregative Functions and Joins

Here i written the code to use the Aggregate functions and Joins in Dynamic Query in spite of using normal Select statement in Ax 2009

Normal Select Statement:

while select sum(qty) from inventTrans where inventTrans.ItemId ==“OL-2500”
join inventDimgroup
by inventBatchId
where
inventDim.InventDimId == inventTrans.InventDimId
{
// Our Code Here
}



Dynamic Query:

static void Vasanth_Query_Eg1(Args _args)
{
Query query;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
QueryRun queryRun;
Qty total;
InventTrans inventTrans;
;

query = new Query();
queryBuildDataSource = query.addDataSource(tableNum(InventTrans));
queryBuildDataSource.addSelectionField(fieldNum(InventTrans,Qty),SelectionField::Sum);

queryBuildDataSource.orderMode(OrderMode::GroupBy);
queryBuildRange = queryBuildDataSource.addRange(fieldNum(InventTrans,ItemId));
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(InventDim));
queryBuildDataSource.orderMode(OrderMode::GroupBy);
queryBuildDataSource.addSortField(fieldNum(InventDim,InventBatchId));
queryBuildDataSource.relations(true);

queryRun = new QueryRun(query);
if (queryRun.prompt())
{
while (queryRun.next())
{
inventTrans = queryRun.get(tableNum(InventTrans));
total = inventTrans.Qty;
}
}
info(strFmt(“Quantity: %1″, total));
}