Creating, Replacing, and Using Views | Teradata Vantage - Rules and Restrictions for Creating, Replacing, and Using Views - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
To avoid unexpected or incorrect results when querying a view, observe the following rules when you create the view definition.
  • An operation on a view that references multiple tables/views causes those tables/views to be joined. The efficiency of such an operation depends on how it uses the view (for example, whether it makes use of indexes defined for the tables).
  • Any user with the privilege to use a view has the privilege to override a lock specified by a view definition.
  • If tables referenced by a view are modified to add or remove columns, or change data types of existing columns, then attempts to access the view can result in error messages or unexpected results. This is true whether the view references table columns by name or by SELECT *.
  • You can create views that reference global temporary, global temporary trace, and volatile tables.
  • You cannot create a view on a queue table (see CREATE TABLE (Queue Table Form)).
  • You cannot refer to a recursive view, a WITH clause, or WITH RECURSIVE clause in a view definition.
  • You can use BLOB, CLOB, UDT, Period, and Geospatial columns in views as long as they do not violate the restrictions on large object use with SQL or the semantics of creating or replacing a view.
    The restrictions are named in the following bulleted list:
    • You cannot create a base table with more than 32 LOB columns, nor can you alter a base table to have more than 32 LOB columns; however, a view definition can include as many LOB columns as its underlying base tables support.
    • You cannot specify BLOB, CLOB, UDT, Period, and Geospatial columns in the GROUP BY or HAVING clauses of a view definition.
    • BLOB, CLOB, UDT, Period, and Geospatial columns can be components of a view subject to the restrictions provided in this list and the semantics of view definitions.

      For more information about large objects and Period data types, see Teradata Vantage™ - Data Types and Literals, B035-1143.

      For more information about Geospatial data types, see Teradata Vantage™ - Geospatial Data Types, B035-1181.

  • Changing the DateForm or using the tdlocaledef utility to change the default Date format does not change the format of date columns in a view, which derive from the underlying tables and views. However, expressions in a CREATE VIEW statement that require date validation against the DateForm or default Date format can generate an error message if the DateForm or default Date format changes. To avoid date string validation errors, specify date constants as ANSI date literals and specify formats in a FORMAT phrase.

    If you change the tdlocaledef.txt file and issue a tpareset command, the new format string settings affect only those tables that are created after the reset. Existing table columns continue to use the extant format string in DBC.TVFields unless you submit an ALTER TABLE request to change it (see ALTER TABLE (Basic Table Parameters)).

  • The database checks the CREATE VIEW or REPLACE VIEW text for syntax errors, but not for semantic errors. Because of this, it is possible to create a view that is syntactically valid, but not valid semantically. No message is returned when this occurs.
  • If there are semantic errors in the view definition, then the request aborts and the database returns a message to the requestor when you perform a DML request that accesses tables through the view.
  • You can only specify an ORDER BY clause in a view definition if you also specify either the TOP n or TOP m PERCENT option. For details, see ORDER BY Clause Usage.

    You cannot specify an ORDER BY clause in a derived table subquery within a view definition.