Hello.
I want to select specific rows from DB. I have a csv file which contains IDs of a several users.
It looks something like that:
SELECT * FROM Table1 WHERE id IN (SELECT id FROM CSVFILE)
Currently I select all rows from a table after that I use the joiner node to get necessary data. How to inject the IDs into select query?
Is there any efficient method?
@bilgee welcome to the KNIME forum
You should be able to use the DB Merge node
Or if the number of IDs is small you could transform them into a list and feed that as a flow variable into the where statement.
Or you could upload the CSV into a database table and use that.
The old KNIME database nodes had a Database Looping node. However in the new nodes this was replaced with the Parameterized DB Query reader which has the issue it does 1 query per row (ID in your case) which is slow. You can use this component that emulates that functionality (you will probably need to update the component a bit by now).
How does it work? it simply creates a WHERE clause with IN. How many items you can put into IN depends on the database you use.
Albeit your approach with the join is pretty straight forward and if it isn’t slow (because not that much data) I don’t really see the issue with it.
Thank you.
Our table is quite large so that we cannot select all data from it. I want to minimize the selection. I will try your solution.
Thank you.
I will try your solution.
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.
