Querying views that reference temporal tables
When you query a view that references a temporal table, you can specify a point in time or time range for a system period, an application period, or both.
About this task
A period specification that is after the name of a view in a table reference applies to all of the applicable table references in the fullselect of the definition of that view. If the view does not access any temporal tables, the period specification has no effect on the result table of the view.
- A view reference followed by a period specification must not include any user-defined functions.
- The definition of the view must not include a period specification.
Procedure
To query a view that references a temporal table, use one of the following methods:
- Specify a period specification (either a SYSTEM_TIME period or BUSINESS_TIME period) following the name of a view in the FROM clause of a query.
- Use the CURRENT TEMPORAL SYSTEM_TIME or CURRENT TEMPORAL BUSINESS_TIME special registers. In this case, you do not need to include a period specification in the query. For instructions on how to use these special registers instead of a period specification, see Querying temporal tables.
Example
The following example shows how you can create a view that references a system-period temporal table (stt), a bitemporal table (btt), and a regular base table (rt). Then you can query the view based on a point in time.
CREATE VIEW v0 (col1, col2, col3)
AS SELECT stt.coverage, rt.id, btt.bus_end
FROM stt, rt, btt WHERE stt.id = rt.id AND rt.id = btt.id;
SELECT * FROM v0
FOR SYSTEM_TIME AS OF TIMESTAMP ‘2013-01-10 10:00:00';