Temporals and Outer Joins

Database
Enthusiast

Temporals and Outer Joins

One of the caveats (if i may say so) of temporals is that apart from inner joins, you DO NOT have the liberty of doing anything with it...ofcourse outside the scope of usual temporal commands. For instance, you can't do an outer join or a UNION or an EXISTS etc

Now, this definitely doesn't mean that one should stop considering temporals, infact i believe that temporals would be and is a quite a game changing phenomenon for Teradata. It's an absolute charm as a concept.

I've been working with Temporals for over a couple of months now and have devised a not so slick formula of getting around with outer joins involving temporal tables.

Keep in mind, we CAN DO Inner joins... and now we are going to use this exact feature availability to achieve what we need to and yes, with an outer join.

Let's create a temporal table as VALIDTIME and load some records with different date ranges.

CREATE MULTISET TABLE TEMPORAL_TABLE
(
Company CHAR(1),
Contract_Id INTEGER,
Location_Id INTEGER,
Effective_Period PERIOD(DATE) TITLE 'Effective Period' AS VALIDTIME)
PRIMARY INDEX TEMPORAL_TABLE_idx_PR ( Company ,Contract_Id );

SEQUENCED VALIDTIME INSERT INTO TEMPORAL_TABLE VALUES ('C',12345,1000, PERIOD(DATE '2011-01-01', DATE '2011-01-20'));
SEQUENCED VALIDTIME INSERT INTO TEMPORAL_TABLE VALUES ('C',12345,1000, PERIOD(DATE '2011-01-20', DATE '2011-02-20'));
SEQUENCED VALIDTIME INSERT INTO TEMPORAL_TABLE VALUES ('C',12345,1000, PERIOD(DATE '2011-02-20', DATE '2011-03-20'));
SEQUENCED VALIDTIME INSERT INTO TEMPORAL_TABLE VALUES ('C',12345,1000, PERIOD(DATE '2011-03-20', UNTIL_CHANGED));

NONSEQUENCED VALIDTIME SELECT * FROM TEMPORAL_TABLE;

Company Contract_Id Location_Id Effective_Period
------------------------------------------------------ ------
C 12345 1000 ('2011-01-01', '2011-01-20')
C 12345 1000 ('2011-01-20', '2011-02-20')
C 12345 1000 ('2011-02-20', '2011-03-20')
C 12345 1000 ('2011-03-20', '9999-12-31')

Now, lets see what's there for a particular date range...

SEQUENCED VALIDTIME PERIOD(DATE '2011-01-01', DATE '2011-02-01')
SELECT * FROM TEMPORAL_TABLE

Company Contract_Id Location_Id Effective_Period
------------------------------------------------------ ------
C 12345 1000 ('2011-01-01', '2011-01-20')
C 12345 1000 ('2011-01-20', '2011-02-01')

What if I need every account id from another table, say ACCOUNT_ID from LOCATION table..That might require an outer join.

The following query would fail with error

"SELECT Failed. 9437: This form of sequenced operation is not allowed."

SEQUENCED VALIDTIME PERIOD (DATE '2011-01-01', DATE '2011-02-01')
SELECT A.CONTRACT_ID,B.ACCOUNT_ID
FROM TEMPORAL_TABLE A

LEFT OUTER JOIN LOCATION B
ON A.LOCATION_ID = B.LOCATION_ID

You cannot do that with temporals.. Remember... only inner joins allowed...

BUT, temporals do allow an outer join with Nonsequenced Validtime or AS OF queries.. So,

REPLACE VIEW TEMPORAL_LEFT_JOIN
AS
NONSEQUENCED VALIDTIME
SELECT TT.COMPANY ,
TT.CONTRACT_ID ,
LOC.ACCOUNT_ID ,
TT.EFFECTIVE_PERIOD
FROM TEMPORAL_TABLE TT

LEFT JOIN LOCATION LOC
ON TT.LOCATION_ID = LOC.LOCATION_ID

Since, it's nonsequenced, i'll get everything from the table.

SELECT * FROM TEMPORAL_LEFT_JOIN

