This post is inspired (or in other word, re-written) from the nice post of Ramesh Singh https://stoneridgesoftware.com/conditional-where-clauses-in-select-statements-in-dynamics-ax.
Background
In many situations, we need a conditional select statement. To do so, the most practical way is to use the thing I called it the Query pattern (Query, QueryBuildDataSource, QueryRun, …).
The Query pattern absolultely works well, but there is an alternative technique.
Example
Let's check a standard method \Data Dictionary\Tables\CustTable\Methods\find.
static CustTable find(CustAccount _custAccount,
boolean _forUpdate = false)
{
CustTable custTable;
;
if (_custAccount)
{
if (_forUpdate)
custTable.selectForUpdate(_forUpdate);
select firstonly custTable
index hint AccountIdx
where custTable.AccountNum == _custAccount;
}
return custTable;
}
Let assume we add a new custom flag field IsActive, then we might need to extend the above implementation. There are several way to implement it. For example, adding a new method and name it like findActive or replacing the above statement with an if-else clause.
However, regarding Ramesh technique, we can make it a bit more simpler by the following code.
static CustTable find(CustAccount _custAccount,boolean _isActiveCheck = false,)boolean _forUpdate = false) { CustTable custTable; ; if (_custAccount) { if (_forUpdate) custTable.selectForUpdate(_forUpdate); select firstonly custTable index hint AccountIdx where custTable.AccountNum == _custAccount&& (!_isActiveCheck || custTable.isActive); } return custTable; }
The above technique is similar to the conventional QueryBuildDataSource.addRange we usually did.
My opinion
In general, to build a dynamic and complex select statement (or query), the Query pattern is still my preferred choice.
But when adding a small customization as the above example, this technique is quite useful. It avoids the unnecessary redundant and safe a couple line of codes, even sacrifice a little bit self-described characteristic.
Thanks for reading. Until the next post!
