Feature Request: Database lookup: Keep original data and join

KNIME Analytics Platform
#1

When I use the database looping node I will want to look-up data for a given ID. Hence there is always 1 query per row and the IN clause will always only contain 1 ID. More importantly I would like to append the data returned from the query and keep the original data. This also does not work and an outer join is required after the lookup (because if data is missing the row will be missing from the lookup).

Also some lookups are not possible due to having to use IN. Example here would be lookup by chemical strucutre which uses custom function in database.

I think easiest would be a new node that allows this. Each column would be availabel for putting it into the SQL where clause as filter. The query is run for each row and the selected columns are appended (missing if null).

How can I achieve this currently?

 

#2

Hi,

you do not need to have "IN" in your query. You can formulate any kind of sql query in the dialog as long as you have the #PLACE_HOLDER_DO_NOT_EDIT# in it e.g. SELECT "#PLACE_HOLDER_DO_NOT_EDIT#" from dual in Oracle would simply return a row with the query value as result. This should allow you to query chemical structures.

If you need to be even more flexible e.g. using two columns for filtering you can still "manually" create the sql statement in a loop (see attached example).

For appending the original data you still would need to join the original table with the db result table.

Bye,

Tobias

#3

Yeah, often there is some work around possible but wouldn't you agree that such a node would be a lot easier to use than you suggested workflow?