Oracle – Can a SELECT Query Return Table Name After FROM Keyword?

oracleplsql

I query a table which has another table name in one column. I must get that column value for searching that table. To do that I try something like this;

select cap from (select table_name from table2 where mi_prinx=(select mip1 from table1 where mip1=184425 and startmi1=0 group by mip1,startmi1 having count(*)>=4);

Inner Select statement returns table3 name. But this does not working. Can I write something like that or should I use Pl/Sql for that.

I am using something like this for now;

declare
table_name varchar2(100);
obj_mi_prinx number;
cap varchar2(100);
begin
select table_name,obj_mi_prinx into table_name, obj_mi_prinx from table2 where mi_prinx=(select mip1 from table1 where mip1=184425 and startmi1=0 group by mip1,startmi1 having count(*)>=4);
dbms_output.put_line('table_name: ' || table_name);
if(table_name = 'table3') THEN
select cap into cap from table3 where mi_prinx=obj_mi_prinx;
END IF;
dbms_output.put_line('mi_prinx in yb_ob_boru: ' || obj_mi_prinx);
end;

If I can do this without using IF statement, I prefer it.

Best Answer

If you have a somewhat limited number of tables that could be returned by the query, you could have the query select from all of them, but only return results for the one matching the data returned by the table1/table2 query. That would look something like this:

With TableVariable As (
   SELECT table_name FROM table2 
   WHERE mi_prinx=(
      SELECT mip1 FROM table1 WHERE mip1=184425 AND startmi1=0 GROUP BY mip1,startmi1 HAVING count(*)>=4)
   )
SELECT cap FROM TableA WHERE 'TableA' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableB WHERE 'TableB' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableC WHERE 'TableC' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableD WHERE 'TableD' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableE WHERE 'TableE' = (SELECT Table_Name FROM TableVariable)
UNION ALL
SELECT cap FROM TableF WHERE 'TableF' = (SELECT Table_Name FROM TableVariable)
;

If the number of tables is too long then you'll need to use pl/sql, which may include the dynamic SQL mustaccio mentioned.

Related Question