Log in to AnswerLog in to Follow
Joseph Puangco
607Views
8Comments
Solved
How do I perform IN operator in aggregate?
Question

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

0
0
on 2019-10-28

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


6
3
on 2020-04-30

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!


2
0
on 2019-10-28

This works for me! Thanks!

1
0
on 2020-01-28

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 :)

6
0
on 2020-01-29

Good one, Raymark. Very insightful.

1
0
on 2020-02-24

Super Like. I was struggling a bit to get the results

1
0
on 2020-04-09

@all How does this performs (performance wise)?

0
0
on 24th Mar

Hi , Raymark Cosme

this is very old topic but very helpful for me , how I can apply more than one index function in aggregate similar to your  previous answer. Single index function work for me on one attribute but when I applying two index function with AND on second attribute doesn't showing output .

Thanks in adv. 

0
0
9 days ago
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.