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));
}
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));
}