Defining and Using Views
A view is a virtual table consisting of data retrieved at execution time from one or more physical tables by means of a SELECT statement or a UNION of several SELECT statements. The SELECT can access data by specifying any combination of tables or other views. Thus a view is a stored query that provides all of the flexibility and security privileges of a physical table.
The same Selectivity value is used for a field specified in a SELECT query and the same field specified in the SELECT clause of a view. Note that a view may have a different distribution of rows than the source table. This can affect the accuracy of view field selectivity.
InterSystems SQL on InterSystems IRIS® data platform supports the ability to define and execute queries on views. All views are either updateable or read-only.
You cannot create a view on data stored in a database that is mounted read-only.
You cannot create a view on data stored in an Informix table linked through an ODBC or JDBC gateway connection. This is because InterSystems IRIS query conversion uses subqueries in the FROM clause for this type of query; Informix does not support FROM clause subqueries. See the ISQL Migration Guide for InterSystems support for Informix SQL.
Creating a View
You can define views in several ways:
-
Using the SQL CREATE VIEW command (either in a DDL script or via JDBC or ODBC).
-
Using the Management Portal Create View interface.
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.
You can determine if a view name already exists using the $SYSTEM.SQL.Schema.ViewExists()Opens in a new tab method. This method also returns the class name that projected the view. You can determine if a table name already exists using the $SYSTEM.SQL.Schema.TableExists()Opens in a new tab method.
A view can be used to create a restricted subset of a table. The following Embedded SQL example creates a view that restricts both the rows (through the WHERE clause) and columns (assuming that Sample.Person contains more than two columns) of the original table that can be accessed thorough the view:
&sql(CREATE VIEW Sample.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 "Serious SQL problem: ",SQLCODE," ",%msg,! }
SELECT * FROM Sample.VSrStaff ORDER BY Vage
The following Embedded SQL example creates a view based on the SalesPeople table, creating a new calculated value column TotalPay:
&sql(CREATE VIEW Sample.VSalesPay AS
SELECT Name,(Salary + Commission) AS TotalPay
FROM Sample.SalesPeople)
IF SQLCODE=0 {WRITE "Created a view",!}
ELSEIF SQLCODE=-201 {WRITE "View already exists",!}
ELSE {WRITE "Serious SQL problem: ",SQLCODE," ",%msg,! }
Management Portal Create View Interface
You can create a view from the Management Portal. Go to the InterSystems IRIS Management Portal. From System Explorer, select SQL. Select a namespace by clicking the name of the current namespace displayed 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:
-
Schema: You can decide to include the view within an existing schema, or create a new schema. If you opt to select an existing schema, a drop-down list of existing schemas is provided. If you opt to create a new schema, you enter a schema name. In either case, if you omit the schema, InterSystems IRIS uses the default schema name.
-
View Name: a valid view name. You cannot use the same name for a table and a view in the same schema.
-
With Check Option: the options are READONLY, LOCAL, CASCADED.
-
Grant all privilege on the view to _PUBLIC: if selected, this option gives all users execution privileges for this view. The default is to not give all users access to the view.
-
View Text: you can specify the View Text in any of the following three ways:
-
Type a SELECT statement into the View Text area.
-
Use the Query Builder to create a SELECT statement, then press OK to supply this query to the View Text area.
-
If you select a Cached Query name (for example %sqlcq.USER.cls4) on the left side of the Management Portal SQL interface, then invoke Create View, this cached query is provided to the View Text area. Note that in the View Text area you must replace host variable references (question marks) with actual values before saving the view text.
-
Views and Corresponding Classes
When you define a view, InterSystems IRIS generates a corresponding class. An SQL view name is used to generate a corresponding unique class name, following name translation rules. The Management Portal SQL interface displays Catalog Details for existing views, including this class name. Refer to Catalog Details for a View.
Altering a View
In the Management Portal SQL interface you can select an existing view to display Catalog Details for that view. The Catalog Details View Info option displays an Edit View link that provides an interface to edit the view text (the SELECT statement for the view). It also provides a drop-down list to select the With Check Option as none, READONLY, LOCAL, or CASCADED.
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 FROM clause of the view’s query contains only one table reference. This table reference must identify either an updateable base table or an updateable view.
-
The value expressions within the SELECT list of the view’s query must all be column references.
-
The view’s query must not specify GROUP BY, HAVING, or SELECT DISTINCT.
-
The view is not a class query projected as a view.
-
The view’s class does not contain the class parameter READONLY=1 (true if the view definition contains a WITH READ ONLY clause).
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, InterSystems 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:
-
WITH LOCAL CHECK OPTION means that only the WHERE clause of the view specified in the INSERT or UPDATE statement is checked.
-
WITH CASCADED CHECK OPTION (and WITH CASCADE CHECK OPTION) means that the WHERE clause of the view specified in the INSERT or UPDATE statement as well as ALL views on which that view is based are checked, regardless of the appearance or absence of other WITH LOCAL CHECK OPTION clauses in those view definitions.
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):
-
SQLCODE -136—View's WITH CHECK OPTION validation failed in INSERT.
-
SQLCODE -137—View's WITH CHECK OPTION validation failed in UPDATE.
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
InterSystems IRIS 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 Sample.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 "Serious SQL problem: ",SQLCODE," ",%msg,! }
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 Sample.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 Sample.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:
-
%VID does not perform time-to-first-row optimization. TOP optimizes to return the first row of data as quickly as possible. %VID optimizes to return the full data set as quickly as possible.
-
%VID does not perform a limited sort (which is a special optimization performed by TOP) if the query specifies sorted results. The query first sorts the full data set, then restricts the return data set using %VID. TOP is applied before sorting, so the SELECT performs a limited sort involving only a restricted subset of rows.
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
Parallel execution cannot be performed on a query that specifies a %VID, even when the %PARALLEL keyword is explicitly specified.
Listing View Properties
The INFORMATION.SCHEMA.VIEWSOpens in a new tab persistent class displays information about all views in the current namespace. It provides a number of properties including the view definition, the owner of the view, and the timestamps when the view was created and last modified. These properties also include whether the view is updateable and if so, whether it was defined with a check option.
When specified in Embedded SQL, INFORMATION.SCHEMA.VIEWSOpens in a new tab requires the #include %occInclude macro preprocessor directive. This directive is not required for Dynamic SQL.
The VIEWDEFINITIONOpens in a new tab property (SqlFieldName = VIEW_DEFINITION) returns as a string the view field names and the view’s query expression for all views in the current namespace. For example,
SELECT View_Definition FROM INFORMATION_SCHEMA.VIEWS
returns strings such as: "(vName,vAge) SELECT Name,Age FROM Sample.Person WHERE Age > 21". When issued from the Management Portal SQL Execute Query interface, returns a string with a space between the view fields list with whitespace and line breaks removed and (if necessary) an appended ellipsis (...) indicating truncated content. Otherwise, issuing this query returns a string of up to 1048576 characters for each view, with a line break between the view fields list and the query text, with the whitespace specified in the view’s query expression preserved, and (if necessary) an appended ellipsis (...) indicating truncated content.
The following example returns the view name (Table_Name field) and owner name for all views in the current namespace:
SELECT Table_Name,Owner FROM INFORMATION_SCHEMA.VIEWS
The following example returns all information for all non-system views in the current namespace:
SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE Owner != '_SYSTEM'
The INFORMATION.SCHEMA.VIEWCOLUMNUSAGEOpens in a new tab persistent class displays the names of the source table fields for each of the views in the current namespace:
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE View_Name='MyView'
You can display much of the same information as INFORMATION.SCHEMA.VIEWSOpens in a new tab for a single view using the Catalog Details tab in the Management Portal SQL Interface. The Catalog Details for a view include the definition of each view field (data type, max length, minval/maxval, etc.), details that are not provided by the INFORMATION.SCHEMA view classes. The Catalog Details View Info display also provides an option to edit the view definition.
Listing View Dependencies
The INFORMATION.SCHEMA.VIEWTABLEUSAGEOpens in a new tab persistent class displays all views in the current namespace and the tables they depend on. This is shown in the following example:
SELECT View_Schema,View_Name,Table_Schema,Table_Name FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
You can invoke the %Library.SQLCatalog.SQLViewDependsOn class query to list the tables that a specified view depends upon. You specify schema.viewname to this class query. If you specify only viewname, it uses the system-wide default schema name. The caller must have privileges for the specified view to execute this class query. This is shown in the following example:
SET statemt=##class(%SQL.Statement).%New()
SET cqStatus=statemt.%PrepareClassQuery("%Library.SQLCatalog","SQLViewDependsOn")
IF cqStatus'=1 {WRITE "%PrepareClassQuery failed:" DO $System.Status.DisplayError(cqStatus) QUIT}
SET rset=statemt.%Execute("vschema.vname")
DO rset.%Display()
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.