Subquery and Union in Cross-Connection Query Builder

Toad Data Point
1

Hello,

I am trying to combine 5 different tables from 5 different schemas on the same connection using UNION. With those 5 tables being combined I need to do a subquery to a different connection. Am I able to do a subquery after I combine the tables or do I need to do a subquery for each UNION?

Another related question and the reason I’m wanting to do one subquery instead of 5. The subquery is on a different connection and even just selecting a column in a table or adding a where clause causes Toad Data Point to seemingly freezes for minutes before it completes one of those two things I mentioned. Is this normal of cross-connection subqueries or is there something I can do to speed that up?

Thanks

2

Hello,

Is that what you want to do?

subquery.png

i.e. something like this:

SELECT A.ID, Subquery.ID
FROM (SELECT B1.ID FROM B1 UNION
(SELECT B2.ID FROM B2 UNION
(SELECT B3.ID FROM B3 UNION
(SELECT B4.ID FROM B4 UNION
SELECT B5.ID FROM B5)))) Subquery,
A

I don’t think the query builder can do this without the “nested unions”. i.e. It perhaps won’t be able to generate the select statement without all the parenthesis.

I don’t know where the freezing can came from. I’m not able to reproduce the problem. Do you have enabled caching (Options -> Database -> Cache)?

Libor

3

Just tried this and I don’t think this will work because the subquery does not return the same as the UNION queries. Or were you saying the Query Builder may not be able to handle this and I should do it in Editor? The subquery basically filters the main query/UNION queries. Is there any way to copy/paste a subquery?

This is what my settings currently are for cache. Any changes you think I should make?

4

Hmm, perhaps I don’t fully understand what you are trying to do.

You want to do UNION with 5 tables and then use the union as a subquery. But could you please give me a more concrete example, how the subquery should look like?

The example, which I sent you, is also union with 5 tables used as subquery and I was able to create this with query builder. But maybe this is not exactly what you want. The only problem, which I see in my example, is that the unions are in the nested brackets (always only two tables). But I don’t see this as a big problem. Am I wrong?

As I said I don’t know, what might be causing the freezing, but I had a theory, that this might be caused by disabled cache.

Libor