CREATE/REPLACE VIEW Examples | Teradata Vantage - CREATE VIEW and REPLACE VIEW Examples - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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;
This statement displays the content of the view t1_view.
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.