Re: outer join syntax...

Volker Hetzer wrote:

> Hi!
> I'm comparing two tables in order to find out whether the column names differ.
> But, somehow I can't get oracle to join all_tab_columns the proper way.
> I'm using 9.2.0.4 on Linux.
>
> Here's the testcase:
> col TABLE_NAME format a15;
> col COLUMN_NAME format a15;
> drop table table1;
> create table table1 (column1 number, column2 varchar(32), column3 number);
> drop table table2;
> create table table2 (column1 number, column3 number);
>
> --Where is the result row with column2?
> select
> t1.table_name,t1.column_name, t2.table_name,t2.column_name
> from
> all_tab_columns t1 FULL JOIN all_tab_columns t2 on (t1.column_name = t2.column_name)
> where t1.owner = 'SPIELPLATZ'
> and t2.owner = 'SPIELPLATZ'
> and t1.table_name = 'TABLE1'
> and t2.table_name = 'TABLE2'
> order by t1.table_name;
>
> --Why does t2 not have a column_name column?
> select
> t1.table_name,t1.column_name, t2.table_name,t2.column_name
> from
> all_tab_columns t1 FULL JOIN all_tab_columns t2 using (column_name)
> where t1.owner = 'SPIELPLATZ'
> and t2.owner = 'SPIELPLATZ'
> and t1.table_name = 'TABLE1'
> and t2.table_name = 'TABLE2'
> order by t1.table_name;
>
> exit;
>
> Also, as you can see when executing the script, the second select fails because t2 doesn't seem
> to have a column column_name. Why?
>
>
> Lots of Greetings and thanks!
> Volker

You seem to have a solution so I'll not solve the problem. But you have a second problem you've yet to discover.

SQL> DESC user_tab_columns

table_names are a VARCHAR2(30)
column_names are a VARCHAR2(30)

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)