How to loop in aggregate (SImilar to sql IN clause)

How to loop in aggregate (SImilar to sql IN clause)

  

Hi,

Can the following be done in Outsystem?

SELECT * FROM PRODUCT WHERE PRODUCTID IN (1,2,3)

I understand that aggregate does not support IN clause.

PRODUCTID 1,2,3 will be store in the list of integer. Do i have to use advaned query to achieve that or i can use foreach loop?

Hi Jace,

This can't be done. You need to create an SQL Query to execute this query.

Kind regards,

Remco Dekkinga

Hey Jace,

I actually think it is a good suggestion, and I guess you should suggest that aggregates should be able to include the "IN" clause.

Anyway, since that is not possible yet you must do it with an advanced query.

What I suggest you to do is to use a parameter for example.

You can use a variable of type text. Then you make a for each for example to fill that String with the values you want to include in the "IN" clause. After you make the for each the String should be for example like this:

String - "1,2,3,4,5,6,7,8,9,10"

Then you pass that string as a parameter to your Advanced Query and put it inside the "IN" clause like this:

Select * from Person where Age IN (@Parameter)


Important: If you want to put as input values for example like this:

IN ( 'abc', 'def' ) with " ' " 

You have to set the propriety "Expand inline" of your parameter to "Yes".


Hope that helps,

Cheers,

VC

Vincent Colpa wrote:

Hey Jace,

I actually think it is a good suggestion, and I guess you should suggest that aggregates should be able to include the "IN" clause.

Anyway, since that is not possible yet you must do it with an advanced query.

What I suggest you to do is to use a parameter for example.

You can use a variable of type text. Then you make a for each for example to fill that String with the values you want to include in the "IN" clause. After you make the for each the String should be for example like this:

String - "1,2,3,4,5,6,7,8,9,10"

Then you pass that string as a parameter to your Advanced Query and put it inside the "IN" clause like this:

Select * from Person where Age IN (@Parameter)


Important: If you want to put as input values for example like this:

IN ( 'abc', 'def' ) with " ' " 

You have to set the propriety "Expand inline" of your parameter to "Yes".


Hope that helps,

Cheers,

VC

Hi,

Thanks for helping. I tried your method but i got error indicate ORA-01722: Invalid Number

Solution

Hi Jace,

As told by Remco and Vincent, use PL/SQL to enable query using clause IN. Technically, you can reach the same result using for-loop and aggregate.

And my concern, why don't you put the list with values of type Product Identifier - so it is apple to apple? 

Given:

   1. Records of Product

  2. List of Product Identifier (Integer-your type): 1,2,3


Find:

Solution:

1. Create String by concatenating the ProductId:

2. The final string must be truncate to remove the last comma: (could be this is your error)

3. Then create the Advanced PL/SQL: [do not forget set expand attribute to YES]

4. Supply the formal parameter InStr with argument the truncated string: (do not forget to encapsulate in EncodeSQL function to not accomodate the SQL injection attact)

the final pic is something like this:

and the result list of the PL SQL can be used to populate a List Records widget or else.

Here the oml.

regards,

bb


 

Solution

Hi,

 It's a good Idea to have IN clause in aggregates, In-fact I have shared the same Idea some time back. Anyways advance sql is only way of writing such sql.  

Sachin

Barong Bali wrote:

Hi Jace,

As told by Remco and Vincent, use PL/SQL to enable query using clause IN. Technically, you can reach the same result using for-loop and aggregate.

And my concern, why don't you put the list with values of type Product Identifier - so it is apple to apple? 

Given:

   1. Records of Product

  2. List of Product Identifier (Integer-your type): 1,2,3


Find:

Solution:

1. Create String by concatenating the ProductId:

2. The final string must be truncate to remove the last comma: (could be this is your error)

3. Then create the Advanced PL/SQL: [do not forget set expand attribute to YES]

4. Supply the formal parameter InStr with argument the truncated string: (do not forget to encapsulate in EncodeSQL function to not accomodate the SQL injection attact)

the final pic is something like this:

and the result list of the PL SQL can be used to populate a List Records widget or else.

Here the oml.

regards,

bb


 


Hi,

Thank you very much, it can work, 

Nice posted by Barong Bali.

Nice post. I got 2 little additions to that:

First, make sure that there's at least 1 value in the InStr parameter, in case there are no product id's. In that case you could put "0" there to keep the query from failing ("WHERE {Product}.Id IN ()" gives an error).

Secondly, you can also use String_Join from the extension Text to build the comma-separated string of Product Id's, as shown below.