How to use QueryHavingFilter in AX 2012

How to use QueryHavingFilter in AX 2012

 

 

QueryHavingFilter:

Consider the following scenario. The CUSTTABLE table has a field called CUSTGROUP, indicating the customer group the customer belongs to. We would like to get a list of all customer groups that have less than 4 customers in them.
Traditionally, in AX queries, we can group by the CUSTGROUP field, COUNT the RecIds. However, there was no way to filter on that counted RecId field. However, in SQL, the having statement gives you that ability:

SELECT CUSTGROUP, COUNT(*) FROM CUSTTABLE

        GROUP BY CUSTGROUP

        HAVING COUNT(*) < 4

In AX you can count, group by, but you'll need to loop over the results and check the counter manually if you want to filter values out. So, in AX 2012, a new query class was added: QueryHavingFilter, that lets you do just that:

static void QueryHaving(Args _args)

{

    Query                   query;

    QueryBuildDataSource    datasource;

    QueryBuildRange         range;

    QueryHavingFilter       havingFilter;

    QueryRun                queryRun;

    int                     counter = 0, totalCounter = 0;

    CustTable               custTable;

   

    query = new Query();

    datasource = query.addDataSource(tableNum(CustTable));

    datasource.addSelectionField(fieldNum(CustTable, RecId),

            SelectionField::Count);

    datasource.orderMode(OrderMode::GroupBy);

    datasource.addGroupByField(fieldNum(CustTable, CustGroup));

   

    havingFilter = query.addHavingFilter(datasource, fieldStr(custTable, RecId),

            AggregateFunction::Count);

    havingFilter.value('<4 o:p="">

   

    queryRun = new QueryRun(query);

    while (queryRun.next())

    {

        custTable = queryRun.getNo(1);

        info(strFmt("Group %1: %2", custTable.CustGroup, custTable.RecId));

    }

}

Note that in this code example, I added a selection field on RecId and used SelectionField::Count. This is not necessary for the having filter to work, the only reason it is in the code example is to be able to show it in the infolog (ie to have the count value available). So it is independent of the HavingFilter!