Complex database queries without database looping node

I have a SQL query that requires a couple of in-line views drawn from multiple tables. Each of these used the #PLACE_HOLDER_DO_NOT_EDIT# string of the Database Looping node in order to make the results manageable. How do you do this in a parameterized node when the input is an unparameterized query? Do I use “select * from Entire_Database” in the Database Table Selector? In the Database Looping node this was simple, as the input string to the IN clause of the in-line views could be included in each of them. I also made use of variables for substitution in the SQL query, which doesn’t look possible in the Paramaterized Database Query node. Please could we have the Database Looping node back. We are wanting to make a case for a Knime server, but if this key functionality is deprecated, then that becomes very hard.
Please could you give me an example using the EMP or HR sample databases from Oracle or something that is more substantial than “select * from Hello_world”.

Hello @kuenzifr,

see here:

Br,
Ivan

The table feeding our #PLACE_HOLDER_DO_NOT_EDIT# field usually has a couple dozen rows at most. However, in order to make the query generic, the values need to change each time the query is run, so they need to come from a table. When we get our Knime server, these can come from an input form from the user. If they have to be hard-coded, then the Knime server is not useful.

The item ipazin refers to is 2 years old. I do not feel comfortable replacing known working components with temporary fixes. Is there a timeline for when the Database Looping node will be available in the new framework?

1 Like

I also wait for Database Looping be back. It’s much faster than Parameterized DB Query Reader.

Do I get this correct that this is basically a database implemented chunk loop?

Most likely it is

select * from Table
where key in (select parameter from Parameter_table)

1 Like

Hello,
I know that the reimplementation of the Database Looping (legacy) node has been requested for quiet some time. I’m happy to tell you that the implementation is scheduled for the second half of this year. Until then you can use the component from the KNIME Hub which emulates the same functionality:

This is just a slightly updated version ( I replaced the Table Row to Variable node and added some more description) of the one from the workflow that ipazin linked above.
Bye
Tobias

4 Likes

Hi,

i implemented the missing functionallity of the database looping node by using chunk loop node followed by a group by with concatenation of the specific entries and table row to variable node. In the sql-statement the criteria is implemented as “where in (’[variable]’)”.

The only thing which have been considered for the group by node is that the delimiter for the concatenated string differs if you have a string or integer type.

BR

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.