Hi,
Firstly, I’m loving the new (labs) DB nodes. I was wondering though, if there’s a way that I’m missing to do “WHERE IN” queries?
The only way I can manage it is to construct a full query string as a workflow variable, and then inject it into a DB Query node.
Cheers,
Richard
You can save IN list in a temp table and use construction
WHERE IN (select field from TempTable)
This may be a silly question, but is that possible when querying a database for which you have read-only privileges? In any case, it seems like overkill, compared to my current solution.
Ideally I’d like the DB Row Filter node to be pointed at a column and have it group it into a collection for me, with the back end appropriate combination of delimiters, quotations etc.
Try if you are able to create temp tables. Say, in SQL server they are located on Temp DB not in a main one.
https://ask.sqlservercentral.com/questions/140316/creating-temp-tables-in-a-read-only-replica.html
Have you thought about such a construction, using a temporary sub-query. Not entirely sure if every DB would allow that. You might have to see if there will be a problem if the content of the subquery gets too large, and you may want to think about an inner join or smth. But it could work.
kn_example_sqlite_where_in_iris.knwf (56.0 KB)
Thanks for the suggestions everyone. Unfortunately they do rather imply that the answer is no, there is not a convenient way of doing “WHERE IN” queries. Any chance this could be added to the existing query nodes?
In the meantime I’ll stick to my workflow variable based solution.
Look at Database Looping node. It has IN parameter as an input table.
Thanks. It looks like that node might be deprecated soon though, since it’s not in the Labs DB nodes. Also it’s not possible to make the IN clause part of a more complex query, incorporating other DB nodes.
That could be a good question - what KNIME recommends for Database Looping in the Labs? I widely use it and hope it will stay.
OK, turns out there is a way of doing it after all:
I’ll probably have to retool some of my workflows to have the “WHERE IN” clause at the very end of the DB query building part, but it’s as elegant a solution as I was hoping for.
Hi there!
Parameterized DB Query Reader is the replacement for the Looping node.
Br,
Ivan
Thank you, Ivan.
