How do I perform IN operator in aggregate?

Hi Team,

Good day! I have my first aggregate that returns all the Ids and I want to use them to my second aggregate as a filter. How do I perform that, like in traditional SQL I want to put them in IN() operator?

Thanks,

J P

Solution

J P wrote:

Hi Team,

Good day! I have my first aggregate that returns all the Ids and I want to use them to my second aggregate as a filter. How do I perform that, like in traditional SQL I want to put them in IN() operator?

Thanks,

J P

Hi J P,


Based on my screenshot, my first aggregate returns all the Ids and I used ListAppendAll to map the field that I need. On the String_Join I have map again the field that I need and use "#" as a separator.



On the second aggregate, I used "Index("#"+ String_Join2.Text + "#", "#" + Technician.TerritoryId + "#") <> -1" as my filter. This performs the IN operator. The return or test values should look like "#21#22#23#" and so on.



Hope this helps.

Regards,


Raymark


Solution

Raymark Cosme wrote:

J P wrote:

Hi Team,

Good day! I have my first aggregate that returns all the Ids and I want to use them to my second aggregate as a filter. How do I perform that, like in traditional SQL I want to put them in IN() operator?

Thanks,

J P

Hi J P,


Based on my screenshot, my first aggregate returns all the Ids and I used ListAppendAll to map the field that I need. On the String_Join I have map again the field that I need and use "#" as a separator.



On the second aggregate, I used "Index("#"+ String_Join2.Text + "#", "#" + Technician.TerritoryId + "#") <> -1" as my filter. This performs the IN operator. The return or test values should look like "#21#22#23#" and so on.



Hope this helps.

Regards,


Raymark


This is exactly what I needed right now! Thank you so much. Works great!


This works for me! Thanks!

Even though the sample Raymark posted can help you, it's not a good approach in terms of best practices - hard to understand, to much code to get a dataset.

Aggregates do not have the IN operator and this is already an Idea with a lot of votes, please check this to help the idea get stronger: https://www.outsystems.com/ideas/2224/in-clause-in-aggregates


Sometimes the IN operator is not the best solution (has limitations and can be a bottleneck performance-wise). Depending on what you want to achieve, using a JOIN or filter the results afterwards can be a simpler and better solution.

Be mindful of what you want to achieve before jumping to a solution :)