Creating Views

A view is a table derived by placing a "window" over one or more tables to let users or programs see only certain data. Views are useful for limiting data visibility; they are also useful for pulling together various tables to simplify complex SELECT statements. Refer to "Designing Views" in the "Logical Design" chapter of this guide.

A view is actually a SELECT statement that is permanently stored as a section in the system catalog. You can store a SELECT statement that extracts part of a table, or, as in the sample database, you can store a SELECT that joins two or more tables together. Refer to the "Maintenance" chapter for further explanation of sections.

The following is the basic syntax for creating a view:

Refer to the ALLBASE/SQL Reference Manual for complete syntax and semantics.

Owner names for views are established the same way they are for tables.

View names must conform to the same conventions as table names. The view name is stored in both SYSTEM.TABLE and SYSTEM.SECTION. The view definition is stored in SYSTEM.VIEWDEF.

Constraint information is stored in the SYSTEM.CONSTRAINT table.

The section containing the SELECT statement for the view is stored in one of three places:

If you omit the column names, they are derived from the column names of the table entered as part of the query expression, which is a SELECT that defines the contents of the view.

The CHECK OPTION clause defines a check constraint that enforces the condition defining the view when changes are made through the view. When the table from which the view is derived is modified through the view, this check constraint must be satisfied. The view must also meet the requirements for updatability.

	NOTE: The query expression that defines a view cannot include an ORDER BY clause. If you need to use ORDER BY, use it in the SELECT on the view.

The following statement creates a view that allows authorized users to see the relationship between the PartNumber column, the VendPartNumber, VendorName, and ListPrice:

   isql=> CREATE VIEW PurchDB.PartInfo

   >(PartNumber,PartName,VendorNumber,VendorName,

   > VendorPartNumber,ListPrice,Quantity)  AS

   > SELECT PurchDB.SupplyPrice.PartNumber,

   > PurchDB.Parts.PartName,

   > PurchDB.SupplyPrice.VendorNumber,

   > PurchDB.Vendors.VendorName,

   > PurchDB.Supplyprice.VendPartNumber,

   > PurchDB.SupplyPrice.UnitPrice,

   > PurchDB.SupplyPrice.DiscountQty

   > FROM PurchDB.Parts,

   > PurchDB.SupplyPrice,

   > PurchDB.Vendors

   > WHERE PurchDB.SupplyPrice.PartNumber =

   > PurchDB.Parts.PartNumber

   > AND PurchDB.SupplyPrice.VendorNumber =

   > PurchDB.Vendors.VendorNumber;

Note that the SELECT statement joins three tables. Views can facilitate query processing and can also be used to limit data visibility.

A check constraint is defined on an updatable view, as follows:

   CREATE VIEW RecDB.EventView

               (Event,

               Date) AS

        SELECT RecDB.Event,

               RecDB.Date

          FROM RecDB.Events

         WHERE Date >= '1992-01-01'

   WITH CHECK OPTION 

See the section "Controlling Table Access with Views," below.