Skip to Main Content
  • Questions
  • Reporting column data difference between two identical tables with a composite key

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Pavan.

Asked: January 22, 2017 - 12:50 pm UTC

Last updated: January 24, 2017 - 9:27 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi ,

I need to report differences between two tables column values ,please note both table structures being identical and have a composite primary key with more than one column.
I have gone through the few earlier post which discussed the same with a primary key with just one column in it using decode on column names and on the two tables which we wanted to compare .
Can any one please help me with a simple example on how we can do column data comparison with a composite key in place on the tables.

Thank you in advance..
Pavan

and Connor said...

First we can do a fuller outer join to bring the tables together

with joined_Result as 
(
  select * 
  from table1 t1 
    full outer join 
       table2 t2
  on ( t1.composite_key_col1 = t2.composite_key_col1 
   and t1.composite_key_col2 = t2.composite_key_col2 
   and t1.composite_key_col3 = t2.composite_key_col3 
)


and then examine that for differences


with joined_Result as 
(
  select * 
  from table1 t1 
    full outer join 
       table2 t2
  on ( t1.composite_key_col1 = t2.composite_key_col1 
   and t1.composite_key_col2 = t2.composite_key_col2 
   and t1.composite_key_col3 = t2.composite_key_col3 
)
select * 
from joined_result
where decode(t1.col1,t2.col1,1,0) = 0
 or decode(t1.col2,t2.col2,1,0) = 0
 or decode(t1.col3,t2.col3,1,0) = 0
..


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 24, 2017 - 3:43 am UTC

Connor thank you very much and it helped me in understanding the way we should be doing it.

Much appreciate your time .


Thanks,
Pavan
Connor McDonald
January 24, 2017 - 9:27 am UTC

glad we could help