Home > Blog > Conditional ‘Where’ Clauses in ‘Select’ Statements in Dynamics AX

Conditional ‘Where’ Clauses in ‘Select’ Statements in Dynamics AX

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

Happy coding!

Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.

Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.