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.

I have slightly different scenario where I need to pass in list of entity identifiers from static entity. I have Status static entity with values like PENDING and COMPLETED and want to pass those  identifiers into the SQL query input parameter. But I see the query parameter only accepts basic type or Entity Identifier not the List of Entity Identifier. I know I can do ListAppend to create required list in a local variable but I am not able to pass it to SQL query parameter.

Carful doing an aggregate just to pass through a for loop and create a list of identifiers just to pass into an SQL query with an IN, it can be extremely inefficient if you have a lot of identifiers.

A better solution is to create a single complex SQL query that either joins the two tables and filters or does a query with an IN on a sub query. For example


Select {Product}.* from {Product}

Where {Product}.ProductID in (select {ProdcutList}.ID from {ProductList} where <SomeProductListFilter>)


The above combines the aggregate query to get the product id’s with the final query to get the products and is far more efficient than looping through and building a string. A Join between the two tables would work as well in most cases but the above gives you a little more flexibility on the sub query. Note this works well with MSSQL and MySQL, I don’t use Oracle much but theory should be the same.


 


 



Thanks for the advice. I am thinking of that too as alternative. I feel with static entity there should be an easier way to do rather than doing sub query or join as I do not want to hardcode the label of static entity inside query.

Remember that a static entity is just a database table underneath so you can use them in a query the same way as a normal entity, you can even add an extra Attribute to filter on.

For example


SELECT {Items}.*
where {Items}.[Status] in (select {ItemStatus}.[Id] where {ItemStatus}.[FinalType] )


In this I created a dynamic entity called Items and a static entity called ItemStatus. ItemStatus has a boolen field called FinalType which can be set to True or false therefore allowing you to do a filter on a group of ItemTypes without having to hardcode the actual ones.