oracle subselect with dynamic table and column

Question

I would like to run the following SQL select:

SELECT ID,
NUMERATOR,
(SELECT m.COLUMNNAME FROM m.TABLENAME p WHERE p.numerator = m.numerator
) VALUE,
TABLENAME,
COLUMNNAME
FROM MainTable m;

at the current state it doesn't run at all, naturally, because oracle doesn't recognize the m.TABLENAME at all. Is it possible to somehow do this sort of thing? The same for the COLUMNNAME - even if I temporarily replace the table to some "real" table I've got, at the current syntax it just outputs the value inside m.COLUMNNAME instead of the value inside p, at the column whose name is m.COLUMNNAME.
Hope I managed to be clear about it.

I guess my other way to go about it is to create a procedure that iterates over the first query's results, and execute another select for each of them to fill the VALUE column. Is that the best I can hope to do?

Answer 1

You're going to have to do that with dynamic SQL ie PL/SQL or dynamically construct your SQL statement in your application. As you note, Oracle does not support this behaviour.

Answer 2

It is probably easier to use the solutions of cletus.

But it is not entirely impossible to do that, you can use dbms_xmlgen.getxml(' '), using dbms_xmlgen.getxml(' ') makes it possible to make dynamic sql statements inside a sql statement.

Don't expect good performance!!

See for example: http://stackoverflow.com/questions/390945/identify-a-table-with-maximum-rows-in-oracle/391026#391026

Answer 3

If there's only a handful of possible table name/ column names you could do something like

SELECT ID,
      NUMERATOR,
      case 
        when m.table_name = 'A' and m.column_name = 'B' then
          (SELECT b FROM a WHERE a.numerator = m.numerator)
        when ....
      end VALUE,
      TABLENAME,
      COLUMNNAME
FROM MainTable m;

Anything more than 3 or 4 would make this pretty ugly though