Views-SELECT statement Teradata

Compatibility: ANSI

A View is a SELECT statement that is stored in the Data Dictionary (DD). It is sometimes referred to as a virtual table because a view is used exactly like a table with columns and rows. Views are used to provide customized access to data tables for the purpose of restricting the number of columns, to derive columns, to combine columns from multiple data tables (join), to restrict the number of rows returned from one or more data tables, to simplify SQL creation or isolate the actual table from the user.

Restricting access to columns from one or more data tables is normally done for reasons of security. If the view does not select a column, it is not available to the user. By creating a view to explicitly request the desired column names from the data table(s) and omitting the restricted columns, it looks as though the columns do not exist. Therefore, they are secure from the users' restricted access to columns through the view.

To restrict rows from user access, the view can be written to disallow access to rows by using a WHERE clause in the stored SELECT. The WHERE clause limits the rows returned to the user by rejecting all rows that do not meet the stated criteria.

Reasons to Use Views

If SQL were the only tool available for a site, views would be strongly recommended. Then, any user can be taught to code a simple "SELECT * FROM Viewname" without having to possess more in depth SQL expertise. All the sophisticated SQL would be stored in a variety of views.

Another factor is that some 4GL languages are not written to perform all the newer more sophisticated functions available in SQL. A view can provide these functions and the 4GL can simply access the view as a virtual table.

Another powerful functionality within views is the use of aggregates. Since data marts often involve summary tables, views can be used to build a logical data mart. When this is done within Teradata, no data movement or extra storage space is required on a separate system. Instead, all summary "virtual tables" are created dynamically using views when they are needed.

Views also provide insulation between the users and the actual data tables. Because Views are stored in the DD, they require no Permanent space in a user's database.

Therefore, they can be tailored to the needs of specific users or organizations without directly impacting the actual data table.

Considerations for Creating Views

When creating a view, there are certain considerations that must be taken into account. In Teradata, a view may not contain:

  • An ORDER BY – rows are not ordered in a table, nor in a view
  • Indices – however, any index on underlying tables may be used
  • Column names must use valid characters
  • Aggregates must be assigned an alias due to ( )
  • Derived data with mathematics symbols must have an alias

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics