Have you encountered a poorly designed physical data model where object columns are distributed randomly across tables, and you wish to unify them in their rightful place?
Merging non-historical tables is simple, but the process becomes more complex when historization is applied to at least one of the tables.
Change history tables are typically complex. However, you need not worry; we have a solution to simplify your task. We will guide you through all the steps using a small example.
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;Please note the language used in this text:
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 and end dates.