Company Contract_Id ACCOUNT_ID Effective_Period
------------------------------------------------------ ------
C 12345 100-200 ('2011-01-01', '2011-01-20')
C 12345 100-200 ('2011-01-20', '2011-02-20')
C 12345 100-200 ('2011-02-20', '2011-03-20')
C 12345 100-200 ('2011-03-20', '9999-12-31')

Harping on that point again "Only Inner Joins in Temporals", i'm now going to use this view and do an inner join back to the temporal table, based on it's unique identifier, which is CONTRACT_ID in this case......

SEQUENCED VALIDTIME PERIOD (DATE '2011-01-01', DATE '2011-02-01')
SELECT A.CONTRACT_ID,
A.ACCOUNT_ID
FROM
(SELECT DISTINCT CONTRACT_ID, ACCOUNT_ID
FROM TEMPORAL_LEFT_JOIN) A

INNER JOIN TEMPORAL_TABLE B
ON A.CONTRACT_ID = B.CONTRACT_ID ;

Contract_Id ACCOUNT_ID Effective_Period
----------------------------------------------------
12345 0070-0915 ('2011-01-20', '2011-02-01')
12345 0070-0915 ('2011-01-01', '2011-01-20')

We get LOCATION.ACCOUNT_ID which was left outer joined to the temporal table for the period (DATE '2011-01-01', DATE '2011-02-01'). This is your sequenced validtime data.... So yes, we got temporal data that involved an outer join.

Many would be wondering, why did i use the SELECT DISTINCT inner query...
Reason is, if i do not, i'll get a cartesian product result set...

SEQUENCED VALIDTIME PERIOD (DATE '2011-01-01', DATE '2011-02-01')
SELECT A.CONTRACT_ID,B.ACCOUNT_ID
FROM TEMPORAL_TABLE A

LEFT OUTER JOIN LOCATION B
ON A.LOCATION_ID = B.LOCATION_ID

Contract_Id ACCOUNT_ID Effective_Period
----------------------------------------------------
12345 0070-0915 ('2011-01-20', '2011-02-01')
12345 0070-0915 ('2011-01-20', '2011-02-01')
12345 0070-0915 ('2011-01-20', '2011-02-01')
12345 0070-0915 ('2011-01-20', '2011-02-01')
12345 0070-0915 ('2011-01-01', '2011-01-20')
12345 0070-0915 ('2011-01-01', '2011-01-20')
12345 0070-0915 ('2011-01-01', '2011-01-20')
12345 0070-0915 ('2011-01-01', '2011-01-20')

Not sure if teradata would allow outer joins and other join functionality involving temporals in future releases.. but till that time, this "MIGHT" work...

Sanji
7 REPLIES
Enthusiast

Re: Temporals and Outer Joins

Or put the SEQUENCED queries into derived tables (subqueries) in the main query and join them...?

Enthusiast

Re: Temporals and Outer Joins

I guess you'd need to use the OVERLAP function to determine the valid cases of concurrent validity.

I guess you don't get the calculated Period of Applicability (the VT) either then.

Is there a function to calculate the overlap (PA / VT) from 2 periods?

Senior Apprentice

Re: Temporals and Outer Joins

Yep, "P1 P_INTERSECT P2" returns the overlapping part

Dieter

Enthusiast

Re: Temporals and Outer Joins

Am I wrong, or would this allow us to do a SEQUENCED TRANSACTIONTIME join query that isn't supported naturally by the DBMS?

Enthusiast

Re: Temporals and Outer Joins

An interesting approach.  Thanks for sharing.

It's unfortunate that outer joins are not supported.    I'm sure there is a reason why ???

In the (outer join) views that I wish to create I would like to have the default behaviour of "Current" , but also allow "AS OF" queries.

I guess the same result is achievable with the above solution, but it makes the queries more complicated.  

How have you found the performance ?  You have to join to the temporal table twice, which could quite a large table.

Enthusiast

Re: Temporals and Outer Joins

Hi,

what about TEMPORAL DATA and OUTER JOINs today on V14.10 ?

i am facing some cases of requests ending with 3610 error and crashdumps, with a stack of views on temporal tables, each view based on left outer joins ...

Thanks for all up to date information ! i couldn't find anything in docs.

Pierre

Teradata Employee

Re: Temporals and Outer Joins

If you are getting 3610, that is a SW fault and should be reported to the Teradata Global Support center. Please write an incident.