15.00 - CREATE VIEW, REPLACE VIEW(Temporal Forms) - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

CREATE VIEW, REPLACE VIEW
(Temporal Forms)

Purpose  

CREATE VIEW defines a view on a set of tables or views or both.

REPLACE VIEW redefines an existing view or, if the specified view does not exist, creates a new view with the specified name.

Syntax  

Note: Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this manual. For additional syntax, see SQL Data Definition Language, SQL Data Manipulation Language, and SQL Data Control Language.

Note: To ensure application portability to future ANSI standards for temporal SQL, Teradata recommends explicit specification of all temporal qualifiers.

 

Syntax Element …

Specifies …

view_name

the name of the view.

database_name
user_name

the name of the database or user to contain view_name if something other than the current database or user.

column_name

the name of a view column. If more than one column is specified, list their names in the order in which each column is to be displayed for the view.

CURRENT VALIDTIME

that select_statement is a current query in the valid-time dimension. The result set is a nontemporal table.

VALIDTIME AS OF date_timestamp_expression

that select_statement retrieves rows where the period of validity overlaps the specified AS OF expression. The result set is a nontemporal table.

date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.

VALIDTIME
SEQUENCED VALIDTIME

that select_statement is a sequenced query. The result set is a valid-time table that includes an extra column for the overlapped valid-time period. If the list of columns for the view does not provide a name for the extra column, the default name is “VALIDTIME”.

If select_statement is a sequenced query in the valid-time and transaction-time dimensions, and the list of columns for the view provides names for the extra columns, the first extra name is the name of the resulting valid-time column and the second extra name (if it exists) is the name of the resulting transaction-time column.

NONSEQUENCED VALIDTIME

that select_statement is a nonsequenced query in the valid-time dimension. If period_expression is specified, the nonsequenced query produces a table with valid time; otherwise, the result set is a nontemporal table.

The result set for a valid-time table includes an extra column for the overlapped valid-time period. If the list of columns for the view does not provide a name for the extra column, the default name is “VALIDTIME”.

If select_statement is a nonsequenced query in the valid-time dimension and sequenced in the transaction-time dimension, and the list of columns for the view provides names for the extra columns, the first extra name is the name of the resulting valid-time column and the second extra name (if it exists) is the name of the resulting transaction-time column.

period_expression

the period of applicability for the sequenced or nonsequenced query.

CURRENT TRANSACTIONTIME

that select_statement is a current query in the transaction-time dimension. The result set is a nontemporal table.

TRANSACTIONTIME AS OF date_timestamp_expression

that select_statement retrieves rows whose transaction-time period in the row overlaps the specified AS OF expression. The result set is a nontemporal table.

date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.

SEQUENCED TRANSACTIONTIME

that select_statement is a sequenced query in the transaction-time dimension. The result set is a table with transaction time.

There can only be one table referenced in select_statement and the table must have transaction time.

NONSEQUENCED TRANSACTIONTIME

that select_statement is a nonsequenced query in the transaction-time dimension. A nonsequenced query produces a nontemporal table as a result set.

AS OF date_timestamp_expression

that select_statement retrieves rows whose valid-time and transaction-time periods overlap the specified AS OF expression.

date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.

select_statement

existing SELECT statement syntax.

SELECT Statement

All rules that apply to the temporal form of the SELECT statement are applicable for queries specified in views.

If no temporal qualifier is specified for the view and the view references any temporal tables, the temporal qualifier defaults to the applicable session temporal qualifier.

Selecting from a View on a Temporal Table

A query that selects from a view can specify a temporal qualifier that is different from the temporal qualifier of the SELECT statement in the view definition.

Updatable Views

Views created on temporal tables are said to be updatable if they satisfy all the existing rules of updatable views on nontemporal tables and:

  • For a view on a valid-time table or on another view, the valid-time qualifier must be SEQUENCED VALIDTIME with the restriction that it must not specify a period of applicability.
  • For a view on a transaction-time table or on another view, the transaction-time qualifier must be SEQUENCED TRANSACTIONTIME.
  • For a view on a bitemporal table or on another view, the valid-time qualifier must be SEQUENCED VALIDTIME and the transaction-time qualifier must be SEQUENCED TRANSACTIONTIME.
  • The table must not be specified with an AS OF clause.
  • Sequenced updatable views permit current, sequenced, nonsequenced, and nontemporal DML operations. A current or sequenced form of UPDATE issued on the view modifies the valid-time and transaction-time column values. An update privilege on the valid-time column is required in addition to those privileges normally required to perform UPDATE on an updatable view. (No additional privilege is required for the transaction-time column.)

    When the WITH CHECK OPTION is specified in the updatable view and the rows are updated using the view, only the modified rows are checked for violations. Those rows that are inserted by the system as part of the temporal semantics (for example close of a row in the transaction-time dimension) are excluded from such checks as they are created to maintain temporal semantics.

    Temporal Sequenced View Support for Teradata ILDMs

    Teradata Database supports the creation of sequenced views to be used in Teradata Industry Logical Data Models (ILDMs). These views can use all existing Teradata Database features with a SEQUENCED qualifier. Some of the features that are often used in ILDMs are:

  • sequenced outer joins
  • sequenced subqueries (inclusion, exclusion joins)
  • sequenced aggregation
  • sequenced OLAP
  • Although these views are created using the SEQUENCED temporal qualifier, they can only be used in queries having point-in-time (CURRENT or AS OF) temporal qualifiers.

    Related Information

     

    For more information on...

    See...

    CREATE VIEW (regular form)

    SQL Data Definition Language

    CREATE RECURSIVE VIEW (temporal form)

    “CREATE RECURSIVE VIEW/REPLACE RECURSIVE VIEW (Temporal Forms)” on page 62

    temporal table views

    “Views on Temporal Tables” on page 213