CREATE VIEW

A View is an imaginary table that appears to contain data selected from existing tables. The view can rename and rearrange columns, omit unwanted columns or rows, define columns by expressions, group results, and combine more than one table. Views make it possible to view data that exists in parts of one or more tables. No data actually exists in a view.

Any SELECT statement that does not contain ORDER BY can be used as the basis of a view; the selected columns and rows become the columns and rows of the view. In the following example, NAME, ID, and JOB from Q.STAFF become the columns of D42. The column names for D42 are LAST NAME, EMP. ID, and JOB.

CREATE VIEW D42
 ("LAST NAME", "EMP. ID", JOB)
   AS SELECT NAME, ID, JOB
FROM Q.STAFF
WHERE DEPT = 42

Issue the command:

DISPLAY TABLE D42

to display this view:

LAST NAME EMP. ID  JOB
--------- -------  -----
KOONITZ        90  SALES
PLOTZ         100  MGR
YAMAGUCHI     130  CLERK
SCOUTTEN      200  CLERK

There are two main reasons for using a view:

• To simplify writing a query to use its data, as in the example above.
• To prevent access to data. Anyone using the view D42, defined above, cannot see salary data.

Use a view by its name, like you use a table. You can select from it, writing the same kind of SELECT statement as if it were a table. For example, run this query:

SELECT * FROM D42
WHERE JOB='CLERK'

With a few restrictions, you can insert, update, and delete rows in a view. Corresponding changes are made to the tables the view is based on.

There are a few things you cannot do with a view:

• You cannot insert, update, or delete using a view if the view contains:
  • Data from more than one table.
  • A column defined by one of the column functions, for example, SUM(SALARY).
  • Data selected by the DISTINCT or GROUP BY keywords.
• You cannot update or insert (you can delete) if the view contains a column defined by an expression (like SALARY/12).
• You cannot use UNION when creating a view.
• You cannot join a view that was created using a GROUP BY to another table or view.