Advanced filtering in code on Query object

This is by now old topic, call it the ABC of X++, however, for newcomers, or even experienced devs - who sometime are just too focused on how to do complex things that they just forget the basic :), thought to recall the following, whenever you want to add the OR logic operator in your sql where clause, be that only one time or multiple, with of course the additional complexity of precedence.
So, you are creating the query programmatically (of course using the Query* classes), and you need a statement like select * from tableA where columnB = val1 or columnC = val2 just do it like:

    QueryBuildDataSource    qbds;
    QueryBuildRange             qbr;
    str                                      range;

    // initializing the query and the datasource(s) here; will go like qbds = query.datasource ...

    qbr = SysQuery::findOrCreateRange(qbds, fieldNum(tableA, columnB));

    range = strFmt('((%1.%2 == %3) || (%1.%4 == %5))',, fieldStr(tableA, columnA), val1,, fieldStr(tableA, columnB), val2);


 You might have seen this also written with tableStr(tableA) instead of This works when your datasources name is the same as the table (of course), but it will fail when this doesn't happen, so best to keep it safe. As I was saying you could end up writing complex logic like select * from tableA where (columnB = val1 or columnC = val2) and columnD = val4.

No comments:

Post a Comment