Compare columns in two tables and list out column names which are different. for ex:- create table t1(c1 number(2), c2 varchar2(10));
create table t2(c1 number(2), c2 varchar2(10)); insert into t1 values(1,'a'); insert into t2 values(1,'b'); result should be column c2 is different. Please suggest me how to do this one?
there is so much missing here...
I'll have to assume that C1 is the primary key of both tables.
I'll have to assume you expect rows to be in C1 and not in C2 sometimes - and vice versa.
I'll have to assume that in general there will be a c3, c4 and so on.
I'll have to assume that if the rows are exactly the same - you don't want to see them at all.
I'll have to assume that if I answered your question as it stands without making lots of other assumptions - you'd be coming back over and over and over and saying "yeah, but what about...."
Asking questions - being very very specific about your needs - that is the most crucial attribute a person in our industry MUST have (well, if they want to be successful that is)...
So, using my assumptions and making your 'test case' (it isn't a test case yet, it is lacking) a better one:
ops$tkyte%ORA10GR2> create table t1(c1 number(2) primary key, c2 varchar2(10), c3 varchar2(10));
Table created.
ops$tkyte%ORA10GR2> create table t2(c1 number(2) primary key, c2 varchar2(10), c3 varchar2(10));
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values(1,'a','c');
1 row created.
ops$tkyte%ORA10GR2> insert into t2 values(1,'b','c');
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values(2,'a','b');
1 row created.
ops$tkyte%ORA10GR2> insert into t2 values(2,'a','c');
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values(3,'a','b');
1 row created.
ops$tkyte%ORA10GR2> insert into t2 values(3,'a','b');
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values(4,'a','b');
1 row created.
ops$tkyte%ORA10GR2> insert into t2 values(4,'x','y');
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values(5,'a','b');
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t2 values(6,'a','b');
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select case when t1.c1 is null then 'missing in t1'
2 when t2.c1 is null then 'missing in t2'
3 end m1,
4 decode( t1.c2, t2.c2, '', 'c2' ) c2_flag,
5 decode( t1.c3, t2.c3, '', 'c3' ) c3_flag,
6 coalesce(t1.c1,t2.c1) pk,
7 t1.c2, t2.c2,
8 t1.c3, t2.c3
9 from t1 full outer join t2 on (t1.c1 = t2.c1)
10 where decode( t1.c2, t2.c2, '', 'c2' ) is not null
11 or decode( t1.c3, t2.c3, '', 'c3' ) is not null
12 order by pk
13 /
M1 C2 C3 PK C2 C2 C3 C3
------------- -- -- ---------- ---------- ---------- ---------- ----------
c2 1 a b c c
c3 2 a a b c
c2 c3 4 a x b y
missing in t2 c2 c3 5 a b
missing in t1 c2 c3 6 a b