| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-29-04, 21:44
|
|
Registered User
|
|
Join Date: Feb 2004
Posts: 2
|
|
|
viewing multiple columns
|
|
I want to view table name, column name, and data type for the columns named STREET, CITY, STATE, ZIP. Here is my script whats wrong?
select dba_tables.table_name, column_name, data_type
from dba_tables, dba_tab_columns
where column_name='STREET' and column_name='CITY' and column_name='STATE' and column_name='ZIP'
order by column_name, table_name;
Thanks,
Sam
|
|
02-29-04, 22:25
|
|
Registered User
|
|
Join Date: Sep 2003
Location: The extremely Royal borough of Kensington, London
Posts: 778
|
|
select t.table_name, t.tablespace_name, c.column_name, c.data_type
from dba_tables t
INNER JOIN
dba_tab_columns c ON
t.table_name = c.table_name
where t.table_name IN
(select table_name
from dba_tab_columns
where column_name IN ('STREET', 'CITY', 'STATE', 'ZIP')
group by table_name
having count(column_name) = 4);
__________________
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.
|
Last edited by r123456; 03-01-04 at 05:02.
|
03-01-04, 02:22
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
|
|
ss_luna, you didn't join dba_tables and dba_tab_columns tables. And, I'd say you don't need dba_tables table at all to achieve what you've asked for.
Besides that, you can NOT use such WHERE clause as it won't return anything - you'd rather use IN than "column_name = value".
r123456, I tried it too. My query would be
PHP Code:
select c.table_name, c.column_name, c.data_type
from dba_tab_columns c
where c.column_name in ('STREET', 'CITY', 'STATE', 'ZIP')
order by c.table_name, c.column_name;
This query seems to be less complicated than yours. Why did you use INNER JOIN? Actually, I wouldn't ask if your query returned anything. But it didn't ... Now, I don't know why. (I runned both of them on Oracle 9.)
|
Last edited by Littlefoot; 03-01-04 at 02:25.
|
03-01-04, 04:57
|
|
Registered User
|
|
Join Date: Sep 2003
Location: The extremely Royal borough of Kensington, London
Posts: 778
|
|
TableA
Street, City, State, Zip
TableB
Street, City, State
The query that you supplied would return both TableA and TableB, however the solution I provided will only return TableA.
The inner join provides the capability to retrieve additional information.
__________________
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.
|
Last edited by r123456; 03-01-04 at 05:01.
|
03-01-04, 06:35
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
OK, r123456, but look at this (I modified column names to fit those in my database, to be able to collect some data as an output):
Your query:
PHP Code:
select t.table_name, t.tablespace_name, c.column_name, c.data_type
from dba_tables t
INNER JOIN
dba_tab_columns c ON
t.table_name = c.table_name
where t.table_name IN
(select table_name
from dba_tab_columns
where column_name IN ('OMM_ID', 'POOL_ID', 'OCI_ID', 'RB')
group by table_name
having count(column_name) = 4);
0 rows selected
My query:
PHP Code:
select c.table_name, c.column_name, c.data_type
from dba_tab_columns c
where c.column_name in ('OMM_ID', 'POOL_ID', 'OCI_ID', 'RB')
order by c.table_name, c.column_name;
TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------ ------------------
GOD_POTR_BCK OMM_ID NUMBER
POOL_KONTR_LISTA OMM_ID NUMBER
POOL_KONTR_LISTA POOL_ID NUMBER
STAVKE_AKO OMM_ID NUMBER
...
39 rows selected
So, the query you provided (in my database) returns nothing; one would hope for Table A's data.
And I still don't understand why INNER JOIN in this case? Does your query really returns something when you run it?
|
|
03-01-04, 20:08
|
|
Registered User
|
|
Join Date: Sep 2003
Location: The extremely Royal borough of Kensington, London
Posts: 778
|
|
The solution I provided, will return tables that contain only the complete set of columns specified.
Code:
SQL> desc tablea;
Name
---------------------
STREET
CITY
STATE
ZIP
SQL> desc tableb;
Name
---------------------
STREET
CITY
STATE
SQL> select t.table_name, t.tablespace_name, c.column_name, c.data_type
2 from user_tables t
3 INNER JOIN
4 user_tab_columns c ON
5 t.table_name = c.table_name
6 where t.table_name IN
7 (select table_name
8 from user_tab_columns
9 where column_name IN ('STREET', 'CITY', 'STATE', 'ZIP') AND
10 table_name IN ('TABLEA', 'TABLEB')
11 group by table_name
12 having count(column_name) = 4);
TABLE_NAME TABLESPACE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------ ------------------------------ -------
TABLEA SYSTEM STREET NUMBER
TABLEA SYSTEM CITY NUMBER
TABLEA SYSTEM STATE NUMBER
TABLEA SYSTEM ZIP NUMBER
SQL> select c.table_name, c.column_name, c.data_type
2 from user_tab_columns c
3 where c.column_name in ('STREET', 'CITY', 'STATE', 'ZIP') AND
4 c.table_name IN ('TABLEA', 'TABLEB')
5 order by c.table_name, c.column_name;
TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------ ---------
TABLEA CITY NUMBER
TABLEA STATE NUMBER
TABLEA STREET NUMBER
TABLEA ZIP NUMBER
TABLEB CITY NUMBER
TABLEB STATE NUMBER
TABLEB STREET NUMBER
__________________
Bessie Braddock: Winston, you are drunk!
Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.
|
Last edited by r123456; 03-01-04 at 20:23.
|
03-02-04, 00:34
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
Now! I understand what you wanted to say in the first place! Thank you for explaining it to me, r123456. Sorry for being such a pain in the _ _ _ 
I hope there's still Summer in Brisbane (as parts of my country suffer under 4 meters (12 feet) of snow).
Looking forward to learn something new from you soon,
Littlefoot
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
viewing multiple columns
