Teradata Historisation Part 3/5: Upleveling cases

0
400

teradata historisation

Part 3 of this series discusses table merge cases that should simultaneously lead to a net increase in the historisation level. Not only are we asked to merge, we also need to adapt the result to the introduction of one more level of historisation than the source tables have had up to now.

Historisation upleveling can be any of the following cases: (0-0-1, 0-0-2, 0-1-2, 0-2-2, 1-1-2).

 

Case 0-0->1 is again the most trivial case from a technical point of view. Nevertheless, it requires some clarification. The only historisation we can introduce to the resulting table is a present and future one. We have nothing else than the most recent load result in the A and B tables and can therefore do nothing more than provide the current date or some other fixed date coming from business considerations as RSD or CDD.

 

Case 0-0->2 leads to the initialisation of two layers at once. We take the date and timestamp of the time we merge and write them into the first and second layer of historisation of the target table.

 

Case 0-1->2 We depart from case 0-1->1 in technical terms. All we have to add is the introduction of the second historisation layer from the historised tables, which can only be in a proactive way as there are is no past validity.

Note that we take the first interpretation of case 0-1->1 as the foundation.

— CASE 0-1-> 2, based on Interpretation thar A Table shows alltime valid history

SELECT

COALESCE(B.ENT_ID, A.ENT_ID) AS ENT_ID, A. C_STATUS ,A.D_LEVEL_CD , A.E_VALUE, B.F_STATUS, B.G_LEVEL_CD, B.H_VALUE, COALESCE(B.RSD,CURRENT_DATE), COALESCE(B.RED, DATE’3000-12-31′),

CAST(CURRENT_TIMESTAMP AS TIMESTAMP(6) ) AS VSD, TIMESTAMP ‘3000-12-31 23:59:59.000000’ AS VED

FROM

TABLE_B1 B

FULL OUTER JOIN

TABLE_A0 A

ON B.ENT_ID = A.ENT_ID

;

 

Case 0-2->2 Just as with the previouscase, we have two options regarding how to deal with A. The difference now is to transport the entire content of table B to the target. Note the implicit decision in the second interpretation that devalidated records of B are not to be matched with the A table content.

— CASE 0-2-> 2 FIRST Interpretation: A Table shows alltime valid history

SELECT

COALESCE(B.ENT_ID, A.ENT_ID) AS ENT_ID, A. C_STATUS ,A.D_LEVEL_CD , A.E_VALUE, B.F_STATUS, B.G_LEVEL_CD, B.H_VALUE,

COALESCE(B.RSD,CURRENT_DATE),COALESCE(B.VSD, CAST(CURRENT_TIMESTAMP AS TIMESTAMP(6) )) AS VSD,

COALESCE(B.RED, DATE’3000-12-31′), COALESCE(B.VED, CAST(CURRENT_TIMESTAMP AS TIMESTAMP(6) )) AS VED

FROM

TABLE_B2 B

FULL OUTER JOIN

TABLE_A0 A

ON B.ENT_ID = A.ENT_ID

;

— CASE 0-2-> 2, Second Interpretation: A Table shows most recent history only

SELECT

COALESCE(B.ENT_ID, A.ENT_ID) AS ENT_ID, A. C_STATUS ,A.D_LEVEL_CD , A.E_VALUE, B.F_STATUS, B.G_LEVEL_CD, B.H_VALUE,

COALESCE(B.RSD,CURRENT_DATE),

COALESCE(B.VSD, CAST(CURRENT_TIMESTAMP AS TIMESTAMP(6) )) AS VSD,

COALESCE(B.RED, DATE’3000-12-31′), COALESCE(B.VED, CAST(CURRENT_TIMESTAMP AS TIMESTAMP(6) )) AS VED

FROM

TABLE_B2 B

FULL OUTER JOIN

TABLE_A0 A

ON B.ENT_ID = A.ENT_ID

AND B.RED>DATE

AND

(B.VED>CURRENT_TIMESTAMP

OR B.VED IS NULL )

;

 

Case 1-1->2 Once we have a solution for the base case 1-1->1 (see part 4 of this series), we can depart from it to arrive at a solution for this case by just adding a present and future validity layer in the result set of columns.

Our Reader Score
[Total: 1    Average: 5/5]
Teradata Historisation Part 3/5: Upleveling cases written by Paul Timar on April 28, 2014 average rating 5/5 - 1 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here