fbpx

 

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.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>