'Not Like' in Dynamics AX

** This article is forwarded from Fred Shen's blog **

In X++, we can use Like '*someIdentifier' to implement the Like keyword.e.g.
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', e.g.

select firstonly purchTable
where !(purchTable.purchId like '00007*');

The second option, using notExists join
e.g.

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

e.g.

Query query = new Query(); QueryRun queryRun; ;
query.addDataSource(tableNum(PurchTable)).addRange(fieldNum(PurchTable, PurchId)).value('!00007*');

queryRun = new QueryRun(query);
if(queryRun.next())
{
purchTable = queryRun.get(tableNum(PurchTable));
print purchTable.PurchId;
pause;
}

Using NotExists join seems more complicated than the first option, but actually there is no performance difference between them.