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:
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:
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
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);
}
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);
}
Komentar
Posting Komentar