MICROSOFT DYNAMICS AX 2012 : DIFFERENCE BETWEEN QUERYBUILDRANGE AND QUERYFILTER

DIFFERENCE BETWEEN QUERYBUILDRANGE AND QUERYFILTER

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

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'

or

SELECT * FROM CUSTTABLE

        OUTER JOIN SALESTABLE ON SALESTABLE.CUSTACCOUNT = CUSTTABLE.ACCOUNTNUM

        WHERE SALESTABLE.CURRENCYCODE = 'EUR'

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));

}