Sunday 10 August 2014

Using NOT LIKE in X++ select statements and query ranges

Hi Friends,
In this post I'll like to share how to use NOT LIKE operator in X++.
LIKE keyword is available in X++ but  NOT LIKE is not directly supported the way it works in SQL. It still can be achieved using X++ select statements as well as in query range values.

For illustration, I'll use an example to select all customer group records which do not start with "1" and do not start with "2", using a select statement as well as using a query object in X++.

Query Object:
If we need to write this in query range, then you need to use the power of expressions in the query range value. Visit using expressions in query ranges to get more details.
In the expression use '!1*' to define a not like condition and then separate other values  by a comma (,) as a comma will represent an AND condition in the query range expression. 


To quickly use the code, here it goes:

    CustGroup               custGroup;
    Query                   query;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    QueryRun                qr;

    query = new Query();
    qbds  = query.addDataSource(tableNum(CustGroup));
    qbr = qbds.addRange(fieldNum(custGroup,CustGroup));
    qbr.value(strFmt("%1,%2",strFmt('!%1*','1'),strFmt('!%1*','2')));

    info (qbds.toString());
    qr = new QueryRun(query);
    while (qr.next())
    {
        custGroup = qr.get(tableNum(CustGroup));
        info (custGroup.CustGroup);

    }


Select Statement:
If we need to write in select statement, then you can achieve by enclosing the like condition in brackets and then use a ! operator (! is the NOT operator in X++). Refer Keywords in X++ for details.

So if there are multiple NOT LIKE conditions which needs to be applied in a single statement, then each condition needs to be enclosed in brackets and need to have a ! operator applied as shown below.

You can see that the customer groups table has three records which starts with 1 and 2 but they are not shown in the info log.

To quickly reuse the code here it goes:

 CustGroup CustGroup;
    ;
    while select CustGroup
        where (!(CustGroup.CustGroup like '1*') && !(CustGroup.CustGroup like '2*'))
    {
        info(CustGroup.custGroup);

    }


The above code works well with AX2012 R3/R2/RTM and 2009 versions. I have not tested in any previous versions. The above information as also be found at MSDN  X++, ANSI SQL Comparison: SQL Select

Thanks for reading the blog and keep sharing.

6 comments:

  1. Hi Priyanka,
    i was trying to extract CXO's and this code worked for me Thank you So mach.
    below is my code
    HRPPartyPositionTableRelationShip pTable;
    Query query;
    QueryBuildDataSource ds;
    QueryBuildRange rTitle;
    QueryRun queryRun;
    ;
    query = new query();

    ds = query.addDataSource(tablenum(HRPPartyPositionTableRelationShip));

    rTitle = ds.addRange(fieldnum(HRPPartyPositionTableRelationShip,Title));
    rTitle.value(strfmt("%1 %2",strfmt('%1','Chief'),strfmt('%1','*Officer')));

    queryRun = new QueryRun(query);
    while(queryRun.next())
    {
    pTable = queryRun.getNo(1);
    info(pTable.Title);
    }

    ReplyDelete
    Replies
    1. Hi Ghasuddin,
      Glad that the post was helpful. Thanks for sharing your scenario. Have a great day.

      Thanks,
      Priyanka

      Delete
  2. Hi Priyanka,
    Thanks for this - nowhere else have I found how to use wildcards in queries. Excellent work!

    ReplyDelete
  3. i am trying something similar, please help me out

    public void executeQuery()
    {
    ;

    ProdTypeQBR.value(SysQuery::value ('7P*'));

    //ProdTypeQBR.(queryvalue ('7P'));
    super();

    }

    ReplyDelete
  4. thanks for this, its work on me ,
    i trying use queryValue(SysQuery::valueLikeAfter(""))) or value like
    but doesnt work

    good work!

    ReplyDelete
  5. Is there a way for OR instead AND?

    ReplyDelete