Using ‘Not Like’ in Ax X++

October 13, 2009 No comment

When you want to use wild-cards in Ax, you can write a SQL statement with a LIKE keyword

1
2
select firstonly purchTable
where purchTable.purchId like '09*';

When you want to have all the other records (not like), in X++ SQL-statements you have 3 possibilities:
1.!LIKE :

1
2
select firstonly purchTable
where !(purchTable.purchId like '09*');

2. notExists join :

1
2
3
select firstonly purchTable
    notExists join refPurchTable
    where purchTable.purchId == '09*';

3. Query-object :

1
2
3
4
5
6
7
8
9
10
11
Query query = new Query();
QueryRun queryRun;
; 
query.addDataSource(tableNum(PurchTable)).addRange(fieldNum(PurchTable, PurchId)).value('!09*');
queryRun = new QueryRun(query);
if(queryRun.next())
{
    purchTable = queryRun.get(tableNum(PurchTable));
    print purchTable.PurchId;
}
pause;