Finance | Project Operations, Human Resources, ...

How the X++ query with IN looks like ?

(0) Share

Report

Posted on by Axel Cage

309

Hi guys,

May I ask, if I want to query using X++ with multiple string, just like IN clause in SQL Server, can I do that ? and what is look like ?

For example I want to query SalesTable, and I have a condition whether my source provide me a Sales Order Number, or not, and also possible the Sales Order Number provided is multiple.

Can we do it in single query ?

FYI, I'm using "select query" not the QueryBuilder.

Thanks,

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Mohit Rampal 12,565 Moderator on at

Like (0)

Report

Hi Axel, As per this article IN operator is supported in F&O. This is interesting as I think it was not supported in previous versions. Let us know if it works for you.

blogs.msdn.microsoft.com/.../

Was this reply helpful? Yes No
Axel Cage 309 on at

Like (0)

Report
Hi Mohit,

Thanks, Upon trying (also searching), it looks like it is only support Enum Values as the example. I have error when using to check my table which has multiple customer account, with something like this :

Container conCustomer; while select CustAccount from MyTableLine group by CustAccount where MyTableLine.Number == _Number { conCustomer = [MyTableLine.CustAccount]; } while select MySecondTable where CustAccount in conCustomer

The editor already threw error saying Types ‘str’ and ‘container’ are not compatible with operator ‘in’.

This is also mentioned in Martin Drab's blog : https://dev.goshoom.net/2018/12/in-operator-in-d365fo/

Thanks.

Was this reply helpful? Yes No
Mohit Rampal 12,565 Moderator on at

Like (0)

Report

Thanks for sharing the link. Is there any reason you are using select query instead of QueryBuilder.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report
Use a query object and add a range for each value. For example:

Query query = new Query(); QueryBuildDataSource salesTableDs = query.addDataSource(tableNum(SalesTable)); salesTableDs.addRange(fieldNum(SalesTable, SalesId)).value(queryValue('SalesId1')); salesTableDs.addRange(fieldNum(SalesTable, SalesId)).value(queryValue('SalesId2')); QueryRun qr = new QueryRun(qr); while (qr.next()) { SalesTable salesTable = qr.get(tableNum(SalesTable)); }

Was this reply helpful? Yes No
Axel Cage 309 on at

Like (0)

Report

Hi Martin,

Which mean there is no other solution except QueryBuilder ?

Reason I hesitant to use it, is only for standard since the current code I'm handling is using Sql query.

Thanks

Was this reply helpful? Yes No
Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

You should stop hesitate and start using it, because it allows you to generate queries in a dynamic way, instead of having the structure hard-coded in X++ select statements. It's exactly what you need, IMO.

It's not the only way - you could also use SysDA framework (which is better for performance but awkward to use).

Was this reply helpful? Yes No

Join the conversation

Community site session details

How the X++ query with IN looks like ?

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

How the X++ query with IN looks like ?

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates