cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate V
Advocate V

IN Operator is not delegating in SQL Server

Per this page, the IN operator should be delegable in a Filter() function for SQL Server. However, This code won't delegate, which is the warning under the "in" keyword below.

EdHansberry_0-1601574633497.png

The table has about 5K rows, and in this example, colOpenItems only has 1 item listed, so should be pretty fast.

But I get nothing.

 

What am I doing wrong, or is the documentation wrong?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Please go further to the Page for Delegation dedicated for SQL.

It states that:

"The 'in' (substring) operator. Supported for ("string value" in <column>), but not for (<column> in "string value")"

 

My understanding is that you can search for example "ABC" text in Column1. But you cannot search for for itemNo which in your case is obviously not a text string.

 

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Please go further to the Page for Delegation dedicated for SQL.

It states that:

"The 'in' (substring) operator. Supported for ("string value" in <column>), but not for (<column> in "string value")"

 

My understanding is that you can search for example "ABC" text in Column1. But you cannot search for for itemNo which in your case is obviously not a text string.

 

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

View solution in original post

Highlighted

Ok, that is the answer, but I am not sure how that would work. Both columns are text strings, and IN is normally [ListOfStrings] in [Column]. If it is just a single string in a column, how is that different from FieldName = "TextString?"

Highlighted

>>If it is just a single string in a column, how is that different from FieldName = "TextString?"

I believe the difference is that you can do: "TextString" In FieldName, so if TextString were "BC1" you would get a match where the field contained "ABC123". 

Sadly, a proper SQL style In is not supported for delegation.

You could do something like:

Filter(
'[dbo].[IMITMIDX_SQL'
item_no = First(colOpenItems) ||
item_no = Coalesce(Last(FirstN(colOpenItems,2)),-1) ||
item_no = Coalesce(Last(FirstN(colOpenItems,3)),-1) ||
item_no = Coalesce(Last(FirstN(colOpenItems,4)),-1) ...
)

This is turning your comparison of item_no IN (Item1, Item2, Item3) to

item_no = Item1 Or item_no = Item2 Or item_no = Item3

This is not great as you need to code a test for each of the maximum number of items you might want to test against and use coalesce so that if you have fewer items to test against, you are checking for a value you know will not exist (e.g. -1), but it will give you a delegable formula.

 

Highlighted

Thanks @PaulD1 for that explanation. Yeah, I have over 5,000 records to test, so cannot do that. Shame the true SQL IN operator isn't supported. What is supported is just a search basically. 😐

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (2,880)