Calling Only Closed Records

Database

Calling Only Closed Records

This may seem like a fundamental question, but I have been playing around with the temporal keywords and have yet to come up with the desired results.

Is there a way to get an answerset with only closed records?

SEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME returns all records, open or closed, history, current, or future. And SEQUENCED VALIDTIME AND TRANSACTIONTIME AS OF TIMESTAMP '[Timestamp]' returns all record that are/were open on that date. The problem is, Teradata will not allow me to execute a SEQUENCED VALIDTIME AND SEQUENCED/NONSEQUENCED TRANSACTIONTIME PERIOD (TIMESTAMP '[timestamp]',TIMESTAMP'[timestamp]').

After all of that, I tried to do a query with SEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTION [...] WHERE TT <> PERIOD(TIMESTAMP '[timestamp]',TIMESTAMP'[timestamp]'). This executed, but did not return what I wanted, instead returning all records without the specified time period. So the direction that I would travel in trying to answer this question is: Is there a way in which I can use a wildcard for the first timestamp in the above code such that the where clause only depends upon the ending stamp (in this case would be '9999-12-31 23:59:59.999999+00:00')?
4 REPLIES
N/A

Re: Calling Only Closed Records

I'm not terribly familiar with the temporal keywords. I've only experimented with them a little, but couldn't you do basically what you describe in the last sentence? Could you construct a query that returns ALL records and then use the WHERE clause with some of the PERIOD data type functions to filter out records where the end timestamp is '9999-12-31 23:59:59.999999+00:00'?

Re: Calling Only Closed Records

Well yes. And it allows me to weed out the open records that also match on the starting timestamp, but not on every single open record. For example, if my query is:

SEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME
[...]
WHERE TT<>PERIOD (TIMESTAMP '2011-07-25 6:49:05.590000+05:00',TIMESTAMP '9999-12-31 23:59:59.999999+00:00');

records with timestamp period of ('2011-07-24 10:23:00.000000-05:00','9999-12-31 23:59:59.999999+00:00') will still appear. That is an open record which I do not want to see.

Now if instead in my query, I wrote something similiar to:

WHERE TT<>PERIOD (TIMESTAMP '%',TIMESTAMP '9999-12-31 23:59:59.999999+00:00');

theoretically, only closed timestamps should appear. Unfortunately, the character wildcard does not work properly in this manner, which leaves me with my original question.

Re: Calling Only Closed Records

Use a query form like the following to select all closed rows.

NONSEQUENCED TRANSACTION TIME
SELECT ...
WHERE END(TT) IS NOT UNTIL_CLOSED;

See Chapter 9 "Period Functions and Operators" in the "SQL Functions, Operators, Expressions, and Predicates" volume of the Teradata 13.10 documentation set.

Re: Calling Only Closed Records

Thank you very much, Jim! Must've missed that in my skim through.