Teradata Historisation Part 1/5: Situation and Setup

0
240

teradata historisation

Imagine that the time has come to renovate your physical data model. Flawed or obsolete Teradata historisation design decisions need to be corrected. You are asked to merge tables of the same entity that hold different attributes in separation. We do not question why there are separate tables of the same entity at this point. Instead, we offer you a systematic approach for transforming any starting constellation to any end constellation under the constraint of any possible historisation setting on either side. By simple we mean that there is only one time layer added but no additional validity layer.

Before we introduce the possible combinations of what our merge task looks like, let us define what historisation levels we could have in the tables that are input to the merge task:

0 = no historisation. Table contents are entered as they come. There is no created attribute to reveal when the data came and how long they were valid.

1 = simple historisation. The table content is time-layered to indicate the time span over which one row held or holds the relevant information. This time layer is structured either by a CDD column so that per primary key and day loaded there is one row stored or by two columns RSD and RED so that one row per primary key can cover a variable time span and a new one is created only if non-key attributes have changed.

2 = double historisation. Additional to the simple historisation, a validity layer is introduced via VSD and VED attributes. This allows for retroactive recreation of change histories.

Here are the parameters you have to set to determine which design case your task is so as to pick the appropriate code pattern:

Historisation level of input table A.

Historisation level of input table B.

Historisation level of the output table.

As a meta-decision, we standardize the level 1 historisation to using RSD and RED even if we have it implemented in the form of a CDD. In the process of merging, we can select CDD one time as RSD and one time as RED if we face a snapshot history in one table instead.

We present two fictional tables and one version per historisation to walk through every non-trivial example.Here are the versions of the first table, Table_A

CREATE SET TABLE TABLE_A0 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

ENT_ID BIGINT NOT NULL,

C_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

D_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

E_VALUE INTEGER )

PRIMARY INDEX (ENT_ID );

CREATE SET TABLE TABLE_A1 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

ENT_ID BIGINT NOT NULL,

CDD DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,

C_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

D_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

E_VALUE INTEGER )

PRIMARY INDEX (ENT_ID )

PARTITION BY RANGE_N(CDD BETWEEN ‘2011-01-01’ AND DATE ‘2017-12-31’ EACH INTERVAL ‘1’ DAY , NO RANGE);

CREATE SET TABLE TABLE_A2 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

ENT_ID BIGINT NOT NULL,

CDD DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,

C_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

D_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

E_VALUE INTEGER ,

VSD TIMESTAMP(6) FORMAT ‘YYYY-MM-DDHH:MI:SS’ NOT NULL,

VED TIMESTAMP(6) FORMAT ‘YYYY-MM-DDHH:MI:SS’ NOT NULL )

PRIMARY INDEX (ENT_ID )

PARTITION BY RANGE_N(CDD BETWEEN ‘2011-01-01’ AND DATE ‘2017-12-31’ EACH INTERVAL ‘1’ DAY , NO RANGE);

 

Here are the versions of the second table, Table_B:

CREATE SET TABLE TABLE_B0 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

ENT_ID BIGINT NOT NULL,

F_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

G_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

H_VALUE INTEGER )

PRIMARY INDEX (ENT_ID );

CREATE SET TABLE TABLE_B1 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

ENT_ID BIGINT NOT NULL,

RSD DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,

RED DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,

F_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

G_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

H_VALUE INTEGER )

PRIMARY INDEX (ENT_ID );

CREATE SET TABLE TABLE_B2 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

ENT_ID BIGINT NOT NULL,

RSD DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,

VSD TIMESTAMP(6) FORMAT ‘YYYY-MM-DDHH:MI:SS’ NOT NULL,

RED DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,

VED TIMESTAMP(6) FORMAT ‘YYYY-MM-DDHH:MI:SS’ NOT NULL,

F_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

G_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

H_VALUE INTEGER )

PRIMARY INDEX (ENT_ID );

 

Our target table can have one of the following three forms:

CREATE SET TABLE TARGET_TABLE0 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

ENT_ID BIGINT NOT NULL,

C_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

D_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

E_VALUE INTEGER ,

F_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

G_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

H_VALUE INTEGER )

PRIMARY INDEX (ENT_ID );

CREATE SET TABLE TARGET_TABLE1 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

ENT_ID BIGINT NOT NULL,

RSD DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,

RED DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,

C_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

D_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

E_VALUE INTEGER ,

F_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

G_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

H_VALUE INTEGER )

PRIMARY INDEX (ENT_ID );

CREATE SET TABLE TARGET_TABLE2 ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT

(

ENT_ID BIGINT NOT NULL,

RSD DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,

VSD TIMESTAMP(6) FORMAT ‘YYYY-MM-DDHH:MI:SS’ NOT NULL,

RED DATE FORMAT ‘YYYY-MM-DD’ NOT NULL,

VED TIMESTAMP(6) FORMAT ‘YYYY-MM-DDHH:MI:SS’ NOT NULL,

C_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

D_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

E_VALUE INTEGER ,

F_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

G_LEVEL_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,

H_VALUE INTEGER )

