I am trying to make the below SQL query work where the list for IN and LIKE clause is variable per row.
My input column ‘A’ can have values like “343,342” which should convert to SQL :
FROM #table# AS t
($A (#1)$ = ‘’ OR A IN ($A$))
AND ($B (#1)$ = ‘’ OR B LIKE (’$B$’))
However, above does not fetch me results that match the list value. If I split column A in separate columns, and then run same query using the individual values in the IN clause, it fetches me the correct results :
FROM #table# AS t
($A (#1)$ = ‘’ OR A IN ($A_Arr$, $A_Arr$))
AND ($B (#1)$ = ‘’ OR B LIKE (’$B_Arr$’))
My issue is that the list can be as long as 10 values, and I need to have this part flexible. Can anyone help hpow to make the SQL IN and LIKE caluse work, most likely I am having a syntactical issue!!
Hi @ManiKa and welcome to the KNIME forum,
Not sure what exactly you are looking for, but as far as I understood, you need to create a dynamic query which can be created by the String Manipulation (Variable) node. Use the join() function to write your query and add flow variables where you need it to be dynamic. Then pass the output (the flow variable created by this node) to the DB Query node and assign the variable to the “sql_statement” option in the Flow Variables tab.
Or if you are using column values, you can use the String Manipulation node to create the sql statement and then convert it to flow variable using the Table Row to Variable node and pass it to the DB Query node.
Hi there @ManiKa,
welcome to KNIME Community!
Can you explain it a bit more? Maybe with example or workflow? Not entirely sure what but maybe check Parameterized DB Query Reader node and see if it can help you.
Hi @armingrudd, @ipazin,
Dynamic query part is fine. The part that has a problem is using the IN clause in the dynamic query. The dynamic value does not consider all values in the list to be used for IN clause.
I am thinking may need to split the data into separate rows for each value of the list (keeping all other columns same)
hmmm. Still not sure on your setup. Maybe provide workflow example using SQLite database?
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.