With AX 2012, some new features were added for queries. For those of you who have access to the DEV manuals, one feature is lightly explained in DEV3, the other is absent in the current development manuals. So, time for a blog post!

Before we begin, I have to point out that there is a slightly annoying trend you will notice. One, the shift away from object IDs and to installation specific IDs. It seems that some new classes in AX 2012 now use element names (classstr, tablestr, etc) instead of tablenum, classnum etc. This is a good thing, however, for objects such as queries, it is annoying that the new classes are not consistent in the use of element identification (old classes use the ID, new classes the string). Secondly, I’m not a fan of the Query classes having “build” in their names, but again, now there’s new classes without “build” in the names, and old classes with “build”. Very inconsistent and annoying. You’ll see what I mean.

So, first new feature, which is touched upon in the DEV3 manual, is the “Filter” object, QueryFilter (not QueryBuildFilter as you may expect). This is a new counterpart to QueryBuildRange. The manual states it is used for outer joins, and filters the data “at a later stage”. Unfortunate choice of words, and not very clear. To understand what’s going on here, you need to understand what happens underneath the covers on the SQL level.

Let’s look at the following scenario. We have a customer table, CustTable, and a sales order table, SalesTable, which has a foreign key relationship to the CustTable based on CustAccount. Let’s say we want to retrieve a list of customers, and optionally any sales orders associated with each customer. To accomplish this, one would use an outer join. In SQL, this would translate as follows:

SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM

</code> So far so good. Now let’s say we want to show all customers, and show all sales orders associated with each customer, but ONLY the orders with currency EUR… In SQL, this gives us TWO options:

SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM
        AND SALESTABLE.CURRENCYCODE = 'EUR'

</code> or

SELECT * FROM CUSTTABLE
        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM
        WHERE SALESTABLE.CURRENCYCODE = 'EUR'

</code> So what’s the difference? In the first option, we use AND, which means the currencycode is part of the JOIN ON statement filtering the SALESTABLE. In the second option, using the WHERE keyword, the currencycode is part of the query’s selection criteria… so what’s the difference? If we filter the SALESTABLE using the ON clause, the CUSTTABLE will still show up, even if no SALESTABLEs with currency EUR exist, and it will just filter the SALESTABLE records. However, using a WHERE clause, we filter the complete resultset, which means no CUSTTABLE will be returned if there are no SALESTABLE records exist with EUR as the currency.

That is exactly the difference between QueryBuildRange and QueryFilter when used on an outer join. The QueryBuildRange will go in the ON clause, whereas QueryFilter will go in the WHERE clause. The following job illustrates this, feel free to uncomment the range and comment the filter, and vice versa, and test the results for yourself.

static void QueryRangeFilter(Args _args)
{
    Query                   query;
    QueryBuildDataSource    datasource;
    QueryBuildRange         range;
    QueryFilter             filter;
    QueryRun                queryRun;
    int                     counter = 0, totalCounter = 0;
    
    query = new Query();
    datasource = query.addDataSource(tableNum(CustTable));
    datasource = datasource.addDataSource(tableNum(SalesTable));
    datasource.joinMode(JoinMode::OuterJoin);
    datasource.relations(true);
    datasource.addLink(fieldNum(CustTable, AccountNum),
            fieldNum(SalesTable, CustAccount));
    filter = query.addQueryFilter(datasource,
            fieldStr(SalesTable, CurrencyCode));
    filter.value(SysQuery::value('EUR'));
    //range = datasource.addRange(fieldNum(SalesTable, CurrencyCode));
    //range.value(SysQuery::value('EUR'));
    
    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        totalCounter++;
        if (queryRun.changed(tableNum(CustTable)))
            counter++;
    }
    
    info(strFmt("Customer Counter: %1", counter));
    info(strFmt("Total result Counter: %1", totalCounter));
}

</code> So, I can hear you thinking “Why is this useful? I could just use an inner join!”. Good catch! One of the main reasons this was introduced is for the user interface. Some screens use outer joins, which works fine. However, when a user filters on fields on the form, the result may not be what that user expects. With a queryBuildRange (as in AX 2009), the query would only filter the joined datasource. So now some fields are showing as empty and read-only (because there is no actual record since it was filtered), but some fields are still showing up (the parent datasource). In this situation, using QueryFilter makes sense. And in fact, AX by default now uses QueryFilter on Forms for any filters a user adds.

Next, the feature that is not mentioned in the DEV books: QueryHavingFilter. For those of you familiar with SQL statements, you are probably aware of the HAVING statement.

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

</code>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: <pre>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');

queryRun = new QueryRun(query);
while (queryRun.next())
{
    custTable = queryRun.getNo(1);
    info(strFmt("Group %1: %2", custTable.CustGroup, custTable.RecId));
} }

</pre></code> 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!

Unfortunately, the HAVING statement is not yet added to the inline SQL statements of X++. So currently the only way to use this feature is by using query objects.

There is no comment section here, but I would love to hear your thoughts! Get in touch!