PRIMARY INDEX (ENT_ID );

 

In order to show the effects of our case studies and to prove to ourselves that the code we provide actually works, we insert a small set of dummy records into the table versions of A and B.

The target table versions are empty by definition and only serve as an illustration of where we want to go with the data.

— fill the A Table versions

INSERT INTO TABLE_A0 (ENT_ID,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (1,’A’,’X’,100) ;

INSERT INTO TABLE_A0 (ENT_ID,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (2,’B’,’Y’,200) ;

INSERT INTO TABLE_A0 (ENT_ID,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,’B’,’Z’,444) ;

INSERT INTO TABLE_A0 (ENT_ID,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,’A’,’X’,55) ;

 

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (1,DATE’2014-01-01′,’A’,’X’,80) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (1,DATE’2014-01-02′,’A’,’X’,90) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (1,DATE’2014-01-03′,’A’,’X’,100) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (2,DATE’2014-01-02′,’B’,’Y’,190) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (2,DATE’2014-01-03′,’B’,’Y’,200) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-01′,’B’,’Y’,200) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-02′,’B’,’Y’,200) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-03′,’B’,’Y’,200) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-04′,’B’,’Y’,200) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-05′,’B’,’Y’,200) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-06′,’B’,’Y’,200) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-07′,’B’,’Y’,200) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-08′,’B’,’Y’,200) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-09′,’B’,’Y’,200) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-10′,’B’,’Y’,333) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-11′,’B’,’Y’, 333) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-12′,’B’,’Y’, 333) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-13′,’B’,’Y’, 333) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-14′,’B’,’Y’, 333) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-15′,’B’,’Y’, 333) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-16′,’B’,’Y’, 333) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-17′,’B’,’Y’, 333) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-18′,’B’,’Y’, 333) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-19′,’B’,’Y’, 333) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-20′,’B’,’Y’,444) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-21′,’B’,’Y’,444) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-22′,’B’,’Y’,444) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-05′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-06′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-07′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-08′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-09′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-10′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-11′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-12′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-13′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-14′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-15′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-16′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-17′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-18′,’A’,’X’,0) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-19′,’A’,’X’,55) ;

INSERT INTO TABLE_A1 (ENT_ID,CDD,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-20′,’A’,’X’,55) ;

 

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (1,DATE’2014-01-01′,TIMESTAMP ‘2014-01-01 07:33:59.000000′, DATE’2014-01-01’, TIMESTAMP ‘2014-01-02 07:59:58.000000′ ,’A’,’X’,0) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (1,DATE’2014-01-02′,TIMESTAMP ‘2014-01-01 07:33:59.000000′,DATE’2014-01-02’,TIMESTAMP ‘2014-01-02 07:59:58.000000′ ,’A’,’X’,0) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (1,DATE’2014-01-01′ ,TIMESTAMP ‘2014-01-02 07:59:59.000000′,DATE’2014-01-01’,TIMESTAMP ‘3000-12-31 23:59:59.000000′ ,’A’,’X’,80) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (1,DATE’2014-01-02′, TIMESTAMP ‘2014-01-02 07:59:59.000000′,DATE’2014-01-02’,TIMESTAMP ‘3000-12-31 23:59:59.000000’, ‘A’,’X’,90) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (1,DATE’2014-01-03′,TIMESTAMP’2014-01-02 07:59:59.000000′, DATE’3000-12-31′, TIMESTAMP ‘3000-12-31 23:59:59.000000’, ‘A’,’X’,100) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (2,DATE’2014-01-02′,TIMESTAMP ‘2014-01-02 07:59:59.000000′,DATE’2014-01-02’,TIMESTAMP ‘3000-12-31 23:59:59.000000’, ‘B’,’Y’,190) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (2,DATE’2014-01-03′, TIMESTAMP ‘2014-01-03 08:16:16.000000′,DATE’3000-12-31’,TIMESTAMP ‘3000-12-31 23:59:59.000000’, ‘B’,’Y’,200 ) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-01′,TIMESTAMP ‘2014-01-01 07:35:59.000000′,DATE’2014-01-09’,TIMESTAMP ‘3000-12-31 23:59:59.000000′ ,’B’,’Y’,200) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-10′,TIMESTAMP ‘2014-01-10 08:21:52.000000′,DATE’2014-01-19’,TIMESTAMP ‘3000-12-31 23:59:59.000000’, ‘B’,’Y’,333) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (3,DATE’2014-01-20′,TIMESTAMP ‘2014-01-20 08:41:13.000000′,DATE’3000-12-31’,TIMESTAMP ‘3000-12-31 23:59:59.000000′ ,’B’,’Z’,444) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-05′,TIMESTAMP ‘2014-01-05 09:09:09.000000′,DATE’2014-01-18’ ,TIMESTAMP ‘3000-12-31 23:59:59.000000′ ,’A’,’X’,0 ) ;

