Using Caché SQL
Defining and Using Views
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

A view is a virtual table consisting of data retrieved from one or more physical tables by means of a SELECT statement or a UNION of several SELECT statements. Thus a view is a defined way of viewing existing table data.

Caché SQL supports the ability to define and execute queries on views. All views are either updateable or read-only, as described later in this chapter.
Note:
You cannot create views on data that is stored in a database that is mounted read-only.
Creating a View
You can define views in several ways:
A view name may be unqualified or qualified. An unqualified view name is a simple identifier: MyView. A qualified view name consists of two simple identifiers, a schema name and a view name, separated by a period: MySchema.MyView. View names and table names follow the same naming conventions and perform the same schema name resolution for unqualified names. A view and a table in the same schema cannot have the same name.
A view can be used to create a restricted subset of a table. For example, the following view restricts both the rows and columns of the original table that can be accessed thorough the view:
   &sql(CREATE VIEW VSrStaff 
        AS SELECT Name AS Vname,Age AS Vage
        FROM Sample.Person WHERE Age>75)
   IF SQLCODE=0 {WRITE "Created a view",!}
   ELSEIF SQLCODE=-201 {WRITE "View already exists",!}
   ELSE {WRITE "We have a problem: ",SQLCODE,! }
 
SELECT * FROM VSrStaff ORDER BY Vage
 
The following example creates a view based on all of the rows of the SalesPeople table, creating a new calculated value column TotalPay:
CREATE VIEW VSalesPay AS
    SELECT Name,(Salary + Commission) AS TotalPay
    FROM Sample.SalesPeople
Management Portal Create View Interface
You can create a view from the Management Portal. Go to the Caché Management Portal. From System Explorer, select SQL ([Home] > [SQL]). Select a namespace with the Switch option at the top of the page; this displays the list of available namespaces. Once you have selected a namespace, click the Actions drop-down list and select Create View.
This displays the Create a View window with the following fields:
Updateable Views
An updateable view is one on which you can perform INSERT, UPDATE, and DELETE operations. A view is considered updateable only if the following conditions are true:
The WITH CHECK Option
In order to prevent an INSERT or UPDATE operation on a view which would result in a row in the underlying base table which is not part of the derived view table, Caché SQL supports the WITH CHECK OPTION clause within a View definition. This clause can only be used with updateable views.
The WITH CHECK OPTION clause specifies that any INSERT or UPDATE operations on an updateable view must validate the resulting row against the WHERE clause of the view definition to make sure the inserted or modified row will be part of the derived view table.
For example, the following DDL statement defines an updateable GoodStudent view containing all Students with a high GPA (grade point average):
CREATE VIEW GoodStudent AS
    SELECT Name, GPA
      FROM Student
        WHERE GPA > 3.0
    WITH CHECK OPTION
Because the view contains a WITH CHECK OPTION, any attempt to INSERT or UPDATE a row in the GoodStudent view with a GPA value of 3.0 or less will fail (such a row would not represent a “good student”).
There are two flavors of WITH CHECK OPTION:
The default is CASCADED if just WITH CHECK OPTION is specified.
During an UPDATE or INSERT, the WITH CHECK OPTION conditions are checked after all default values and triggered computed fields have been calculated for the underlying table’s fields and before the regular table’s validation (required fields, data type validation, constraints, and so on).
After the WITH CHECK OPTION validation passes, the INSERT or UPDATE operation continues as if the INSERT or UPDATE was performed on the base table itself. All constraints are checked, triggers pulled, and so on.
If the %NOCHECK option is specified on the INSERT or UPDATE statement, the WITH CHECK OPTION validation is not checked.
There are two SQLCODE values related to the WITH CHECK OPTION validation (the INSERT/UPDATE would have resulted in a row not existing in the derived view table):
Read-only Views
A read-only view is one on which you cannot perform INSERT, UPDATE, and DELETE operations. Any view that does not meet the criteria for updateable views is a read-only view.
A view definition may specify a WITH READ ONLY clause to force it to be a read-only view.
If you attempt to compile/prepare an INSERT, UPDATE, or DELETE statement against a read-only view an SQLCODE -35 error is generated.
View ID: %VID
Caché assigns an integer view ID (%VID) to each row returned by a view or by a FROM clause subquery. Like table row ID numbers, these view row ID numbers are system-assigned, unique, non-null, non-zero, and non-modifiable. This %VID is commonly invisible to the user, and is only returned when explicitly specified. It is returned as data type INTEGER. Because %VID values are sequential integers, they are far more meaningful if the view returns ordered data; a view can only use an ORDER BY clause when it is paired with a TOP clause. The following Embedded SQL example creates a view named VSrStaff:
   &sql(CREATE VIEW VSrStaff 
        AS SELECT TOP ALL Name AS Vname,Age AS Vage
        FROM Sample.Person WHERE Age>75
        ORDER BY Name)
   IF SQLCODE=0 {WRITE "Created a view",!}
   ELSEIF SQLCODE=-201 {WRITE "View already exists",!}
   ELSE {WRITE "We have a problem: ",SQLCODE,! }
 
The following example returns all of the data defined by the VSrStaff view (using SELECT *) and also specifies that the view ID for each row should be returned. Unlike the table row ID, the view row ID is not displayed when using asterisk syntax; it is only displayed when explicitly specified in the SELECT:
SELECT *,%VID AS ViewID FROM VSrStaff
 
The %VID can be used to further restrict the number of rows returned by a SELECT from a view, as shown in the following example:
SELECT *,%VID AS ViewID FROM VSrStaff WHERE %VID BETWEEN 5 AND 10
 
Thus %VID can be used instead of TOP (or in addition to TOP) to restrict the number of rows returned by a query. Generally, a TOP clause is used to return a small subset of the data records; %VID is used to return most or all of the data records, returning records in small subsets. This feature may be useful, especially for porting Oracle queries (%VID maps easily to Oracle ROWNUM). However, the user should be aware of some performance limitations in using %VID, as compared to TOP:
To preserve time to first row optimization and limited sort optimization, you can use a FROM clause subquery with a combination of TOP and %VID. Specify the upper bound (in this case, 10) in the FROM subquery as the value of TOP, rather than using TOP ALL. Specify the lower bound (in this case, >4) in the WHERE clause with %VID. The following example uses this strategy to return the same results as the previous view query:
SELECT *,%VID AS SubQueryID
   FROM (SELECT TOP 10 Name,Age 
         FROM Sample.Person
         WHERE Age > 75
         ORDER BY Name)
   WHERE %VID > 4
 
Listing View Dependencies
You can invoke the %Library.SQLCatalog.SQLViewDependsOn class query to list the tables that the view depends upon. You specify schema.viewname to this class query. If you specify only viewname, it uses the default schema. The caller must have privileges for the specified view to execute this class query.
This SQLViewDependsOn query lists the tables that the view depends upon, listing the table schema followed by the table name. If the caller does not have privileges for a table that the view depends upon, that table and its schema are listed as <NOT PRIVILEGED>. This allows a caller without table privileges to determine how many tables the view depends upon, but not the names of the tables.