I would like to set the SQL “where in” conditions from the Tulip App side. Is it possible ?
I was able to write down the conditions directly in the SQL, such like “WHERE T_1.ID in (‘123’,‘456’,‘789’)”, but I would like to change the conditions dynamically.
The number of condition values may change, so I guess the List type would be suitable to pass the values.
Hey @ta-aoki
This is something you can totally do in a trigger.
To achieve this I made a list variable (List_var)
and variable that is the text we want to check (ToCheck).
In my trigger all I did was check to see if my variable (List_Var) contains my ToCheck text variable
Does this solve what you are trying to do?
Pete
Thank you for the reply.
Unfortunately, it seem to be not solving my requirements.
This is my situation.
Let’s say we have a relational database table T_1 with two fields(ID,VALUE), five records.
ID VALUE
‘123’ ‘A’
‘456’ ‘B’
‘789’ ‘C’
‘111’ ‘D’
'721 ‘E’
And let’s say that the VAL is updated every hour by some external system (not Tulip).
What I want my Tulip App to do is to get multiple records from this table.
Not a single record.
Not all records.
I need to show some specific records to the App user.
If the record I want to show is always the same, I could just write down all the ID’s in the SQL like this.
SELECT T_1.VALUE AS VAL
FROM DB.T_1
WHERE T_1.ID IN ( ‘123’ , ‘456’ , ‘789’ )
However, the required records are not always the same. I want my Tulip App to specify which records to get.
If the number of records I need is small, I could write down the conditions as variavle.
SELECT T_1.VALUE AS VAL
FROM DB.T_1
WHERE T_1.ID in ( $id1$ , $id2$ , $id3$ )
However in my real situation, the table has more than 5000 records, and I want ~50 records from that table. I do not want to write ~50 variables.
That is why I want to use a list as a Input to the SQL.
You could do something like this:
WITH
--Read inputs into column elements
arrayin AS (SELECT UNNEST(STRING_TO_ARRAY(COALESCE($sequence$,''),',')) AS elements)
--Return number of elements
select * from DB.T_1
join arrayin on elements = T_1.id
You could then send in any number of values as a comma separated values, such as “id1,id2,id3”. The result would be your filtered return… Note: the above code is written in postgres, but there are similar functions in the other SQL languages.
Here is an image to help show how it would work.
1 Like
Thank you very much. It worked ! 
Oracle did not seem to have UNNEST and STRING_TO_ARRAY but I managed to find the way by reading this article.
https://blogs.oracle.com/sql/post/split-comma-separated-values-into-rows-in-oracle-database
1 Like
This answer was very helpful to me.
Here is an example for SQL server:
Blockquote
WITH SplitSequences AS (
SELECT value AS elements
FROM STRING_SPLIT(ISNULL($sequence$, ‘’), ‘,’)
)
SELECT *
FROM DB.T_1
JOIN SplitSequences ON elements = T_1.id