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?

cletus · Accepted Answer · 2009-05-21 11:28:59Z

2

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.

answered May 21, 2009 at 11:28

cletus

627k169 gold badges922 silver badges945 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:48:35Z

1

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

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered May 21, 2009 at 12:00

tuinstoel

7,30630 silver badges28 bronze badges

Comments

Gary Myers · Accepted Answer · 2009-05-22 02:11:48Z

1

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

answered May 22, 2009 at 2:11

Gary Myers

35.5k4 gold badges52 silver badges77 bronze badges

Collectives™ on Stack Overflow

oracle subselect with dynamic table and column

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related