SEQUENCED VALIDTIME PERIOD '(2009-01-01, 2009-12-31)'
SELECT Policy_ID, Customer_ID, Validity FROM (
SEQUENCED VALIDTIME SELECT Policy.*, Validity
FROM Policy) AS my_derived_table;
Policy_ID Customer_ID Validity VALIDTIME
----------- ----------- ----------------------- ------------------------
541077 766492008 ('09/12/21', '99/12/31') ('09/12/21', '09/12/31')
541008 246824626 ('09/10/01', '99/12/31') ('09/10/01', '09/12/31')
541145 616035020 ('09/12/03', '10/12/01') ('09/12/03', '09/12/31')
Comparing the Validity and VALIDTIME columns demonstrates that the valid-time period of the result set is the intersection of the valid-time period in the original Policy table Validity column with the PA specified in the sequenced select statement.
This example uses the SELECT * asterisk notation in combination with explicit specification of a temporal column. For more information on the asterisk see Asterisks in Select Lists
Other clauses of the SELECT statement, such as WHERE, cannot reference the new VALIDTIME column, and VALIDTIME cannot be used as an alias for any other projected columns.
If a view or a derived table results in a valid-time table, it is treated like any other valid-time table specified in the query. Otherwise, it is treated like a nontemporal table.
The sequenced form of a temporal query is limited to a simple select from a single valid-time table or a simple select with inner joins from multiple tables. A noncorrelated scalar subquery can be used in the temporal query.
- Outer joins
- Set operations
- Ordered analytic functions
- Subqueries other than noncorrelated scalar subqueries
- WITH, WITH RECURSIVE, TOP n, DISTINCT
- The term row or rows mentioned in the context of sequenced inner joins implies all the rows of a table where the period of validity overlaps with the period of applicability of the query and satisfies any specified single table conditions.
- The joined row contains the projected columns in the specified order and a valid-time column whose value is set to the result of the intersection of the period of applicability, the period of validity of the left row, and the period of validity of the right row.
- If the valid-time column of each table involved in the join has the same granularity as the period of applicability, the granularity of the resulting period of validity is the same as the granularity of the period of applicability. Otherwise, the granularity of the result period of validity is the finest amongst them and each valid-time column is implicitly converted to the finest valid-time granularity before the join is performed.
For example, consider a sequenced join with a PERIOD(DATE) period of applicability, a table with a PERIOD(TIMESTAMP(3)) valid-time column, and a table with a PERIOD(TIMESTAMP(5)) valid-time column. The resulting VALIDTIME column has a PERIOD(TIMESTAMP(5)) data type.
The granularity difference does not usually cause an issue. However, it can be confusing for some types of projected columns. For example a column that represents charge per day in the original table can be confusing when it is projected in a result having a validity of hours. Such cases can be avoided if tables with similar granularities are joined, or the data model specifies the same granularity for tables that are likely to be joined.
Similarly if a non-temporal table is joined with a temporal table, the resulting rows reflect the validity of the temporal table. This validity can be misleading if applied to data that was not originally of a temporal nature.
- For a sequenced inner join, the left row is joined with a matching row from the right table only if the periods of validity of the rows overlap.
A sequenced query with an ORDER BY clause may specify ordering on the resulting VALIDTIME column. To specify this column in the ORDER BY clause, use the VALIDTIME keyword or use its name delimited by double quotation marks ("VALIDTIME"). If the ordering clause does not specify VALIDTIME, the resulting VALIDTIME column is automatically made a part of the ORDER BY list as the last element in the list with ASC (ascending) default ordering