Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 136 137 138 139 140 141 < 142 > 143 144 145 146 147 148 .. 241 >> Next


tables and views must be held by the owner of the view WITH GRANT OPTION.

To create a view, the user must have the CREATE VIEW privilege. The creator also receives the DROP VIEW, INSERT, UPDATE, DELETE, SELECT privileges on the newly created view, WITH GRANT OPTION.

To update a table via a view, you must have the update privilege on the view, and the view owner must have the update privilege on the table.

To delete rows using a view, you must have the delete privilege on the view and the view owner must have the delete privilege on the table.

If updating or deleting using a view, and a search condition exists, you must have SELECT ACCESS rights on the view, and the view owner must have SELECT ACCESS on the objects searched.

Teradata RDBMS for UNIX SQL Reference

8-109
Teradata SQL Syntax Guide

CREATE VIEW

Limit On Request Size

WHERE and HAVING Clauses

Caution:

Rules for Creating and Using Views

Because expansions of source text in view definitions are made to fully qualify object names and to normalize expressions, it is possible for a view to be defined but to be unusable because of stack overflows in the Syntaxer at execution. In this case, the system returns a 3741 error.

The WHERE clause specifies the conditional expression by which rows for the view are to be selected.

The HAVING clause selects the groups defined by a GROUP BY clause that satisfy a conditional expression.

Joins may only be defined in a HAVING clause of a view when the HAVING clause is part of a subquery.

Do not use SELECT * if any underlying table or view might be modified.

Names are resolved and columns are defined when the view is defined; therefore, changes to the table are not incorporated into the view definition if the SELECT * construct is used.

In order to avoid unexpected or incorrect results when querying a view, the following rules should be considered when creating the view:

• Joins may not be used against a view defined with a GROUP BY clause.

• An operation on a view that references multiple tables causes the tables 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).

• A view may be used as a modifier; that is, to insert or update data in the underlying table. A modifier view may reference only one table, and no data descriptions or aggregates may be used in the expressions that reference the table columns.

• Any user with the right to use a view has the right to override a lock specified for a view. Users of the Performance Monitor/Application Programming Interface who have the MONITOR SESSION privilege can view all locks in the system, and can determine which other users’ locks are blocking other users.

• 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 may result in error messages or unexpected results. This is true whether the view references table columns by name or by SELECT *.

8-110

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE VIEW

WITH CHECK OPTION Clause in Views

WITH CHECK OPTION pertains to views that can be used to INSERT or UPDATE values in an underlying base table.

WITH CHECK OPTION may be used when creating updatable views.

Typically a view is created to restrict which columns and which rows the user can access in a table. Row restriction is accomplished by using a WHERE clause when the view is created. The WITH CHECK OPTION is used to restrict which rows in the table can be affected by an INSERT or UPDATE statement

The WITH CHECK OPTION clause is inserted at the end of the statement defining the view.

WHEN WITH CHECK OPTION is . . . Any INSERT or UPDATE made to the table through the view . . .
used only creates or modifies rows that satisfy the WHERE clause.
not used ignores the WHERE clause used in defining the view.

Example

A query specification could state "WHERE column_1 > 10" which restricts the rows that can be seen through the view.

The WITH CHECK OPTION clause also restricts the rows that can be inserted by an INSERT or restricts the resulting row of an UPDATE statement.

Updatable Views and WITH CHECK OPTION

The following rules apply to updatable views and WITH CHECK

OPTION.

• If WITH CHECK OPTION is specified, the view should be updatable. In addition, any insert or update to the table through the view will not result in the creation of a row for which the where clause is false.

• If WITH CHECK OPTION is not included in an updatable view, then any where clause contained in the query defining the view is ignored for any insert or update action through the view.

• Nested Views: A user could define nested views which only reference a single base table, which may allow the views to be updatable. In this case, the presence of a WITH CHECK OPTION in a view definition causes the WHERE clause for that view, and WHERE clauses of underlying views, to be used in the constraint on INSERT or UPDATE.
Previous << 1 .. 136 137 138 139 140 141 < 142 > 143 144 145 146 147 148 .. 241 >> Next