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.

Saturday, June 28, 2008

Comparing Data From Two Similar Queries

At one point my company was loading older data into Oracle tables. There was a constant stream of requests to compare two subsets of data from different queries. After awhile, I coded this master query which would run two other queries, and compare the data between the them.

In this example, I've created a second EMP table and made a couple of changes to the data.
Create Table Emp2
As
Select *
From Emp;
Update Emp
Set Sal = 950
Where EmpNo = 7369;
Update Emp2
Set Job = 'DBA'
Where EmpNo = 7566;
Insert Into Emp2 Values
(8484,'MICKEY','CEO',NULL,'01-JUL-80',9999,5000,10);
The names of these two queries need to be set equal to 'Code1' and 'Code2' in the Defines.
Empcd1:
Select Empno, Ename, Job, Hiredate, Sal, Deptno
From Emp
Where To_Char(Hiredate,'yyyy') Between 1980 And 1981;

Empcd2:
Select Empno, Ename, Job, Hiredate, Sal, Deptno
From Emp2
Where To_Char(Hiredate,'yyyy') Between 1980 And 1981;

The field that will be used to match the records needs to be set equal to Sort_Fld in the Define. In this example, I was matching on 'Empno'.

This query reads the data into two temporary tables shown in the 'With' clause. The two are compared with separate 'minus' queries and then unioned together to get a full set.
--+----------------------------+--
--| Fill In These Three Fields |
--+----------------------------+--
Define Sort_Fld = Empno
Define Code1 = Empcd1
Define Code2 = Empcd2

Break On &Sort_Fld Dup Skip 1
Column Srt Noprint
Column Tblx Noprint

With
Table1 As
(
@&Code1
),
Table2 As
(
@&Code2
)
Select vt1.&Sort_Fld Srt,
vt1.Tbl Tblx,
vt1.*
From
(
----------------------------------
-- Add User To Differences
----------------------------------
(Select 't1' Tbl, In1not2.*
From
-------------------------
-- Show Records In 1 Not 2
-------------------------
(Select * From Table1
Minus
Select * From Table2
)In1not2
)
Union
-----------------------------
-- Add User To Differences
-----------------------------
(Select 't2' Tbl, In2not1.*
From
-------------------------
-- Show Records In 1 Not 2
-------------------------
(Select * From Table2
Minus
Select * From Table1
)In2not1
)
)Vt1
Order By Vt1.&Sort_Fld, Vt1.Tbl;
See Oracle documentation for further detail:
Set Operators: Union and Minus
With Clause - Subquery Factoring

No comments:

Post a Comment