SQL table macros 13: compare with primary key

I just updated my COMPARE_ROWSETS function to handle an optional primary or unique key.

If that optional parameter is left out, the function uses the venerable GROUP BY method that handles duplicate rows.

If key columns are indicated, then the “Z#_OP” column can contain updates :

• U: Updated data (as present in p_new_table)
• O: Old data (as present in p_old_table)

There is no need for a “Z#_CNT” column since there can be no duplicate rows.

Here is an example of use with a key column :

drop table emp2 purge;
 
create table emp2 as select * from emp;
 
update emp2 set ename = ename||' KONG' where rownum = 1;
 
insert into emp2 
select EMPNO+1000, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
from emp2 where rownum <= 4 and job != 'PRESIDENT';

select * from compare_rowsets(
  emp, emp2,
  p_key_cols => columns(empno),
  p_exclude_cols => columns(comm)
)
order by empno, z#_op;

Z#_OP	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	DEPTNO
O	7839	KING	PRESIDENT		1981-11-17 00:00:00	5000	10
U	7839	KING KONG	PRESIDENT		1981-11-17 00:00:00	5000	10
I	8566	JONES	MANAGER	7839	1981-04-02 00:00:00	2975	20
I	8698	BLAKE	MANAGER	7839	1981-05-01 00:00:00	2850	30
I	8782	CLARK	MANAGER	7839	1981-06-09 00:00:00	2450	10
I	8788	SCOTT	ANALYST	7566	1987-04-19 00:00:00	3000	20

 

Posts on SQL table macros

• SQL table macros 1: a moving target?
• SQL table macros 2: “polymorphic” is not “dynamic”
• SQL table macros 3: parameter strangeness
• SQL table macros 4: distribute into equal parts
• SQL table macros 5: dynamic but dangerous!
• SQL table macros 6: SQM_UTIL package
• SQL table macros 7: Select excluding
• SQL table macros 8: Print table
• SQL table macros 9: just pivot!
• SQL table macros 10: JUST_PIVOT examples
• SQL table macros 11: JUST_PIVOT for 21c (and 19c)
• SQL table macros 12: compare tables or queries
• SQL table macros 13: compare with primary key

Advertisement