CODEWITHSUNDEEP
sqloracle-database
Noam Gal
Noam Gal

Reputation: 3405

oracle subselect with dynamic table and column

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?

Upvotes: 0

Views: 5048

Answers (3)

Gary Myers
Gary Myers

Reputation: 35401

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

Upvotes: 1

tuinstoel
tuinstoel

Reputation: 7306

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: Identify a table with maximum rows in Oracle

Upvotes: 1

cletus
cletus

Reputation: 625097

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.

Upvotes: 2

Related Questions