Use variable in where clause with IN

Hi OD-Users,

i have a workflow variable with a list_of_strings: string1, string2
I want to use this list of strings in a where clause inside of a query processor, like this:

select * from inputTable i
where i.filter_col IN (@list_of_strings@)

Unfortunately, this query fails. I tried different things, I wanted to use split(@list_of_strings@, ‘,’), which failed. Also putting it into quotes also fails. Do I miss some important step here? Has anyone encountered a similar problem with a list given as workflow variable, and trying to use it in a where clause? Any help would be appreciated.

Hi Patrick,

we had this issue before too. In our case we had a MultiSelect that allowed to select strings.
We load the list of strings via the variable inside a Custom Input Table. After some other step we do this:

       explode(SPLIT(i.list_strings, ',')) AS string_name
FROM inputTable i

Then you will have a rather random list which you have to extract you strings from with another query:

                        REPLACE( i.string_name , '"', '')
                , '[', '')
        , ']', '') as string_name
FROM inputTable i

This will give you then the content you wanted to, in this case all items of the list separated.
Maybe this is similiar to your problem :slight_smile:

Hi Franziska,

I will give it a try. Thank you

Hello Patrick,

I had exactly the same issue as well and I learned that the magic word here is Query Helper.
This is an processor, which allows you to use an additional input as the query term.
So you use an Extended Mathematical Operation to define the Query and then use it subsequently in the Query Helper.

Workflow outline:

Extended Mathematical Operation (Top right processor):

Query Helper (Bottom Processor):

So in our case, we extracted from the variable attributeKeys_for_structure_owner a list and then used it in the subsequent query.

I hope this helps.

1 Like

Hi, if you just surround your strings in list_of_strings using single or double ticks, the query works fine:


SELECT * FROM inputTable i
WHERE  i.column_1 IN (@list_of_strings@)

EDIT: Important. There should not be any empty space " " separating the strings, or the IN clause will not work properly. The strings must just be separate by a comma, not followed by any whitespace.