Friday 21 December 2012

X++ code to define OR conditions in QueryBuildRange for a same field in a simple way

This blog post is show how to apply OR conditions in query ranges in a simple way on a same field in a table.

For example let's take a very simple select statement:
select * from CustGroup where custGroup.CustGroup == '10' || custGroup.Group =='20';

We can of course achieve the above by using expression in query ranges (MSDN) but as it has lot of specifications which needs to be followed (Axaptapedia)  and also in situations when we do not know how many values we want to pass in the range, the above approach can be a nightmare.

However there is a simple way also: Just add range and assign value on the same field multiple times as shown below:


Query   q;
QueryBuildDataSource    qbds;
QueryRun  qr;
QueryBuildRange qbr;
;
q = new Query();
qbds = q.addDataSource(tableNum(CustGroup));
qbr = qbds.addRange(fieldNum(CustGroup,CustGroup));
qbr.value(queryValue('10'));
 
qbr = qbds.addRange(fieldNum(CustGroup,CustGroup));
qbr.value(queryValue('20'));
 
info(qbds.toString());

 The resultant select statement can be seen in below image.






Well, there are some points to remember when taking the above approach:

1. Add range on the field every time you want to have a new value in OR condition. If you apply the range only once and assign the value multiple times then system will pick the value which is assigned in the last statement something like the below image:




2. You can use the same queryBuildRange variable to assign the range multiple times. Even if you use different queryBuildRange variables system will apply it correctly. It also works without a queryBuildRange variable, you can directly apply range on QueryBuildDataSource object (this is shown in the last image).








The above code is useful in situations which I talked earlier, when you are getting the range values at run time from some list,map or may be another query. We can add ranges on the same field inside a while loop which creates the corresponding OR conditions in the resulting select statement, a sample of such code is shown below where I created a list having the customer group values on which I want to assign in the OR condition.



Thanks!!!