Ask The Oracle Expert: Questions & Answers

Querying metadata and data at the same time

Querying metadata and data at the same time

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

I'm trying to write an Oracle SQL statement where I'm extracting the name of the table from USER_TAB_COLUMNS, and within the same SQL statement determine if an attribute value from those tables returned by the first query has values. For example:

select a.* from 
 ( select TABLE_NAME 
   from USER_TAB_COLUMNS 
   where COLUMN_NAME='HGT') a 
where HGT is not null;

There is obviously something wrong with the syntax of this SQL, because I get an Oracle error, ORA-00904, saying that the HGT is an invalid identifier.

You will have to break your query up into two steps. The first query will obtain the table name(s) from the dictionary. The second query will utilize a separate subselect for every table found by the first query, and combine the subselects with UNION.

I'm afraid there's no way to query metadata and data at the same time, not if querying the data is driven by results from the metadata query.