AX2012 QueryHavingFilter usage

In this Example I am showing how to QueryHavingfilter(New class in AX2012) class
Before seeing this post please see my previous post explains the QueryFilter Class
http://krishhdax.blogspot.com/2012/05/ax2012-query-filter-usage.html 

If we want to get this following Query format from X++ then we can use the QueryHavingFilter class.
Select VendGroup,Count(*) from VendTable group by VendGroup having Count(*) >2


static void Krishh_HavingQueryFilter(Args _args)
{
    Query                   query;
    QueryBuildDataSource    datasource;
    QueryBuildRange         range;
    QueryHavingFilter       havingFilter;
    QueryRun                queryRun;
    int                     counter = 0, totalCounter = 0;
    VendTable               vendTable;
  
    query = new Query();
    datasource = query.addDataSource(tableNum(VendTable));
    datasource.addSelectionField(fieldNum(VendTable, RecId),
            SelectionField::Count);
    datasource.orderMode(OrderMode::GroupBy);
    datasource.addGroupByField(fieldNum(VendTable, Vendgroup));
  
    havingFilter = query.addHavingFilter(datasource, fieldStr(VendTable, RecId),
            AggregateFunction::Count);
    havingFilter.value('>2');
  
    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        vendTable = queryRun.getNo(1);
        info(strFmt("Group %1: %2", vendTable.VendGroup, vendTable.RecId));
    }
}

No comments:

Post a Comment

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