‘Not Like’ in Dynamics AX

In X++, we can use Like ‘*someIdentifier’ to implement the Like keyword.
select firstonly purchTable
where purchTable.purchId like 00007*‘;
However if you want to use ‘Not Like’ in X++ SQL statement, you have three options:
The first option, using ‘!’ as ‘not’,
select firstonly purchTable
where !(purchTable.purchId like ‘00007*‘);
The second option, using notExists join
PurchTable purchTable, refPurchTable;
select firstonly purchTable
notExists join refPurchTable
where purchTable.purchId ==00007*‘;

Please make sure that you do put purchTable.purchId in condition statement, otherwise the SQL statement will retrieve an empty result set.
The last option, using Query
Query query = new Query();
QueryRun queryRun;

query.addDataSource(tableNum(PurchTable)).addRange(fieldNum(PurchTable, PurchId)).value(‘!00007*‘);
queryRun = new QueryRun(query);
purchTable = queryRun.get(tableNum(PurchTable));
print purchTable.PurchId;
Using NotExists join seems more complicated than the first option, but actually there is no performance difference between them.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: