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.