Trying to pull tables from a list to insert into a loop

mrbolt 0
Viewed 42

I'm using an Oracle database where I need to run the same query on a multitude of customer database tables all held in the same database.

The query is a select command and runs as such:

select id from customer1_table name where customer1_table.row = 1234

The problem is, instead of running the command 100 times I'm trying to figure out if I can change cusotmer1 to point to a list with all 100 customer names (they each have a unique name for the same table to denote who belongs where) in a loop statement and each successive run of the loop picks a different customer name and inserts it where needed into the select statement. Any help is GREATLY appreciated.

oracle sql
2 Answers

You can use list selection such as:

SELECT * FROM table1 WHERE ID IN (1,2,3,4,...,1001,1002,...)

Syntax may vary between database types.

abo0

You could just use a WITH clause in order to define a scope and just join it:

WITH names_list AS
(
  SELECT name1 AS name FROM dual UNION ALL 
  SELECT name1 FROM dual UNION ALL
  ...
  SELECT nameN FROM dual
)
SELECT name.id 
  FROM customer1_table name 
       INNER JOIN names_list nl ON name.name = nl.name;
Gnqz0
Related