All Forums Database
mufford 2 posts Joined 06/11
14 May 2012
Issue Querying Temporal Table Through View

Hello,

I have a view that references a temporal table, i.e.

 

CREATE MULTISET TABLE P_SMA_T.myTable
(
    EffectivePeriod PERIOD(DATE) AS VALIDTIME
    ...
);

CREATE VIEW P_SMA.vt_myTable
AS
SELECT EffectivePeriod
    ...
FROM P_SMA_T.myTable;

 

I can run the following query against P_SMA_T.myTable:

VALIDTIME AS OF DATE'2012-04-10'
SELECT DISTINCT EffectivePeriod
FROM P_SMA_T.myTable;

However, when I run it against my view, I get the following error:

VALIDTIME AS OF DATE'2012-04-10'
SELECT DISTINCT EffectivePeriod
FROM P_SMA.vt_myTable;

ValidTime qualifier requires at least one table with ValidTime. (9330)

Any ideas what I need to do to get this query to work against a view?

 

 

 

mufford 2 posts Joined 06/11
15 May 2012

I've figured out the issue. I needed to add SEQUENCE VALIDTIME to the view, i.e.

 

CREATE VIEW P_SMA.vt_myTable
AS

SEQUENCED VALIDTIME /* new line */

SELECT EffectivePeriod
...
FROM P_SMA_T.myTable;

mmatten 17 posts Joined 06/07
10 Jul 2012

Is it really necessary to code the SEQUENCED VALIDTIME into the view?

That seems very restrictive and it means that there is a need to have different sets of views for different presentations of the data.

E.g. one for a CURRENT VALIDTIME view (current data only) and one for a SEQUENCED VALIDTIME view (includling past, current, and future).

I hope there really is a more elegant way to do this.

 

mmatten 17 posts Joined 06/07
10 Jul 2012

Aha!

If I code SEQUENCED VALIDTIME into the view and query the view with SELECT * FROM <view> then it seems that CURRENT VALIDTIME overrides the temporal qualifier in the view.

If I query the view using SEQUENCED VALIDTIME SELECT * FROM <view> then I see all the past/future rows too as if I'd queried the table directly.

Nice!

mmatten 17 posts Joined 06/07
10 Jul 2012

After some more playing I think I understand:

The output from the view is sequenced and so the query against the view is querying sequenced data.

If I run SELECT * FROM <view> then that's using CURRENT VALIDTIME and so only shows current data.

If I run SEQUENCED VALIDTIME SELECT * FROM <view> then that's using SEQUENCED VALIDTIME and so only shows th additional.

 

davebutler 2 posts Joined 06/11
10 Jul 2012

mmatten, your conclusions are correct. By constructing the View with SEQUENCED VALIDTIME, it behaves nearly identical to the underlying table: Temporal Upward Compatibility defaults to CURRENT VALIDTIME, but you can use SEQUENCED VALIDTIME (or VALIDTIME AS OF <DATE>) to see other Temporal data.

I have noticed one subtle difference between directly querying the temporal table and querying the temporal access view: Querying the table will not include the VALIDTIME column by default for a CURRENT VALIDTIME PA in the form of 'SELECT * FROM TEMPORAL_TABLE', but it is available to be requested if specific columns are specified in the SELECT statement. For the Temporal access View, the View creator can pass through the VALIDTIME column or not, as desired, but the exact same behavior will not be seen. If the VALIDTIME column is specified in the Temporal View it will always appear in 'SELECT * FROM TEMPORAL_VIEW'. If not, it cannot be selected by specifying the column in the SELECT statement.

This is only a subtle issue that may not present any real problems in practice. With non-Temporal tables Access Views look just like their underlying tables to the user of the SELECT statement. I was a little surprised to find this slight difference with Temporal Views.

mmatten 17 posts Joined 06/07
10 Jul 2012

It's shame that this functionality doesn't also exist for TRANSACTIONTIME.

I'd rather not create all the access views using NONSEQUENCED TRANSACTIONTIME time and the make the user/tool filter the results using, for example, a CURRENT_TIMESTAMP BETWEEN vtbegin AND vtend.

davebutler 2 posts Joined 06/11
11 Jul 2012

Mark, this functionality DOES seem to exist for TRANSACTIONTIME. Your view can contain SEQUENCED TRANSACTIONTIME and your query against the view can start with CURRENT TRANSACTIONTIME (or leave it off since that is the default) or TRANSACTIONTIME AS OF TIMESTAMP'YYYY-MM-DD HH:MM:SS.XXXXXX' to get a previous point in time view of data.

 

Dave

You must sign in to leave a comment.