AX / D365FO – Conditional ‘Where’ Clauses in ‘Select’ Statements

Ever found yourself in a situation where you conditionally want to apply a ‘where’ clause in a select statement in Dynamics AX? Here’s how you can do it:

There are several scenarios we run into everyday where we are writing a select statement to query data but we only want to apply the ‘where’ clause if certain conditions are met. This is easy to do if you are using a query object, like:

         public static void queryData(boolean _applyCondition, anytype _value)
                {
                    Query                query; 
                    QueryBuildDataSource qbds;
                    QueryBuildRange      qbr;
                    QueryRun             qr;
                                
                    query = new Query();
                    qbds = query.addDataSource(TableNum(<Table>));
                                
                    if (_applyCondition)
                    {
                      qbr = qbds.addRange(FieldNum(<Table>, <Field>));
                      qbr.value(_value);
                    }
                                
                      qr = new QueryRun(query);
                                
                      .........etc
                }

But you can also apply the where clause conditionally in a select statement, like:

    public static void queryData(boolean _applyCondition, anytype _value)
    {
        <Table> table;
        
        select table
            where (!_applyCondition || table.<Field> == _value);
        
        .........etc
    }

If ‘_applyCondition’ is passed in as ‘true’, the ‘!_applyCondition’ will be resolved as false and table.<Field> = _value will be evaluated for ‘where’ clause, as the ‘OR’ result with ‘false’ can be true or false.

False OR False   =  False

False OR True    =  True

On the other hand, if ‘_applyCondition’ is passed in as ‘false’, the ‘!_applyCondition’ will be resolved as true, so there’s is no point evaluating table.<Field> = _value condition, as the ‘OR’ result of true with any expression would be true only.

True OR False    =  True

True OR True     =  True

Hence the ‘where’ clause is not applied.

General syntax for the conditional where clause looks as follows: where (!<Condition> || <Table>.<Field> == <Value>).