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 :
SELECT *
FROM #table# AS t
WHERE
($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 :
SELECT *
FROM #table# AS t
WHERE
($A (#1)$ = ‘’ OR A IN ($A_Arr[0]$, $A_Arr[1]$))
AND ($B (#1)$ = ‘’ OR B LIKE (’$B_Arr[0]$’))
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!!
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.
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)
