select * from person where person.id in (personList)

Hi there,

Suppose we have a @person List which contains some @person id and @person table

Could you please clarify me how I can extract all records from @person table according to @person List?

Thanks


Regads,

Mirzarashid Abbasov 

Hi,

You can do that with an SQL query but, for just that It would be much easier if you just use a aggregate for that purpose like GetPersons like this example:

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

Hi Nuno,

Thanks for your support. 

In my opinion, my case is more complicated than just making a selection from a table. 

Please note that @personList is a list of person identifier, and @personTable is a table and I cannot found IN operator in the @aggregate's filter tools.

Thanks


Regards
Mirzarashid Abbasov

Hi Mirzarashid,

You can use an advanced query with a IN Clause instead. 

https://www.outsystems.com/learn/lesson/1769/advanced-queries-sql/?LearningPathId=0

If you want to go by Advanced query, you can secure your list of ID's this way in order to prevent SQL injection:

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


Solution

Hi Mirzarashidm

Here is an abstract from https://success.outsystems.com/Documentation/Best_Practices/Building_dynamic_SQL_statements_the_right_way

that is i think excactly what you want

Kind regards,

daniel

Dynamically building a "WHERE ... IN (...)" clause

It's not possible to use a prepared statement for the values in a WHERE <column> IN (@valuelist) clause because you cannot replace a query parameter (i.e. valuelist) with an array of values. Therefore, in this case, you must enable the Expand Inline property for the valuelist Query Parameter.

To properly build the values for the "IN" clause you should always use one of BuildSafe_InClauseIntegerList and BuildSafe_InClauseTextList functions available in the Sanitization extension.

Example 1:

1SELECT {Users}.[Username], {Users}.[Surname], {Users}.[Firstname]
2FROM {Users}
3WHERE {Users}.[IsActive] = 1 AND {Users}.[Id] IN (@idlist)

In this example, the idlist query parameter is built using the BuildSafe_InClauseIntegerList function available in the Sanitization extension.

This example assumes that you have filled in a List of "Integer" Records — where "Integer" a structure defined in the Sanitization extension with a single attribute for holding a long integer value — with the user identifiers for the "IN (@idlist)" filter. For example:

userids[0] = 4
userids[1] = 45
userids[2] = 76

Define the value of the idlist SQL Query Parameter using the BuildSafe_InClauseIntegerList function to build the content of the IN clause:

idlist = BuildSafe_InClauseIntegerList(userids)

idlist will contain the text "4,45,76".

Example 2:

1SELECT {Users}.[Username], {Users}.[Surname], [Users].[Firstname]
2FROM {Users}
3WHERE {Users}.[IsActive] = 1 AND [Users].{Surname} IN (@namelist)

In this example, the namelist query parameter is built using the BuildSafe_InClauseTextList function available in the Sanitization extension.

This example assumes that you have filled in a List of "Text" Records — where "Text" is a structure defined in the Sanitization extension with a single attribute for holding a text value — with the surnames for the "IN (@namelist)" filter. For example:

surnames[0] = "Smith"
surnames[1] = "Johnson"
surnames[2] = "Martinez"

Define the value of the namelist SQL Query Parameter using the BuildSafe_InClauseTextList function to build the IN clause:

namelist = BuildSafe_InClauseTextList(surnames)

namelist will contain the text "'Smith','Johnson','Martinez'".

Solution

Helena Lameiro wrote:

Hi Mirzarashid,

You can use an advanced query with a IN Clause instead. 

https://www.outsystems.com/learn/lesson/1769/advanced-queries-sql/?LearningPathId=0

If you want to go by Advanced query, you can secure your list of ID's this way in order to prevent SQL injection:

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


Hi Helena, 

Thanks for your support.

In my opinion, the main problem is that @SQL parameters have type restrictions in outsystems. Complex types cannot be used.

p.s. I have closed this "open issue" via another way.


Thanks for all.


Regards
Mirzarashid Abbasov