Thursday, February 10, 2011

Use X++ wildcard (LIKE and NOT LIKE) in X++ select statement

If you want to use wildcards in an AX X++ select statement (*), you will have to use the LIKE command. The following select statement will find all captions in the batch tasks (BatchHistory) table.

For x++ select statements:
select firstOnly batchHistory
     where batchHistory.Caption LIKE "*Test*"


For x++ queries:
queryBuildRange.value(*Test*);

Note the LIKE instead of a '==' and the wildcards inside of the quotations. All other combinations of wildcard usage will work here. This is the same functionality as what the users would put in the grid filtering(eg. '*TEST*' in the caption field filter on the batch tasks form).

However, if you want to find all Captions that do not have the word Test in them (NOT LIKE, !LIKE), you will have to modify the above example slightly.

For x++ select statements:
select firstOnly batchHistory
     where !(batchHistory.Caption LIKE "*TEST*");


For x++ queries:
queryBuildRange.value(!*Test*);

Note the "!(" and ")". Again, this is the same functionality as what the users would put in the grid filtering(eg. '!*TEST*' in the caption field filter on the batch tasks form).
I hope it helps you out!

11 comments:

  1. LIKE should be the last option to use. results may not be accurate and hits performance

    ReplyDelete

  2. Hello ! thank you it was really fun! If you like programire and interesting facts so you should go to check for more here
    click


    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. insert_recordset XYZ (Id) select Id from ABC where ABC.Id like '*1*';
    is throwing "Cannot execute a data definition language command on ABC (ABC). Employee: 0.
    The SQL database has issued an error." can any one of you help in this?

    ReplyDelete

  6. Good clip You shared. Here step by step details shown to learn by RDP and query ssrs report development. Ax 2012. I hope you will like and subscribe

    https://youtu.be/4dRWxco02PY

    ReplyDelete
  7. Good post You shared. Here step by step details shown to learn RLS using Ax 2012. I hope you will like and subscribe
    https://youtu.be/bziW-RI1xUw

    ReplyDelete
  8. May 2019 Pass LPI 701-100 DevOps Tools Engineer Exam Questions with Crack4sure
    LPI 701-100 DevOps Tools Engineer Exam

    ReplyDelete
  9. Amazing - thank you! Microsoft doesn't seem to have bothered sharing this anywhere.

    ReplyDelete