Issue Querying Temporal Table Through View

Database

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?

Tags (2)
7 REPLIES

Re: Issue Querying Temporal Table Through View

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;

Enthusiast

Re: Issue Querying Temporal Table Through View

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.

Enthusiast

Re: Issue Querying Temporal Table Through View

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!

Enthusiast

Re: Issue Querying Temporal Table Through View

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.

Re: Issue Querying Temporal Table Through View

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.

Enthusiast

Re: Issue Querying Temporal Table Through View

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.

Re: Issue Querying Temporal Table Through View

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