CODEWITHSUNDEEP
sqloracle-database
ajm
ajm

Reputation: 13223

How to reconcile common rows in two tables columnwise in oracle?

I have two tables in a database. The tables are called bi_employee and hr_employee. These two tables have few similar columns and then other extra columns specific to each table.

I need to reconcile the data between these two tables based on some user defined columns.

Lets say the common columns are id(pk), emp_code, region, country, title, division etc.

Now when I reconcile these two tables, I would want to see the rows which are there in both the tables but only differ in some columns.

e.g. emp_code 1000 is prsent in both the tables but in hr_employee his title is jr. developer but in bi_employee his title is sr.developer.

I do not want the records which are in one table but not in another table.

I only need to reconcile the rows which are present in both the tables but on columnwise which will be selected by the user.

User may chose to reconcile based on title or region or country or all of them.

Please help.

EDIT 1:

This is what I have done so far, with the following query I could get all the records which are there in both the tables. Now I just need to compare their columns to see if there are any mismatches. 

SELECT emp_code FROM bi_employee INTERSECT SELECT emp_code FROM hr_employee

Upvotes: 1

Views: 5472

Answers (2)

Kirill Leontev
Kirill Leontev

Reputation: 10941

You might try this.

select hr.<list of columns to reconcile from hr table> 
  from bi_employee bi join hr_employee hr on hr.emp_code = bi.empcode
minus
select bi.<list of columns to reconcile from bi table> 
  from bi_employee bi join hr_employee hr on hr.emp_code = bi.empcode

Upvotes: 0

MatBailie
MatBailie

Reputation: 86798

From what I understand, there is only one column that relates the recodes in each table; emp_code. Then you want to show records where the emp_code is the same in each talbe, but other field(s) are different.

You can do that with a simple join and filter in a WHERE clause...

SELECT
  *
FROM
  bi_employee
INNER JOIN
  hr_employee
    ON bi_employee.emp_code = hr_employee.emp_code
WHERE
     (bi_employee.title  <> hr_employee.title)
  OR (bi_employee.region <> hr_employee.region)
  etc, etc

(If any fields are nullable, you'll need to account for that with something like ISNULL(bi.x, '') <> ISNULL(hr.x, '')).

Upvotes: 3

Related Questions