I am returning the primary key through a query like:
SELECT b.column_name
FROM all_constraints a, all_cons_columns b
WHERE a.constraint_name = b.constraint_name
AND a.table_name = 'tableX';
AND a.constraint_type = 'P'
... and the query returns 3 rows, e.g.
A
B
C
Now I want to create a query that nested in the above query to create another query like:
SELECT A, B, C
FROM tableY
So the A, B, C part needs to be replaced by a query. Any ideas? I am using Oracle 11G.
As Ed and Mat suggested, this is almost certainly best done through EXECUTE IMMEDIATE or some PL/SQL block that outputs a query.
For those rare cases when this must be done in a single SQL statement, try my open source project Method4. It lets you run a query generated by a query. It's powerful, but more confusing, slower, and buggier than a regular SQL statement.
--Sample schema:
drop table tablex;
create table tableX(A number, B number, C number, D varchar2(10),
constraint tableX_pk primary key (A,B,C));
insert into tableX values (1,2,3,'4');
--Query the primary key columns of a table.
select * from table(method4.dynamic_query(
q'[
--Query that returns a query.
SELECT
'select '||listagg(b.column_name,',') within group (order by column_name)||
' from '||a.table_name v_sql
FROM all_constraints a, all_cons_columns b
WHERE a.constraint_name = b.constraint_name
AND a.table_name = 'TABLEX'
AND a.constraint_type = 'P'
GROUP BY a.table_name
]'
));
--Results:
A B C
- - -
1 2 3
EXECUTE IMMEDIATE.