| SQL newbie question: nested query [message #371806] |
Fri, 08 December 2000 07:48  |
Gunter Lange
Messages: 1
Registered: December 2000
|
Junior Member |
|
|
Hello
I want to submit following query : asking for all tables containing the column 'ABC'
> select table_name from user_tab_columns where column_name='ABC'
OK that works !
Now I want to use each table_name and ask for a special contents of the column 'ABC' (such as table_name.column_name='123').
The result of that query should be all table_names that contain the special column ('ABC') with the specified contents ('123').
Is there a way to do this ?
Thanks in advance
|
|
|
|
| Re: SQL newbie question: nested query [message #371807 is a reply to message #371806] |
Fri, 08 December 2000 11:31  |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Gunter,
You may have to do it in two steps. First, spool the output of the following query to a file. This will create select statements for all the tables containing the column you are looking for and the value you are searching. Just copy the entire set and then paste it in the sql plus window. The statements are executed one by one and the values are displayed.
when you run this query, you will be prompted for "Column_Value" and "Column_Name". From your example, you'll enter 123 for column value and ABC for column name.
select 'SELECT '||column_name||' FROM '||table_name||' WHERE '||COLUMN_NAME||' = '||'&COLUMN_VALUE;'
from user_tab_columns
where column_name = '&Column_name'
Alternativy, you can use dbms_sql to dynamically create the select statements from the user_tab_columns, issue them and store the values/spool them to some text file....
But the first method seems simple.
hth
Prem :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
