在X++使用Not Like

最新推荐文章于 2024-05-23 20:23:59 发布

GuanXX

最新推荐文章于 2024-05-23 20:23:59 发布

阅读量1k

点赞数

分类专栏： Dynamics AX 文章标签： query performance join sql

27 篇文章 2 订阅

订阅专栏

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.

关注