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!!!





14 comments:

  1. thanks a lot for this great information it was exactly what i am looking for

    ReplyDelete
  2. thank you, this really helped!

    ReplyDelete
  3. Hello , I want to know if exist something similar to IN operator in SQL , to do something like this Range.value(strFmt('CovInventDimId in (%1)', list))

    ReplyDelete
  4. thanks for the info .... i was really helped with this info ....do you have somethng to share on difference between inventory recalculation and inventory close.

    ReplyDelete
  5. Wonderful Explanation ... Really nice and very easy to understand ... Keep posting such light weight articles :-)

    ReplyDelete
  6. Dear,

    This ain't working when there are multiple DataSources joined together, system place "AND" statement instead of "OR". Did you come across with the fact?

    ReplyDelete
  7. Hi Noor,
    Can you please share your code. Please check the queryBuildRange object.
    Which data sources are you using in your query. Would be great if you can explain more on what query you want to achieve.

    Thanks,
    Priyanka

    ReplyDelete
  8. I'm looking for a similar way to define AND conditions in QueryBuildRange for a same field.
    Do you have any idea for that ?
    Thanks
    Kathrin

    ReplyDelete
  9. Hi Kathrin,
    Can you give me an example of the X++ select statement code which you want to convert using query classes. If you apply AND condition on a same field multiple times then you will not get any results because in a record one column will have only one value.

    Thanks,
    Priyanka

    ReplyDelete
    Replies
    1. Hi Priyanka,
      thank you for your reply.
      Your statement that applying multiple AND conditions on the same field will not get any results is only valid for conditions that result in only one value but not if I combine conditions that deliver ranges (BETWEEN or LIKE conditions).
      This is my example:
      select from COSCostTrans
      where accountnumber not like ”5%” AND accountnumber not like “61%” AND accountnumber not like “7%” etc.

      My code looks like this:
      while (meAccountGroup.moveNext())
      {
      queryValue = meAccountGroup.currentValue(); // "6*", " 70*" etc.
      queryValue = strfmt(' !( %1.%2 like "%3") ',
      qbdsLedger.name(),
      fieldstr(COSCostTrans, AccountNumber),
      queryValue);
      qbrAccountGroup = qbdsLedger.addRange(fieldnum(COSCostTrans, AccountNumber));
      qbrAccountGroup.value(queryValue);
      }

      the addRange method combines the conditions with OR , I need it combined with AND.

      This is just an example, I stumbled upon this problem several times and therefore would be very interested if you see any possibility to tell the add Range method what relational operator to use when adding conditions to a query.
      Hope thats understandable.

      regards Kathrin

      Delete
    2. sorry, I mean logical operator instead of relational operator.

      Delete
    3. Hi kathrinxfb,

      I was also looking to achieve what you want for a long time: there is a method on QueryBuildRange called 'associateRangeNodeToCompositeQuery', which forces the AND condition and does exactly what you want. A bit late after your post, but bumped into this post through Google and thought it might still be of use.

      Regards,

      Bjorn Douchy

      Delete
  10. Thanks a lot. This great article has really helped me

    ReplyDelete
    Replies
    1. Thanks for the feedback. Glad it helped. Have a good day.

      Delete