‘Not Like’ in Dynamics AX

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.
 
Advertisements
This entry was posted in Axapta Development. Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s