Oracle subheading 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;
in its current state, it doesn't start at all, naturally, because the oracle doesn't recognize m.TABLENAME at all. Is there any way to do this? Same for COLUMNNAME
- even if I temporarily replace the table with some "real" table I got, in the current syntax it just outputs the value inside m.COLUMNNAME
instead of the value inside p in column, name m.COLUMNNAME
.
I hope I was able to figure it out.
My guess is that my other way of doing this is to create a procedure that iterates over the first query results, and make a different selection for each one to populate the column VALUE
. Is this the best I can hope to do?
a source to share
You will need to do this with dynamic SQL , i.e. PL / SQL or dynamically build a SQL statement in your application. As you can see, Oracle does not support this behavior.
a source to share
It might be easier to use cletus solutions.
But it's not completely impossible to do, you can use dbms_xmlgen.getxml(' ')
using dbms_xmlgen.getxml(' ')
to make dynamic sql statements inside a sql statement.
Don't expect good performance!
See for example: Define a table with max rows in Oracle
a source to share
If there are only a few possible table / column names, you can 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 greater than 3 or 4 would make it pretty ugly though
a source to share