Merging two change history tables

0
302

 

Have you ever been in the situation of facing a bad physical data model where the columns of an object are scattered randomly across tables, and you would like to bring them together where they belong to?

While merging is a straightforward operation for not-historical tables, it demands more than just the execution of a merge SQL statement once historization is introduced to at least one of the tables.

Change history tables are at the high end. But dear reader, rejoice! You are relieved from designing this demanding task from scratch – here is the solution taking a small dummy example to walk through all steps:

CREATE MULTISET VOLATILE TABLE <JOIN_TYPE_LOOKUP> (
JOIN_TYPE CHAR(4)
) ON COMMIT PRESERVE ROWS;

INSERT INTO <JOIN_TYPE_LOOKUP> VALUES (‘before’);
INSERT INTO <JOIN_TYPE_LOOKUP> VALUES (‘between’);
INSERT INTO <JOIN_TYPE_LOOKUP> VALUES (‘after’);

— Two historized test tables

CREATE MULTISET VOLATILE TABLE <TABLE_A> (
ID_A INTEGER,
RSD DATE,
RED DATE
) ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE <TABLE_B> (
ID_B INTEGER,
RSD DATE,
RED DATE
) ON COMMIT PRESERVE ROWS;

INSERT INTO <TABLE_A> VALUES (1, ‘2014-01-01’, ‘2014-01-31’);
INSERT INTO <TABLE_B> VALUES (1, ‘2014-01-31’, ‘9999-12-31’);

SELECT
——— Block for columns from table A ———
CASE
WHEN JOIN_TYPE = ‘before’ AND a.RSD < b.RSD THEN ID_A
WHEN JOIN_TYPE = ‘before’ AND b.RSD < a.RSD THEN NULL
WHEN JOIN_TYPE = ‘between’ THEN ID_A
WHEN JOIN_TYPE = ‘after’ AND a.RED > b.RED THEN ID_A
WHEN JOIN_TYPE = ‘after’ AND b.RED > a.RED THEN NULL
END AS ID_A,

——— Block for columns from table B ———
CASE
WHEN JOIN_TYPE = ‘before’ AND a.RSD < b.RSD THEN NULL
WHEN JOIN_TYPE = ‘before’ AND b.RSD < a.RSD THEN ID_B
WHEN JOIN_TYPE = ‘between’ THEN ID_B
WHEN JOIN_TYPE = ‘after’ AND a.RED > b.RED THEN NULL
WHEN JOIN_TYPE = ‘after’ AND b.RED > a.RED THEN ID_B
END AS ID_B,

——– Identify new RSD/RED values ——–
CASE
WHEN JOIN_TYPE = ‘before’ AND a.RSD < b.RSD THEN a.RSD
WHEN JOIN_TYPE = ‘before’ AND b.RSD < a.RSD THEN b.RSD
WHEN JOIN_TYPE = ‘between’ AND a.RSD < b.RSD THEN b.RSD
WHEN JOIN_TYPE = ‘between’ AND b.RSD <= a.RSD THEN a.RSD
WHEN JOIN_TYPE = ‘after’ AND a.RED < b.RED THEN a.RED + 1
WHEN JOIN_TYPE = ‘after’ AND b.RED < a.RED THEN b.RED + 1
WHEN JOIN_TYPE = ‘between’ THEN (coalesce(a.RSD, b.RSD))
END AS new_RSD,
CASE
WHEN JOIN_TYPE = ‘before’ AND a.RSD < b.RSD THEN b.RSD – 1
WHEN JOIN_TYPE = ‘before’ AND b.RSD < a.RSD THEN a.RSD – 1
WHEN JOIN_TYPE = ‘between’ AND a.RED < b.RED THEN a.RED
WHEN JOIN_TYPE = ‘between’ AND b.RED <= a.RED THEN b.RED
WHEN JOIN_TYPE = ‘after’ AND a.RED < b.RED THEN b.RED
WHEN JOIN_TYPE = ‘after’ AND b.RED < a.RED THEN a.RED
WHEN JOIN_TYPE = ‘between’ THEN (coalesce(a.RED, b.RED))
END AS new_RED
—————————————————
from <TABLE_A> a
FULL OUTER JOIN
<TABLE_B> b
ON (a.RSD, a.RED+1) OVERLAPS (b.RSD, b.RED+1)
— further join conditions
CROSS JOIN <JOIN_TYPE_LOOKUP> h
WHERE
new_RSD IS NOT NULL
AND new_RED IS NOT NULL;

A note on the terminology used:

I assume that you are familiar with the concept of volatile tables and the basic concepts in historisation. RSD and RED stand for the record start date and the record end date.

Our Reader Score
[Total: 4    Average: 3.3/5]
Merging two change history tables written by Roland Wenzlofsky on March 24, 2014 average rating 3.3/5 - 4 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here