Q
Problem solve Get help with specific problems with your technologies, process and projects.

Querying metadata and data at the same time

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...

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.

This was last published in April 2008

Dig Deeper on Oracle development languages

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close