When writing a X++ select statement that requires a like it will always translate into "where buffer.fieldId like '%TEXT%' and ...."
For performance, we sometimes only to do "where like 'TEXT%'..." or " where like '%TEXT' and.... " and using the wildcard symbol on both sides prevents an efficient use of existing indexes if they are avalable.
Currently we can only do this with query objects but it requires a lot of additional code just for one little symbol in the converted SQL.
To solve this we could introduce new "like" keywords as alternatives :
- BeginsLike = SQL : like 'TEXT%'
- EndsLike = SQL : like '%TEXT'
Comments
Thanks Martin, that solves this idea, and it can be closed !
Category: Development
This already supported (from Axapta 3.0 at least) - use the asterisk as a wildcard.
Here is an example:
BOM bom;
select generateOnly forceLiterals RecId from bom where bom.BOMId like 'a*';
info(bom.getSQLStatement());
It shows that T-SQL code is the following:
SELECT T1.RECID FROM BOM T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'dat')) AND (BOMID LIKE N'a%' ESCAPE '\' ))
Category: Development