INSERT INTO TABLE_A2 (ENT_ID,RSD,VSD,RED,VED,C_STATUS,D_LEVEL_CD,E_VALUE ) VALUES (4,DATE’2014-01-19′, TIMESTAMP ‘2014-01-19 03:39:40.000000′,DATE’3000-12-31’, TIMESTAMP ‘3000-12-31 23:59:59.000000′,’A’,’X’,55) ;

— fill the B Table versions

INSERT INTO TABLE_B0 (ENT_ID,F_STATUS,G_LEVEL_CD,H_VALUE ) VALUES (1,’F’,’Q’,5) ;

INSERT INTO TABLE_B0 (ENT_ID,F_STATUS,G_LEVEL_CD,H_VALUE ) VALUES (2,’J’,’V’,20) ;

INSERT INTO TABLE_B0 (ENT_ID,F_STATUS,G_LEVEL_CD,H_VALUE ) VALUES (4,’L’,’V’,20) ;

 

INSERT INTO TABLE_B1 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE ) VALUES (1,DATE’2014-01-01′,DATE’2014-01-02′,’F’,’Q’,0) ;

INSERT INTO TABLE_B1 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE ) VALUES (1,DATE’2014-01-03′,DATE’3000-12-31′,’F’,’Q’,5) ;

INSERT INTO TABLE_B1 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE ) VALUES (2,DATE’2014-01-02′,DATE’3000-12-31′,’J’,’V’,20) ;

INSERT INTO TABLE_B1 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE ) VALUES (4,DATE’2014-01-01′,DATE’2014-01-17′,’J’,’V’,20) ;

INSERT INTO TABLE_B1 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE ) VALUES (4,DATE’2014-01-18′,DATE’2014-01-21′,’K’,’V’,20) ;

INSERT INTO TABLE_B1 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE ) VALUES (4,DATE’2014-01-22′,DATE’3000-12-31′,’L’,’V’,20) ;

 

INSERT INTO TABLE_B2 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE,VSD,VED )

VALUES (1,DATE’2014-01-01′,DATE’3000-12-31′,’F’,’Q’,0,TIMESTAMP ‘2014-01-01 07:33:59.000000’,TIMESTAMP ‘2014-01-02 07:59:58.000000’) ;

INSERT INTO TABLE_B2 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE,VSD,VED )

VALUES (1,DATE’2014-01-01′,DATE’2014-01-02′,’F’,’Q’,0,TIMESTAMP ‘2014-01-02 07:59:59.000000’,TIMESTAMP ‘3000-12-31 23:59:59.000000’) ;

INSERT INTO TABLE_B2 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE,VSD,VED )

VALUES (1,DATE’2014-01-03′,DATE’3000-12-31′,’F’,’Q’,5,TIMESTAMP’2014-01-03 08:16:16.000000′,TIMESTAMP ‘3000-12-31 23:59:59.000000’) ;

INSERT INTO TABLE_B2 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE,VSD,VED )

VALUES (2,DATE’2014-01-02′,DATE’3000-12-31′,’J’,’V’,20,TIMESTAMP ‘2014-01-02 07:59:59.000000’,TIMESTAMP ‘3000-12-31 23:59:59.000000’ ) ;

INSERT INTO TABLE_B2 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE,VSD,VED )

VALUES (4,DATE’2014-01-01′,DATE’2014-01-17′,’J’,’V’,20,TIMESTAMP ‘2014-01-01 07:34:01.000000’,TIMESTAMP ‘2014-01-18 08:08:33.000000’ ) ;

INSERT INTO TABLE_B2 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE,VSD,VED )

VALUES (4,DATE’2014-01-18′,DATE’2014-01-21′,’K’,’V’,20,TIMESTAMP ‘2014-01-18 08:08:34.00000’,TIMESTAMP ‘2014-01-22 07:48:11.00000’ ) ;

INSERT INTO TABLE_B2 (ENT_ID,RSD,RED,F_STATUS,G_LEVEL_CD,H_VALUE,VSD,VED )

VALUES (4,DATE’2014-01-22′, DATE’3000-12-31′,’L’,’V’,20,TIMESTAMP ‘2014-01-22 07:48:12.000000’,TIMESTAMP ‘3000-12-31 23:59:59.000000’ ) ;

 

Note that the two tables are not synchronous with respect to entities or historisation events. This is a wanton disorder that forces us to foresee such a situation and treat it in a meaningful way in the code examples to come.

In the coming parts of this series we will systematically walk through each of the aforementioned cases, interpret it and provide SQL solutions whenever there is more to do than just a simple merge of two tables.

Cases will be described with the pattern A-B->T, where A, B, and T can be any of the historisation levels 0 to 2.

Our Reader Score
[Total: 4    Average: 4/5]
Teradata Historisation Part 1/5: Situation and Setup written by Paul Timar on April 22, 2014 average rating 4/5 - 4 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here