An introduction to the view definition.
Example: Creating a View with Column Titles
The following request creates a view of the department table. Each column in the view is defined with a title. Therefore, the view data is displayed with column titles that differ from those defined for the department table.
CREATE VIEW dept AS SELECT deptno(TITLE 'Department Number'), deptname(TITLE 'Department Name'), loc (TITLE 'Department Location'), mgrno(TITLE 'Manager Number') FROM department;
Example: Using a View in an Update, Insert, or Delete Request
This example shows how use of a view name in an UPDATE, INSERT, or DELETE request allows you to add, change, or remove data in the table set on which the view is based. For example, updating data via a view changes data in the underlying table. Inserting or deleting rows via a view adds or removes rows from the underlying table.
Consider the following staff_info view, which gives a personnel clerk retrieval access to employee numbers, names, job titles, department numbers, sex, and dates of birth for all employees except vice presidents and managers:
CREATE VIEW staff_info (number, name, position, department, sex, dob) AS
SELECT employee.empno, name, jobtitle, deptno, sex, dob
FROM employee
WHERE jobtitle NOT IN ('Vice Pres', 'Manager')
WITH CHECK OPTION;
If the owner of staff_info has the insert privilege on the employee table, and if the clerk has the insert privilege on staff_info , then the clerk can use this view to add new rows to employee. For example, performing the following INSERT request inserts a row in the underlying employee table that contains the specified information:
INSERT INTO staff_info (number, name, position, department, sex, dob) VALUES (10024, 'Crowell N', 'Secretary', 200, 'F', 'Jun 03 1960');
The constraint on staff_info illustrated by the following WHERE clause applies to any insert using this view that includes the WITH CHECK OPTION phrase.
...
WHERE jobtitle NOT IN ('Vice Pres', 'Manager')
...
Therefore, the preceding INSERT request would fail if the Position entered for Crowell was Vice Pres or Manager.
If this view were defined to not include the WITH CHECK OPTION, and a user had UPDATE privilege, that user could update a job title to Vice Pres or Manager. The user would be unable to access the changed row through the view.
The following request changes the department number (from 200 to 300) entered for Crowell in the preceding INSERT request:
UPDATE staff_info SET department = 300 WHERE number = 10024;
Performing the following DELETE request removes the row for employee Crowell from the staff_info table:
DELETE FROM staff_info WHERE number = 10024;
A view is a useful method for allowing users access to table data. However, as the preceding examples suggest, granting another user insert, update, and delete privileges on a view means relinquishing some control over your data. Carefully consider granting such privileges.
The default is not to constrain updated or inserted values unless the view definition explicitly includes WITH CHECK OPTION.
Example: Creating a View With a Dynamic UDT Expression
This example shows how a dynamic UDT can be specified in a view definition.
First, the following request shows the long version in which the column expressions are all specified:
SELECT udf_aggregate_mp_struc(NEW VARIANT_TYPE(MultiType.w AS w, MultiType.x AS x, MultiType.y AS y, NEW MP_STRUCTURED_INT(MultiType.w, MultiType.x, MultiType.y) AS z)) AS m FROM MultiType; *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. m ----------- 60
Now create a view that incorporates the previous SELECT request:
CREATE VIEW multitype_v AS SELECT udf_aggregate_mp_struc(NEW VARIANT_TYPE(MultiType.w AS w, MultiType.x AS x, MultiType.y AS y, NEW MP_STRUCTURED_INT(MultiType.w, MultiType.x, MultiType.y) AS z)) AS m FROM MultiType;
Running the following SELECT request against the newly created view returns the identical result:
SELECT * FROM multitype_v; *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. m ----------- 60
Example: Replacing a View
To change the department name column in the employee_info view to a department number column, type:
REPLACE VIEW employee_info (number, name, position, department)
AS SELECT employee.empno, name, jobtitle, deptno
FROM emp_info
WHERE jobtitle NOT IN ('vice pres', 'manager');
You must have the DROP privilege on a view or its containing database or user to replace it.
If you enter a REPLACE VIEW request for a view that does not exist, the system creates the view following the specifications in the REPLACE statement.
Example: Creating a View on a Table With Row-Level Security Constraints
This example shows how you can create a view on a table that is defined with row-level security constraints. First create the table emp_record. The view emp_record_view will be defined on this table.
The row-level security constraint in the emp_record table is defined as group_membership. When Teradata Database creates this emp_record, it implicitly adds a fourth security constraint column named group_membership to the table.
CREATE TABLE emp_record ( emp_name VARCHAR(30), emp_number INTEGER, salary INTEGER, group_membership CONSTRAINT) UNIQUE PRIMARY INDEX (emp_name);
Now define the view emp_record_view on emp_record.
CREATE VIEW emp_record_view AS SELECT emp_number, salary, group_membership FROM emp_record WHERE emp_name=user;