connect 2022 banner tablet connect 2022 banner mobile

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

By Ramesh Singh | November 4, 2015

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!

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X