DB Query Reader can't support multiple queries

Hi Team,

Is there any way for DB Query Reader node to support multiple queries?

Thanks,
Srinivas

This logically corresponds to nodes one output port. If you thinking about intermediate temp tables, you need to use

following by DB Query Reader.

4 Likes

Hi @ShinagdeS , perhaps some additional info such as some examples of what you are trying to do can help us be more precise in what solution to propose.

2 Likes

Hi @bruno29a , below is how my sample query looks in DB Query Reader node.
I got the error as can’t support multiple queries.

DROP TABLE IF EXISTS #temp1;
select *
into #A
from table_A;

DROP TABLE IF EXISTS #temp2;
select *
into #B
from table_B;

select * from #temp1 t1
left join #temp2 t2
on t1.id = t2.id;

Hi @izaychik63, I want to read the data after querying it.
So I think I can’t use DB SQL Executor Node.

You can do it pure KNIME way. See example below

2 Likes

@izaychik63 knime node takes a lot of time to execute.
So I am trying to do it in sql.

Hi @ShinagdeS , you can use the DB SQL Executor followed by the DB Query Reader to achieve what you are trying to do:
image

You run your drop/create table in the DB SQL Executor and make sure you select “Support multiple SQL statements” and chose semi-colon as separator:

Note: I think your statement was meant to be:

DROP TABLE IF EXISTS #temp1;
select *
into #temp1
from table_A;

DROP TABLE IF EXISTS #temp2;
select *
into #temp2
from table_B;

And you then run your select via the DB Query Reader:

You will be able to access the temp tables since you are running on the same DB connection

EDIT: Just a note: If that’s just a use case example, then it’s fine to proceed this way, but if it’s going to be the same use case, then you do not need to go through temp tables. You could just do:

select * from table_A t1
left join table_B t2
on t1.id = t2.id;
2 Likes

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