Friday, 3 July 2015

Identifying Change Records and Voids in a partitioned table

Let's assume I am comparing the two partitions.I want to capture changes and voids while comparing the consecutive partitions.

In this example,I have taken 2 consecutive partitions as two tables.

1)Employee 1 and 2 are on Jan1st,but voided on Jan2nd.
2)Employee 3 and 4 are on both the days but does not have any change in the record.
3)Employee 5 has it's first record on Jan1st and change on Jan2nd.
4)Employee 6 has updates on Jan1st and Jan2nd.
5)Employee 7 has update sometime back but no change in this consecutive days

Now I need to fetch the records which are changed and voided.

SQL> SELECT * FROM PAR1;







SQL> SELECT * FROM PAR2;





OUTPUT:

SELECT PAR1.*
FROM
PAR2 RIGHT OUTER JOIN PAR1
ON PAR1.EMPNO=PAR2.EMPNO
WHERE COALESCE(PAR1.ACTN_IND,'$')<>COALESCE(PAR2.ACTN_IND,'$')
OR (PAR1.ACTN_IND='C' AND ORA_HASH(PAR1.EMPNO,PAR1.SAL)<>ORA_HASH(PAR2.EMPNO,PAR2.SAL))



No comments:

Post a Comment