Defining a Database Using SQL › Creating a View

Creating a View

You create a view by issuing the CREATE VIEW statement and specifying the view column names, the table(s) and column(s) from which the view is derived, and data restrictions, if any.

Things You Can Specify

1. Name of a view, using a schema qualifier unless you have specified a default schema name in the SET SESSION statement

   Note: For more information about session management statements, see the CA IDMS SQL Reference Guide.

2. A column list if there are computations or duplicate column names in the result table of the view definition
3. An appropriate SQL select statement

   Note: For a complete discussion of SQL select statements, see the CA IDMS SQL Reference Guide.

4. A check option to ensure that only data values that satisfy the SELECT statement are inserted or updated through the view.
5. Physical attributes, including a synchronization stamp.

Specifying Physical Attributes

When creating a view, you can specify physical attributes that are normally generated automatically. Specifying explicit values for this information allows you to create and maintain views that have identical attributes and can therefore be accessed through a single schema definition.

Care should be exercised when specifying a specific timestamp, since its purpose is to enable the detection of discrepancies between a view and its definition. If explicitly specified, the timestamp should always be set to a new value following a definitional change so that the change is detectable to the run time system.

Considerations

• You cannot define an index on a view
• Updatable views are syntactically valid anywhere in SQL DML statements that tables are; a view is updatable when the SELECT statement references only one table and when the view projects no computed values
• If the WHERE clause of the SELECT statement contains a subquery, you cannot use the check option
• Avoid using an asterisk (*) in the SELECT statement of your view. If a column is added to the underlying table, the view becomes invalid and must be dropped and recreated.

Example - Single Table View

In the following example, a simple view is defined on the EMPLOYEE table.

create view prod.emp_home_phone
   as select emp_id, emp_lname, emp_fname, phone
      from prod.employee;

Example - Updatable View

In the following example, a view is defined with the check option to restrict rows that can be updated and inserted. Using the view, the value of DEPT_ID cannot be changed to something other than 'SALES', and new rows must have a DEPT_ID of 'SALES'.

create view hr.sales_employee
   as select emp_id, emp_lname, emp_fname, dept_id, emp_ssno
      from prod.employee
      where dept_id = 'SALES'
   with check option;

Example - Nonupdatable View

In the following example, a view is defined with three columns derived from two tables. Since the third column includes both aggregate functions and an arithmetic operation, the CREATE VIEW statement must specify names for the columns in the view.

This view is nonupdatable because the SELECT references more than one table and because the view projects computed values.

create view prod.emp_vacation
   (emp_id, dept_id, vac_time)
   as select e.emp_id, dept_id, sum(vac_accrued) - sum(vac_taken)
      from prod.employee e, prod.benefits b
      where e.emp_id = b.emp_id
      group by dept_id, e.emp_id;

Note: For more information about SQL syntax, see the CA IDMS SQL Reference Guide.