All Forums Database
martin.lawless 4 posts Joined 07/11
25 Jul 2011
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')?

mnylin 118 posts Joined 12/09
25 Jul 2011

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'?

martin.lawless 4 posts Joined 07/11
25 Jul 2011

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:

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.

Jim Chapman 449 posts Joined 09/04
25 Jul 2011

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


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

martin.lawless 4 posts Joined 07/11
25 Jul 2011

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

You must sign in to leave a comment.