“in” operator in D365FO

Yesterday Michael Fruergaard Pontoppidan published a brief blog post New capability in X++ : The In operator. He mentioned that this feature went unnoticed by most, which is my case too. And I didn’t find anything even when I explicitly looked for more information.

Anyway, such an operator can be very handy and I’m quite sure that everybody who knows IN() operator in T-SQL sometimes missed it in X++.

In short, it allows you to check if a field value is in a set of expected values. For example, if I want to find all sales orders with status either Delivered or Invoice, I can do this:

SalesTable st;
container statuses = [SalesStatus::Delivered, SalesStatus::Invoiced];
 
select from st
    where st.SalesStatus in statuses;

The generated code is what you had to do when there was no ‘in’ operator – it uses OR:

SELECT * FROM SALESTABLE T1
    WHERE (((PARTITION=123) AND (DATAAREAID=N'dat'))
    AND ((SALESSTATUS=3) OR (SALESSTATUS=2)))

I wanted to know what else I can do with this operator and because I didn’t find any documentation, I tried a few things by myself. Note that my environment has platform update 20; it might behave differently in different versions.

I wondered if I can’t use a container directly, instead of putting it to a variable. It would make things simpler if the set is known at design time.

select count(RecId) from st
    where st.SalesStatus in [SalesStatus::Delivered, SalesStatus::Invoiced];

The editor didn’t show any error, but compilation blew up completely:

Abnormal termination with unhandled exception. Exception key: a59d89f7-fe80-4ab3-a420-9a6ba9a30bca. System.NullReferenceException: Object reference not set to an instance of an object.

Hmm, let’s try something else. Which data types can I use? What about a set of string values, which is a common scenario?

container ids = ["S01", "S02", "S03"];
 
select st
    where st.SalesId in ids;

This fails already in the editor. The compilation error is: Types ‘str’ and ‘container’ are not compatible with operator ‘in’. 🙁

The last thing I tried was using ‘in’ operator outside a query. I didn’t expect it to work and it indeed doesn’t. This doesn’t compile; it fails with ‘)’ expected.

if (highestStatus in statuses)

The ‘in’ operator is useful and I’m glad it’s been added. But I see space for improvement, especially the ability of using it with string fields would be very useful.

5 Comments

  1. This is true, we only implemented in the context of data access statements. We have upcoming deliverables that implement the rest, i.e. “in” as an dyadic operator anytype and containers, as you describe.

Leave a Reply to Martin Dráb Cancel reply

Your email address will not be published. Required fields are marked *