Use variable in where clause with IN

Processors, Python, Spark, R
1

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.

2

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:

SELECT 
       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:

SELECT 
        REPLACE(
                REPLACE(
                        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:

3

Hi Franziska,

I will give it a try. Thank you

4

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:

image
image987×319 14.9 KB

Extended Mathematical Operation (Top right processor):

image
image1630×1011 33.7 KB

Query Helper (Bottom Processor):

image
image1636×306 11 KB

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.
Best,
Johannes

1 Like
5

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

image

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.

4 Likes