IN Conditional Filter on aggregate
Application Type
Traditional Web

Dear All,


I have a problem if i want to filter with IN clause like on sql server but i want use this on aggregate,

I have first aggregate and inside field there is data, for example: "10,11,12"

in second aggregate i want to filter table will compare Id field with IN "10,11,12"


Warm Regard,

Kane


mvp_badge
MVP

Hi Kane,

you can not use IN clause in aggregate that why Outsystem introduce Advance SQL widget you can use it .
or You can use LIKE clause in aggregate .


https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries

https://www.outsystems.com/forums/discussion/49579/advance-sql/



Regards

Rahul

Hi Rahul,

Thanks for your option, but can we manipulate the field with the comma separator so that it can be filtered in such a way


Thanks,

Kane

Hi,

You can pass that values from first aggregate for a text variable, separated with a comma, and then, in the second aggregate, you use a filter with the Index function to verify if that values exists:


Hope this can help.


Best regards,

Ricardo Pereira

Hallo Ricardo,


I have followed the instructions but when I try to test values it does not return precise data

in test values

List Id: "1, 2"

It will return data with Id: 1,2,3,4,5,6,7,8,9,10 -> Not return data specific Id 1 and 2


Thanks,

Kane

Hi Kane,

it is possible to apply the IN clause in an aggregate using index function.

Check this topic which explains how to do it:

https://www.outsystems.com/forums/discussion/53735/how-do-i-perform-in-operator-in-aggregate/

Regards

Hi Kane Loony, 

The best option is to use a Advance Query.

As I understand you are executing 2 query's, one to get the list values and another to filter the data with the IN clause. I believe that is will be more efficient if you use a single Advance Query. Using it will allow you to use other SQL Commands that Aggregates do not allow.

Hope this helps

Best Regards 

Rodrigo Henriques

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