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!