Database Concatenate node

iiiaaa · July 14, 2016, 6:21pm

Hi,

Is it possibile to concatenate two database branches into one? It seems that knime misses a Database Concatenate node (in the Database/Manipulation familiy).

Thanks in advance

Regards

thor · July 15, 2016, 12:44pm

You mean a UNION of two tables?

iiiaaa · July 15, 2016, 2:41pm

Hi Thor, Yes, I mean a Union.

Iris · July 15, 2016, 3:05pm

Hi,

there is no native node availabe but please see the workflow attached. There are two wrapped nodes you can use to apply different set operations. Use UNION for the concatenate option.

We will release such a node in the near future as well.

Best regards,

Iris

sql_set_operator.zip

iiiaaa · July 19, 2016, 12:06am

Hi Iris,

thank you very much! Impressive! I think it's an excellent solution in the mean time...

Regards

agaunt · June 5, 2018, 2:42pm

Hi Iris,

are there any news concerning the Database Union / Concatenate node?

Kind regards,
agaunt

Iris · June 6, 2018, 7:05am

Hi Agaunt,
no sorry, this will not make it in the next release. I will give it a +1 from you.
Cheers, Iris

EvanB · January 17, 2019, 5:30pm

Yes, Union would be great, and even more advanced row filtering (e.g. Nominal)

Tim_Napier · May 23, 2019, 2:24am

I would like to see this too please

ipazin · July 16, 2019, 12:39pm

Hi there,

all your requests have been noted.

Br,
Ivan

Korzen · October 15, 2019, 6:29am

Hi @Iris I was using your fantastic node Everything was great when I used s4/hana databeses. However, when I connected to mySQL database it didn’t work. It only took data from the first node (UNION) or double data from the first node (UNION ALL). Both tables which I want to combine/union, has the same stucture. Is it possible?

Give another +1 that it will be in next release

Ps. When I use concatenate node - everything is ok, but I want to do this in database

Iris · October 15, 2019, 9:41am

Hi @Korzen

I have to admit all credits for the nodes go to @tobias.koetter I just did some beautifications.

That sounds strange. I just checked and MySql does not have a special union syntax, it seems standard. Did you try this on a very small example table? Just combinng a few rows and both tables have one column only?

Best, Iris

Korzen · October 15, 2019, 10:19am

I tried - please find attached

Best, Tomek

tobias.koetter · October 22, 2019, 9:54am

Hello Tomek,
what command do you use in the SQL Set Operator node? Have a look at the SQL that is generated in the node by opening the out port view of the SQL Set Operator component. It should look similar to this one

e.g. two select statements combined using the UNION command.
Bye
Tobias

Korzen · October 22, 2019, 11:23am

Hello Tobias, I attached SQL view, it very simple

second one is the same, only difference is UNION => UNION ALL.

Below a attached SQL view, where I connected to data base S4/Hana, and it’s work.

Best, Tomek

Korzen · October 22, 2019, 11:37am

I think I found sth. When I changed enter ports, SQL statement only takes data from the second database, its another IP, port and database name.

so it’s seems to work, only when I UNION tables form the same database?

tobias.koetter · October 22, 2019, 11:39am

Hi Tomek,
that explains it. The node only works when you concatenate data within the same database. the database doesn’t know how to load data from a completely other database. In your case it didn’t throw an exception since the second table name also existed in the first database and it simply concatenated the same table with itself.
Bye
Tobias

Tom-Kuehner · November 2, 2020, 10:13am

Hi there, 4 years later and still no Union Tool???

Korzen · December 2, 2020, 8:29am

And this “SQL Set Operator” works only with “legacy” nodes, not with the new one

supersharp · June 23, 2022, 5:27pm

Hi KNIME, any update to this problem?