Results 1 to 11 of 11

Thread: SQL Query

  1. #1
    Join Date
    Jul 2003
    Posts
    6

    Unanswered: SQL Query

    Hi All

    I am using Oracle 9i.

    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

    Regards,
    Harmeet singh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Query

    Originally posted by meetharmeet
    Hi All

    I am using Oracle 9i.

    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

    Regards,
    Harmeet singh
    This requires dynamic SQL, e.g.

    Code:
    SET SERVEROUTPUT ON SIZE 1000000
    
    DECLARE
      v_count INTEGER;
    BEGIN
      FOR r IN (SELECT table_name FROM queues)
      LOOP
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||r.table_name INTO v_count;
        DBMS_OUTPUT.PUT_LINE( RPAD(r.table_name,30) || ' ' || v_count );
      END LOOP;
    END;
    /

  3. #3
    Join Date
    Jul 2003
    Posts
    6
    thx

    My problem is that i am using .Net to retrive data.
    And i cant write procedures .

    I need a single SQL query which returns me the result

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by meetharmeet
    thx

    My problem is that i am using .Net to retrive data.
    And i cant write procedures .

    I need a single SQL query which returns me the result
    There is no such single SQL query, sorry. But you could adopt the same logic as I showed above in your .NET code.

  5. #5
    Join Date
    Jul 2003
    Posts
    1

    Re: SQL Query

    Hi,

    For the example u hv given -

    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.

    the following query would serve the purpose:
    select table_name, count(table_name) from queues group by table_name

    cheers!

    Originally posted by meetharmeet
    Hi All

    I am using Oracle 9i.

    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

    Regards,
    Harmeet singh

  6. #6
    Join Date
    Jul 2003
    Posts
    6
    The name of the table i.e table_name is actual table.

    so for each row i will have the table name from the TABLE_NAME column . and for that table name i have to find the count

    I don't want the count of the table _name in the queue table.

  7. #7
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Help on its way

    Hi
    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,

    so you want

    TABLE1 20
    TABLE2 30

    Please confirm ?
    Aruneesh

  8. #8
    Join Date
    Jul 2003
    Posts
    6
    yes thats right

    but i wanted a single query.
    and i can't use a procedure.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by meetharmeet
    yes thats right

    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:

    Code:
    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
      print result
      close recordset2
    end while loop

  10. #10
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    Isn't it possible tow rite something like that

    select a.col1, a.col2.... , CURSOR(Select * from TABLE(A.colx) from ParentTable a


    Found in a sample of using nestedtable , but never tried

    regards

  11. #11
    Join Date
    Aug 2003
    Posts
    1

    You ought to know

    Harmeet,
    Working for a huge multinational client , you ought to know the answer to such elementary questions.
    Your's Sincerely
    Murali

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •