Rules and Restrictions for Creating, Replacing, and Using Views
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
You cannot create a view on a queue table (see “CREATE TABLE (Queue Table Form)” on page 673).
You cannot refer to a recursive view, a WITH clause, or WITH RECURSIVE clause in a
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 SQL Data Types and Literals.
For more information about Geospatial data types, see SQL Geospatial Types.
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)” on page 31).
Teradata 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
If there are semantic errors in the view definition, then the request aborts and Teradata
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” on page 777.
You cannot specify an ORDER BY clause in a derived table subquery within a view definition.