Microsoft Dynamics AX (Archived)

Query with OR and Not Like

(0) Share

Report

Posted on by Michael N

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

I have the same question (0)

All responses (13)

Answers (1)

Muthusamy 4 on at

Like (2)

Report

RE: Query with OR and Not Like

Hi Michael,

You can write a code like below,

For X++:

Eg. select firstOnly custTable

where !(custTable.AccountNum LIKE "*005*");

For Query:

queryBuildRange.value(!*005*);

Thanks.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report

RE: Query with OR and Not Like

Boolean operators do work with AX as mentioned above within the select statement.

http://daxdude.blogspot.hu/2011/02/use-x-wildcard-like-and-not-like-in-x.html

It also works with advanced filtering as expressions, see the bottom of this article:

http://www.axaptapedia.com/Expressions_in_query_ranges

For the query range you should consider using SysQuery:valueLike() and SysQuery::valueNot()

msdn.microsoft.com/.../sysquery.valuenot.aspx

Was this reply helpful? Yes No
Michael N 140 on at

Like (0)

Report

RE: Query with OR and Not Like

I need OR + NOT Like in query range, not in select statement. (AX 2009)

Following is an OR + LIKE example in CustTable Form Query:

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

I try many, but don't find the syntax to replace LIKE with NOT LIKE.

Someone have an idea, how to do.

Or is it not possible?

Was this reply helpful? Yes No
Mea_ 60,284 on at

Like (1)

Report
RE: Query with OR and Not Like

Hi Michael Niemeyer,

Try this:

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

Was this reply helpful? Yes No
Michael N 140 on at

Like (0)

Report

RE: Query with OR and Not Like

Hi Ievgen,

i try it, and got following error.

Same as all my trys before

Was this reply helpful? Yes No
DavidGunawan 1,381 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Michael N 140 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
DavidGunawan 1,381 on at

Like (0)

Report

RE: Query with OR and Not Like

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

Was this reply helpful? Yes No
Michael N 140 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Vilmos Kintera 46,149 on at

Like (0)

Report
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); } }

Was this reply helpful? Yes No