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:
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!
No comments:
Post a Comment