SQL query against each element in a column of data?

The new version of KNIME is looking great! I’ve already been able to simplify a number of my workflows, but there’s one function that I’m struggling with.

How do I execute a SQL query against each element in a column of data and return the results to a new column?

In PL/SQL, I would use something like:

cursor data_c is select data from table_knime;
begin
open data_c;
loop
fetch data_c into v_data;
exit when data_c%NOTFOUND;
select column from table_oracle where column = v_data;
do something;
end loop;
close data_c;
end;

The new Database Looping node doesn’t seem to be quite what I’m looking for. For a given column of data, it processes a request as :

select * from table_oracle where column in (data1, data2, data3,…)

Am I using this node incorrectly, or is there another way to loop through each element in a column and run the query only against that element?

Simon

Wait…I’ve just solved my own question.

  1. The ‘Column selection’ pop-up menu in the Database Looping node only shows columns that are strings. The column that I wanted to use contained integer data and I didn’t even notice it was missing. Added a ‘Number2String’ node.

  2. Changed the default where clause from

where column in (’#PLACE_HOLDER_DO_NOT_EDIT#’)

to

where column = ‘#PLACE_HOLDER_DO_NOT_EDIT#’

Obviously.

Sorry for the disturbance - works as advertised!

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