How to pass in clause values as parameters to execute select query on external database.

0
I am using external database connector module to connect to an external database. My sql statement has in clause in the select query statement. How do I pass values for in clause as parameters.  SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); Please let me know if this is possible to achieve using external database connector module. Or should I switch to database connector module.    Another question is can I use both modules at the same time executing simple queries with external database connector and switching to database connector for more complex queries. 
asked
4 answers
1

You can probably create something but it will be database specific. Here's an example that takes a single string of ids and uses that in an in clause. This solution is Oracle specific, but other databases probably offer similar functionality.

 

WITH empids AS (
  SELECT empid FROM json_table( '[1,7946,3,4,7942,7943,7945]', '$[*]' columns ( empid number path '$' )) 
)
SELECT *
FROM emp WHERE empno IN (SELECT empid FROM empids);

 

image (2).png

 

image (3).png

answered
0

Hi Vivek,

You could add IN clause values as parameters as below

 

image.png

image.png

answered
0

Hi Vivek,

Can you create a string variable in your microflow called Parameter1 and loop over your list of values so that Parameter1 =  'Germany, France, UK, ... ' and then pass that single parameter to your DB query? This would work for any n number of parameters. 

In the screenshot above, your query would always be 

SELECT * FROM customers WHERE country in ({Parameter1})

answered
-1

Hi Vivek,

you can add parameter indeed , see below

image.png

answered