Creating and Using VIEWS Teradata

Views are created using a special form of Data Definition Language (DDL). The CREATE requests a new VIEW, provides the name and the SELECT for the view. It is suggested that the name of the view either start with "v_" or end with "_v" to identify it as a view name (check your site standards). That way, it is visibly obvious to people that this is a view and not a table. The name of the view must be unique from the names of other objects in the database. The CREATE VIEW verifies that the name does not already exist and return an error if it does.

The rest of the statement is the SELECT statement required to return the desired columns and rows. The syntax for creating a view follows:

A view, if created with "SELECT * FROM <table-name>" reflects the column definitions at the time the CREATE VIEW is executed. If the data table is altered afterwards, it will not be seen when using the view definition. Therefore, using this technique is discouraged.

The Employee table is used to demonstrate the use of views:

Employee table is used to demonstrate the use of views

The following creates a view to return the employees (above table) in department 200. It limits the view to an employee's number, last name, and salary. This view restricts both columns (i.e. first_name) and rows not for department number 200:

The next SELECT can be used to return all the columns and rows from this VIEW:

SELECT * FROM empl_200_v ;SELECT can be used to return all the columns and rows from this VIEW

This above view contains alias names for all the columns using the AS to define the alias in the SELECT. The view was created using them and they become the column names of the view. However, if the original SELECT does not contain alias names and they are needed in the view, alias names may be assigned as part of the CREATE VIEW.

The following creates the same view as above, however, it establishes the alias using this other technique:

Using this second technique, if one alias is established in the parentheses, all columns must be represented with an alias column name. The order of the name needs to respectively match the sequence of the columns in the SELECT list. If the SELECT contains an alias, this technique over-rides it using the name in parentheses.

It is a common practice to use views for the purpose of accomplishing a join. The lengthy and sometimes complicated join code and conditions are made easier and automatic when they are stored in a view. By simply selecting from the view name, the join is automatically performed and the appropriate columns and rows are returned. A view does not store the data separately; only the SELECT is stored.

These two tables are used in the following examples:

two tables are used in the following examples

two tables are used in the following examples

The following view performs the join as part of its processing whenever a user does a SELECT from the view:

5 Rows Returnedperforms the join as part of its processing whenever a user does a SELECT from the view

Notice that all alias names and formatting defined in the view become the default for the virtual table. Now that there is a view, it can be involved in another join as a table. As a result, there may be a 3-table join executed as two 2-table joins using the view. Virtually, a view is a table.

Another common use for views is to summarize data. Instead of creating an actual table and storing the data twice, many times Teradata is powerful enough to do all the aggregation within a view.

The following creates a view to perform the aggregation of all orders placed by every customer for each month:

The view can then be used to aggregate the columns that are created as a result of an aggregate, as seen next:

SELECT SUM(Order_Sum) FROM Aggreg_Order_v ; 1 Row Returnedaggregate the columns that are created as a result of an aggregate
2 Rows Returnedaggregate the columns that are created as a result of an aggregate

Notice that the view contains a WHERE and so does the SELECT from the view. All conditions within the view and the user SELECT must be satisfied for the rows to be returned. Together, the conditions are compared using the AND logical operation. This can be seen using an EXPLAIN on the SELECT from the view. Also seen in the EXPLAIN is the use of actual table names, never the view name. Therefore, there is no additional processing overhead compared to selecting the rows directly out of the table. At the same time, this allows companies the option of additional security by not revealing to users if the rows are retrieved directly from a table or through a view.


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

Teradata Topics