On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Thursday, May 29, 2008

Discrepancies in Table Data

A way to find discrepancies in data between two tables is shown below. I first ran the top three lines to create a similar table to 'EMP', but with differences in the values.
Create Table Emp2
As
Select *
From Emp;
Update Emp
Set Mgr = 7698
Where EmpNo = 7369;
Update Emp2
Set Job = 'DBA'
Where EmpNo = 7566;
The 'WITH' allows the tables to be read only once to gather the data needed for evaluation.
Break on EmpNo Skip 1  NoDup

With Table1 as (
Select EmpNo, Job, Mgr
From Emp
Where DeptNo = 20
),
Table2 As (
Select EmpNo, Job, Mgr
From Emp2
Where DeptNo = 20
)
---------------------------------------------------
Select *
From
(
----------------------------------
-- add user/table to differences
----------------------------------
(Select 'Emp' Tbl, In1Not2.*
From
-------------------------
-- Show records in 1 not 2
-------------------------
(Select * From Table1
Minus
Select * From Table2
)In1Not2
)
Union
-----------------------------
-- add user/table to differences
-----------------------------
(Select 'Emp2' Tbl, In2Not1.*
From
-------------------------
-- Show records in 1 not 2
-------------------------
(Select * From Table2
Minus
Select * From Table1
)In2Not1
)
)
Order By EmpNo, Tbl;

TBL EMPNO JOB MGR
---- ---------- --------- ----------
Emp 7369 CLERK 7698
Emp2 CLERK 7902

Emp 7566 MANAGER 7839
Emp2 DBA 7839

No comments:

Post a Comment