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 Vantage 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;
Example: Using a WITH Modifier in CREATE VIEW
A common table expression (CTE) in a WITH modifier can reference either preceding or subsequent CTEs defined in the WITH modifier, provided that the CTE does not indirectly reference itself. That is, circular references are not allowed.
Following is the table definition for this example.
CREATE TABLE orders (customer_id INTEGER, total_cost FLOAT); INSERT INTO orders (43563, 734.12); INSERT INTO orders (65758, 211.15); INSERT INTO orders (23235, 1264.98); INSERT INTO orders (43563, 583.23); INSERT INTO orders (89786, 278.66); INSERT INTO orders (13253, 401.97); INSERT INTO orders (98765, 1042.23); INSERT INTO orders (23235, 699.23); INSERT INTO orders (43563, 935.35); INSERT INTO orders (88354, 375.09);
This example uses a named query in the WITH modifier to create the view sales_v. The WITH modifier includes a nonrecursive common table expression (CTE), specified as multiple_order_totals, that references the table multiple_orders, which is previously defined in the WITH clause.
CREATE VIEW sales_v AS WITH multiple_orders AS ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1 ), multiple_order_totals AS ( SELECT customer_id, SUM(total_cost) AS total_spend FROM orders WHERE customer_id IN (SELECT customer_id FROM multiple_orders) GROUP BY customer_id ) SELECT * FROM multiple_order_totals;
Then, you can query the view.
SELECT * FROM sales_v ORDER BY total_spend DESC;
The query returns this answer set:
| customer_id | total_spend |
|---|---|
| 43563 | 2.25270000000000E 003 |
| 23235 | 1.96421000000000E 003 |
This example of a WITH modifier includes a nonrecursive common table expression (CTE), specified as multiple_order_totals, that references the table multiple_orders, which is subsequently defined in the WITH clause.
CREATE VIEW sales_v AS WITH multiple_order_totals AS ( SELECT customer_id, SUM(total_cost) AS total_spend FROM orders WHERE customer_id IN (SELECT customer_id FROM multiple_orders) GROUP BY customer_id ), multiple_orders AS ( SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1 ) SELECT * FROM multiple_order_totals;
Then, you can query the view.
SELECT * FROM sales_v ORDER BY total_spend DESC;
The query returns this answer set:
| customer_id | total_spend |
|---|---|
| 43563 | 2.25270000000000E 003 |
| 23235 | 1.96421000000000E 003 |
Example: Using a Recursive Query in a WITH Modifier in CREATE VIEW
This shows the view t1_view based on the recursive query s5.
CREATE VIEW t1_view AS WITH RECURSIVE s5 (MinVersion_view) AS (SELECT a1 FROM t1 WHERE a1 > 1 UNION ALL SEL MinVersion_view FROM s5 WHERE MinVersion_view > 3), RECURSIVE s6 (MinVersion_view2) AS (SELECT a1 FROM t1 WHERE a1 = 2 UNION ALL SEL MinVersion_view2 FROM S6 WHERE MinVersion_view2 > 2) SEL * FROM s5,s6;
SEL * FROM t1_view;
| MinVersion_view | MinVersion_view2 |
|---|---|
| 3 | 2 |
| 2 | 2 |
Example: Creating a View that Defines a Self-Join with a Table
The following request creates a view that allows a personnel executive to keep track of employees who have more experience on the job than their supervisors. This request defines a self-join of the employee table. The correlation names workers and managers, created in the FROM clause, see the two temporary tables participating in the self-join.
CREATE VIEW emp_info (workername,workeryrsexp,department,
managername,manageryrsexp) AS
SELECT workers.name, workers.yrsexp, workers.deptno,
managers.name, managers.yrsexp
FROM employee AS workers, employee AS managers
WHERE workers.deptno = managers.deptno
AND managers.jobtitle IN ('Manager', 'Vice Pres')
AND workers.yrsexp > managers.yrsexp;
Example: Invoking an SQL UDF Within a View Definition
This example invokes the SQL UDF value_expression in the select list of the definition of the view named v1.
CREATE VIEW v1 (a, b, c) AS SELECT a1, test.value_expression(3,4), c1 FROM t1 WHERE a1 > b1;
The next example invokes the SQL UDF value_expression in the WHERE clause of the view named v2.
CREATE VIEW v2 (a, b, c) AS SELECT a1, b1, c1 FROM t1 WHERE test.value_expression(b1, c1) > 10;
Example: Creating a View with Aggregates
The following request illustrates the use of aggregates in a view definition. The result rows are grouped by department number and include only those rows with an average salary of $35,000 or higher.
CREATE VIEW dept_sal (deptno, minsal, maxsal, avgsal) AS SELECT deptno, MIN(salary), MAX(salary), AVG(salary) FROM employee GROUP BY deptno HAVING AVG(salary) >= 35000;
Now perform the following SELECT request using this view:
SELECT * FROM dept_sal;
The query returns the following response set:
DeptNo MinSal MaxSal AvgSal ------ ---------- --------- ---------- 600 28,600.00 45,000.00 36,650.00 300 23,000.00 65,000.00 47,666.67 700 30,000.00 45,000.00 37,666.67 500 22,000.00 56,000.00 38,285.71
The following SQL request returns the response set that follows:
SELECT deptno, minsal, minsal+10000, avgsal FROM dept_sal WHERE avgsal > (minsal + 10000); DeptNo MinSal (MinSal+10000) AvgSal ------ ---------- ----------------- ---------- 500 22,000.00 32000.00 38,285.71 300 23,000.00 33000.00 47,666.67
Example: Creating a View for Names and Job Titles Only
The following request creates a view of the employee table so that it provides access only to the names and job titles of the employees in department 300:
CREATE VIEW dept300 (name, jobtitle) AS SELECT name, jobtitle FROM employee WHERE DeptNo = 300 WITH CHECK OPTION;
The WITH CHECK OPTION prevents using this view to INSERT a row into employee through the view, or to update any row of employee for which DeptNo <> 300.