Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Query with OR and Not Like

(0) ShareShare
ReportReport
Posted on by 140

Hi,

can i use OR with Not Like in a query range?

OR with like works...

(AccountNum LIKE "005*") || (Name LIKE "SGBD*")

...but how change to Not Like

 

Regards

Michael

*This post is locked for comments

  • Gao Lu Wang Profile Picture
    Gao Lu Wang 180 on at
    RE: Query with OR and Not Like

    Maybe it is too late. But I just found the similar issue today in AX 4.0.

    Since Like and Not Like is not supported,my solution is :not use OR  and "not like" explicitly  as follows.  

    CustTable custTable;
    Query query = new Query();
    QueryRun queryRun;
    QueryBuildDataSource qbds = query.addDataSource(custTable.TableId);
    //QueryBuildRange qbr = SysQuery::findOrCreateRange(qbds, fieldNum(CustTable, AccountNum));

    qbds.addRange(fieldnum(custTable, AccountNum)).value('C904029*');
    qbds.addRange(fieldnum(custTable, AccountNum)).value('!C*');

    //info(range);
    info(qbds.toString());

    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
    custTable = queryRun.get(tableNum(CustTable));
    info(custTable.AccountNum);
    }

    The info message is :  SELECT * FROM CustTable WHERE ((AccountNum LIKE N'C904029*') OR (NOT (AccountNum LIKE N'C*')))

  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Query with OR and Not Like

    Replacing the values with fixed data is correct instead of using the SysQuery, does essentially the same.

    I am afraid your observation is correct and the syntax is not supported in AX 2009 probably.

    You could try to raise a ticket with Microsoft to address the issue, mainstream support is still active until next year:

    support.microsoft.com/.../search

  • Michael N Profile Picture
    Michael N 140 on at
    RE: Query with OR and Not Like

    Hi Vilmos,

    in AX 2009 i have no SysQuery::valueLikeAfter.

    I think it only add '*' after the string ?

    str    range       = strFmt('((AccountNum LIKE "%1") || (!(AccountNum LIKE "%2")))', 'C904029*', 'C*');

    Then i got following as range ((AccountNum LIKE "C904029*") || (!(AccountNum LIKE "C*"))).

    This is the same syntax ievgen posted before.

    When i run the job, i got nearly the same error, with info  all customers.

    7382.Query-NOT-LIKE-Error.jpg

    So, if it works in AX 2012 with my range line, the answer here is:

    OR + NOT LIKE in QueryBuildRange don't work in AX 2009.

     

  • Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Query with OR and Not Like

    Ievgen's syntax is correct. I do not have AX 2009 in front of me, but are you sure you have CustTable.Name field? In AX 2012 it is no longer there.

    The below code works perfectly fine in AX 2012 as per your requirements. It correctly returns all my customers that start with that specific C-account, and also return all customers which do not start with the C character.

    If it does not work for you like this, then it might be a limitation of AX 2009. But first of all I would double-check that Name field.

    static void WIK_LikeNotLike(Args _args)
    {
        CustTable               custTable;
        Query                   query       = new Query();
        QueryRun                queryRun;
        QueryBuildDataSource    qbds        = query.addDataSource(custTable.TableId);
        QueryBuildRange         qbr         = SysQuery::findOrCreateRange(qbds, fieldNum(CustTable, AccountNum));
        str                     range       = strFmt('((AccountNum LIKE "%1") || (!(AccountNum LIKE "%2")))',
    SysQuery::valueLikeAfter('C904029'),
    SysQuery::valueLikeAfter('C')); qbr.value(range); info(range); info(qbds.toString()); queryRun = new QueryRun(query); while (queryRun.next()) { custTable = queryRun.get(tableNum(CustTable)); info(custTable.AccountNum); } }
  • Michael N Profile Picture
    Michael N 140 on at
    RE: Query with OR and Not Like

    Sorrry but i don't found an example for QueryBuildRange with OR + NOT LIKE

    I found many examples before, but nothing with OR + NOT LIKE:

    If you have an example syntax, please publish it here.

  • DavidGunawan Profile Picture
    DavidGunawan 1,381 on at
    RE: Query with OR and Not Like

    Please look into the link that i provided. It provides the example of code.

  • Michael N Profile Picture
    Michael N 140 on at
    RE: Query with OR and Not Like

    How is the syntax for OR + NOT LIKE in x++ QueryBuildRange.

    It's no difference, if i use the syntax in advance filter, or in x++ QueryBuildRange.

  • DavidGunawan Profile Picture
    DavidGunawan 1,381 on at
    RE: Query with OR and Not Like

    Hi,

    I believe the answers above is not for query dialog in advance filter. Please try to use the expresion in x++ by select statement or QueryBuildRange.

    You can look into this link for example:

    www.doens.be/.../using-not-like-in-ax-x

  • Michael N Profile Picture
    Michael N 140 on at
    RE: Query with OR and Not Like

    Hi Ievgen,

    i try it, and got following error.

    Same as all my trys before

    Query-NOT-LIKE-Error.jpg

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: Query with OR and Not Like

    Hi Michael Niemeyer,

    Try this:

    ((AccountNum LIKE "005*") || (!(Name LIKE "SGBD*")))


Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,979 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,848 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans