HP 3000 Manuals

ALLBASE/SQL Database Administration Guide

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:

CREATE VIEW [Owner.]ViewName [( ColumnName [,...])]  AS QueryExpression 

[IN DBEFileSetName]  [WITH CHECK OPTION [CONSTRAINT ConstraintID]]

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:

   *   the DBEFileSet you specify in the CREATE VIEW statement
   *   the default section space DBEFileSet you specify with SET DEFAULT
       DBEFILESET
   *   the system DBEFileSet

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.

MPE/iX 5.5 Documentation