How to add empty ranges in query

In Dynamics AX programming, a common mistake I have seen when it comes to query ranges is passing an empty string value to the range. The developer here is expecting it to show all the records where the range field is empty. Instead, what we get is all records without the range being applied. As baffling as it may seems, it is easily explainable.

The query engine in Dynamics AX, will not add a range if you don’t specify a value or if you specify an empty string because an empty string is treated as no value.

So the following code will result in this SQL statement,

    query = new Query();
    qbds = query.addDataSource(tableNum(ProjTable));
    qbr = qbds.addRange(fieldNum(ProjTable, Name));
    qbr.value('');

Select first fast * from ProjTable;

But what was expected was something like,

Select first fast * from ProjTable where Name == '';

So to work around this issue, use the SysQuery::valueEmptyString() static method which parses the empty strings correctly so that it is added as a range.

The below job shows the difference.

Uncomment the //qbr.value(SysQuery::valueEmptyString()); to see this in action.

static void EmptyQueryRanges(Args _args)
{
    Query                   query;
    QueryRun                queryRun;
  
    QueryBuildDatasource    qbds;
    QueryBuildRange         qbr;
   
    ProjTable               projTable;
    ;

    query = new Query();
    qbds = query.addDataSource(tableNum(ProjTable));
    qbr = qbds.addRange(fieldNum(ProjTable, Name));
  
    // The below range is useless, it doesn't get added. 
    qbr.value('');
   
    // Uncomment the below line and try again. This time the range is added.
    //qbr.value(SysQuery::valueEmptyString());

    queryRun = new QueryRun(query);    

    while (queryRun.next())
    {
        projTable = queryRun.getNo(1);
        print projTable.ProjId;
    }

    pause;
}