0

I have a requirement where I would like to know if I can use the output of the query and use it as an input in the same query.

The table name is ALL_TAB_COLUMNS. This is the table structure:

This is how the source table looks like

ROW_ID TABLE_NAME NO_COLUMNS

1-24A   EMP_DIV1    6
1-28B   EMP_DIV2    6
1-32C   EMP_DIV3    7
1-36D   MGR_DIV3    10
1-40E   MGR_DIV4    10

I just want to know if I can publish the count of records of each the table in the fourth column by querying ? So, my final table would look like this.

ROW_ID TABLE_NAME NO_COLUMNS NO_RECORDS_IN_TABLE

1-24A   EMP_DIV1    6         932
1-28B   EMP_DIV2    6         998
1-32C   EMP_DIV3    7         1024
1-36D   MGR_DIV3    10        458
1-40E   MGR_DIV4    10        378



SELECT ROW_ID, 
       TABLE_NAME, 
       NO_COLUMNS, 
       (
           SELECT COUNT(TABLE_NAME) 
           FROM   ALL_TAB_COLUMNS
       ) AS NO_RECORDS_IN_TABLE 
FROM   ALL_TAB_COLUMNS;

Kindly suggest. Thank you!

Siyual
  • 16,415
  • 8
  • 44
  • 58
Cool_Oracle
  • 311
  • 1
  • 4
  • 15
  • 1
    In general, yes, you can use the result of a subquery in an outer query. But not in the way you seem to want to: table and column names must be hard-coded, they can't be the result of reading table names (or column names) from a table, or be the result of a string operation, etc. –  Jan 26 '17 at 21:25
  • @mathguy : Thank you ! – Cool_Oracle Jan 27 '17 at 00:32
  • Used the xml option as provided and it did the trick for count of records. to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) C from .'||TABLE_NAME)),'/ROWSET/ROW/C')) as TABLE_COUNT – Cool_Oracle Jan 27 '17 at 00:33

0 Answers0