My problem is that in subquery i want to create table name dynamically
which is a column name of the main query.
eg IN Queues table there is a column TABLE_NAME which stores table name. In the select query on Queues i want the Name of the table and count of the each TABLE_NAME stored.
select TABLE_NAME , select count(*) from TABLE_NAME from QUEUES
But it give me error at TABLE_NAME in the sub query
This requires dynamic SQL, e.g.
SET SERVEROUTPUT ON SIZE 1000000
FOR r IN (SELECT table_name FROM queues)
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||r.table_name INTO v_count;
DBMS_OUTPUT.PUT_LINE( RPAD(r.table_name,30) || ' ' || v_count );
As far as I understand ur question is that you have a queues table, containing names of different tables.
Then using the table_name, you want to display the number of rows in each table specified by the tables names.
Like QUEUES has 2 names, TABLE1 and TABLE2, and
table TABLE1 has 20 rows, and table TABLE2 has 30 rows,
but i wanted a single query.
and i can't use a procedure.
Since a single query is not possible, why not write a .NET version of the procedural code I showed earlier? It will look something like:
open recordset1 for sql = 'select table_name from queues'
while not eof loop
get next record
open recordset2 for sql = 'select count(*) from ' & recordset1.table_name
get record from recordset2
end while loop