X++ | Technique | Conditional select statement

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!