1

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.

1
  • You'll need something called dynamic SQL. Read the manual about the EXECUTE IMMEDIATE statement.
    – mustaccio
    Jul 27, 2016 at 14:33

1 Answer 1

1

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.

4
  • Or you could explain the plan for the statement and then lookup in the plan_table where operation like 'TABLE%' , that would be much more dynamic.
    – Raj
    Jul 27, 2016 at 19:29
  • @Raj That seems considerably more complex and would still require dynamic SQL. Jul 29, 2016 at 13:05
  • AFAIK, dynamic SQL was created for purposes similar to this. forget explain plan part in my reply, but if you really don't want to do dynamic sql, a ref_cursor based on table_name will be much more maintainable.
    – Raj
    Aug 1, 2016 at 12:29
  • 1
    @Raj Then you should provide it as an answer for Ardahan Aug 1, 2016 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.