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.