SQL join for Loop?

Question

I have beginner knowledge on SQL and I am wondering whether this is possible in SQL.

SQL query 1 >>

select distinct(id) as active_pod from schema_naming

Query 1 output >>

active_pod
DB_1
DB_2
... 
DB_20

SQL query 2 >>

select * from DB_1.mapping UNION
select * from DB_2.mapping UNION
....
select * from DB_20.mapping UNION

Due to my limited knowledge on SQL, I'm currently running #1 query first and change DB_1, DB2,.. DB_20 in query 2 everytime and run #2.

However, I was wondering whether there's way to this in one query so I don't have to manually change DB number in the #2 query and don't have to union every line.

something like this..(but not sure what to do with union)

select * from {
select distinct id from schema_naming}.user_map

It will be great if someone can shed light on this. (I'm trying to do this on Oracle SQL)

thank you in advance.

Why do you have so many copies of the mapping table across databases? This seems more like a problem of bad design — Phil
– Phil, Commented Aug 25, 2017 at 0:56
@maSTAShuFu, I edited my Q so my objective is more clear. thanks for note! — user1486507
– user1486507, Commented Aug 25, 2017 at 1:12
You might want to look into creating dynamic queries in a stored procedure / function. See docs.oracle.com/cd/B19306_01/appdev.102/b14261/… — Phil
– Phil, Commented Aug 25, 2017 at 1:18

Vishwanath Dalvi · Accepted Answer · 2017-08-25 02:26:54Z

2

Are you trying to get something like this?

SELECT 'SELECT * FROM ' || active_pod  || '.' || 'Mapping UNION'
FROM
(
select distinct(id) as active_pod from schema_naming
) as DT;

answered Aug 25, 2017 at 2:26

Vishwanath Dalvi

36.9k41 gold badges128 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1486507 Over a year ago

actually i was looking for a simple query that will return the output of entire union. I start to think that this can be only done with dynamic query but the above concatenating trick is very good to know! thank you!

Paweł Zaraś · Accepted Answer · 2017-08-28 14:32:23Z

1

Alternatively, use PL/SQL block:

BEGIN
For i in (SELECT 'SELECT * FROM ' || ACTIVE_POD || '.MAPPING UNION' AS QUERY
FROM SCHEMA_NAMING) loop
dbms_output.put_line(i.query);
end loop;
END

Your queries will appear in the output window on your IDE.

answered Aug 28, 2017 at 14:32

Paweł Zaraś

283 bronze badges

Comments

dave · Accepted Answer · 2017-08-25 02:21:27Z

This is a definitely a hack but it might make your life easier until a better solution is proposed. Basically use a query to generate your 2nd query, only manual edit needed would to remove the unecessary UNION on the final line.

SELECT 'SELECT * FROM ' || ACTIVE_POD || '.MAPPING UNION' AS QUERY
FROM SCHEMA_NAMING

Results:

SELECT * FROM DB_1.MAPPING UNION
SELECT * FROM DB_2.MAPPING UNION
SELECT * FROM DB_3.MAPPING UNION
SELECT * FROM DB_4.MAPPING UNION
SELECT * FROM DB_5.MAPPING UNION
SELECT * FROM DB_6.MAPPING UNION
SELECT * FROM DB_7.MAPPING UNION
SELECT * FROM DB_8.MAPPING UNION
SELECT * FROM DB_9.MAPPING UNION
SELECT * FROM DB_10.MAPPING UNION
SELECT * FROM DB_11.MAPPING UNION
SELECT * FROM DB_12.MAPPING UNION
SELECT * FROM DB_13.MAPPING UNION
SELECT * FROM DB_14.MAPPING UNION
SELECT * FROM DB_15.MAPPING UNION
SELECT * FROM DB_16.MAPPING UNION
SELECT * FROM DB_17.MAPPING UNION
SELECT * FROM DB_18.MAPPING UNION
SELECT * FROM DB_19.MAPPING UNION
SELECT * FROM DB_20.MAPPING UNION

Collectives™ on Stack Overflow

SQL join for Loop?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related