Dynamic Query Using Aggregative Functions and Joins


Hi All,
Here i written the code to use the Aggregate functions and Joins inDynamic 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));
}

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.