Caché SQL Reference
CREATE VIEW
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Creates a view.
Synopsis
CREATE [OR REPLACE] VIEW view-name [(column-commalist)]
       AS select-statement 
       [ WITH READ ONLY  |  WITH [level] CHECK OPTION ]
Arguments
view-name The name for the view being created. A valid identifier, subject to the same additional naming restrictions as a table name. A view name can be qualified (schema.viewname) or unqualified. Note that you cannot use the same name for a table and a view in the same schema.
column-commalist Optional — The column names that compose the view, one or more valid identifiers. If specified, this list is enclosed in parentheses and items in the list are separated by commas.
AS select-statement A SELECT statement that defines the view.
WITH READ ONLY Optional — Specifies that no insert, update, or delete operations can be performed through this view upon the table on which the view is based. The default is to permit these operations through a view, subject to the constraints described below.
WITH level CHECK OPTION Optional — Specifies how insert, update, or delete operations are performed through this view upon the table on which the view is based. The level can be the keywords LOCAL or CASCADED. If no level is specified, the WITH CHECK OPTION default is CASCADED.
Description
The CREATE VIEW command defines the content of a view. The SELECT statement that defines the view can reference more than one table and can reference other views.
Privileges
To select from the objects referenced in the SELECT clause of a view being created, it is necessary to have the appropriate privileges:
You can determine if the current user has these table-level privileges by invoking the %CHECKPRIV command. You can determine if a specified user has these table-level privileges by invoking the $SYSTEM.SQL.CheckPriv() method. For privilege assignment, refer to the GRANT command.
The creator (owner) of a view is granted the %ALTER privilege WITH GRANT OPTION when the view is compiled.
The CREATE VIEW command is a privileged operation. Prior to using CREATE VIEW it is necessary for your process to have %CREATE_VIEW privileges. Failing to do so results in an SQLCODE -99 error (Privilege Violation). You can use the GRANT command to assign %CREATE_VIEW privileges, if you hold appropriate granting privileges.
In embedded SQL, you can use the $SYSTEM.Security.Login() method to log in as a user with appropriate privileges:
   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )
You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, refer to %SYSTEM.Security in the InterSystems Class Reference.
%CREATE_VIEW privileges are assigned using the GRANT command, which requires you to assign this privilege to a user or role. This requirement is configurable:
The default is “Yes” (1). When “Yes”, a user can only perform actions on a table or view for which that user has been granted privilege. This is the recommended setting for this option.
If this option is set to “No” (0), SQL Security is disabled for any new process started after changing this setting. This means privilege-based table/view security is suppressed. You can create a table without specifying a user. In this case, Dynamic SQL assigns “_SYSTEM” as user, and embedded SQL assigns "" (the empty string) as user. Any user can perform actions on a table or view even if that user has no privileges to do so.
View Naming Conventions
A view name has the same naming conventions as a table name, and shares the same name set. Therefore, you cannot use the same name for a table and a view in the same schema. Attempting to do so results in an SQLCODE -201 error. A class that projects a table definition and a view definition with the same name also generates an SQLCODE -201 error.
View names follow identifier conventions, subject to the restrictions below. By default, view names are simple identifiers. A view name should not exceed 128 characters. View names are not case-sensitive. For further details see the “Identifiers” chapter of Using Caché SQL.
Caché uses the view name to generate a corresponding class name. A class name contains only alphanumeric characters (letters and numbers) and must be unique within the first 96 characters. To generate this class name, Caché first strips punctuation characters from the view name, and then generates a identifier that is unique within the first 96 characters, substituting an integer (beginning with 0) for the final character when needed to create a unique class name. Caché generates a unique class name from a valid view name, but this name generation imposes the following restrictions on the naming of views:
A view name can be qualified or unqualified. A qualified view name has the following syntax:
schema.viewname
A qualified view name can specify an existing schema or a new schema. If it specifies a new schema, the system creates that schema.
An unqualified view name takes as default the schema SQLUser (or a configured system default schema name). Caché uses the schema name to generate a corresponding class package name. SQLUser generates the package name User. For further details on schema names and default value, see the “Defining Tables” chapter in Using Caché SQL.
Existing View
To determine if a specified view already exists in the current namespace, use the $SYSTEM.SQL.ViewExists() method.
What happens when you try to create a view that has the same name as an existing view depends on the optional OR REPLACE keyword and on the configuration setting.
With OR REPLACE
If you specify CREATE OR REPLACE VIEW, the existing view is replaced by the view definition specified in the SELECT clause and any specified WITH READ ONLY or WITH CHECK OPTION. This is the same as performing the corresponding ALTER VIEW statement. Any privileges that had been granted to the original view remain.
This keyword phrase provides no functionality not available through ALTER VIEW. It is provided for compatibility with Oracle SQL code.
Without OR REPLACE
If you specify CREATE VIEW, Caché, by default, rejects an attempt to create a view with the name of an existing view and issues an SQLCODE -201 error. This behavior is configurable. Note that changing this behavior affects both CREATE VIEW and CREATE TABLE. You can configure this behavior as follows:
The default is “No” (0). This is the recommended setting for this option. If this option is set to “Yes” (1), Caché deletes the class definition associated with the view and then recreates it. This is much the same as performing a DROP VIEW and then performing a CREATE VIEW.
Column Names
A view can optionally include a column-commalist list of column names, enclosed in parentheses. These column names, if specified, are the names used to access and display the data for the columns when using that view. If the list of column names is omitted, the column names of the SELECT source table are used. If you omit the list of column names, you must also omit the parentheses.
If you specify the column-commalist, the following apply:
The following example shows a CREATE VIEW with matching lists of view columns and query columns:
CREATE VIEW MyView (ViewCol1, ViewCol2, ViewCol3) AS
     SELECT TableCol1, TableCol2, TableCol3 
     FROM MyTable
Alternatively, you can use the AS keyword in the query to specify the view columns as query column / view column pairs, as shown in the following example:
CREATE VIEW MyView AS 
  SELECT TableCol1 AS ViewCol1,
     TableCol2 AS ViewCol2,
     TableCol3 AS ViewCol3
     FROM MyTable
SELECT Clause Considerations
A view does not have to be a simple subset of the rows and columns of one particular table. A view can be created using more than one table or other views with a SELECT clause of any complexity. There are, however, a few restrictions on the SELECT clauses in a view definition. A CREATE VIEW statement:
CREATE VIEW can contain a UNION statement to select columns from the union of two tables. You can specify a UNION as shown in the following embedded SQL example:
  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
  &sql(CREATE VIEW MyView (vname,vstate) AS
  SELECT t1.name,t1.home_state
    FROM Sample.Person AS t1
  UNION
  SELECT t2.name,t2.office_state
    FROM Sample.Employee AS t2)
  IF SQLCODE=0 { WRITE !,"Created view" }
  ELSE { WRITE "CREATE VIEW error SQLCODE=",SQLCODE }
 
Note that an unqualified view name, such as in the above example, defaults to the system-wide default SQL schema name (in this case, User.MyView), even though the tables referenced by the view are in the Sample schema. Thus it is usually a good practice to always qualify a view name to ensure that it is stored with its associated table(s).
View ID: %vid
When data is accessed through a view, Caché assigns a sequential integer view ID (%vid) to each row returned by that view. Like table row ID numbers, these view row ID numbers are system-assigned, unique, non-zero, non-null, and non-modifiable. This %vid is usually invisible. Unlike a table row ID, it is not displayed when using asterisk syntax; it is only displayed when explicitly specified in the SELECT. The %vid can be used to further restrict the number of rows returned by a SELECT accessing a view. For further details on using %vid, refer to the Defining and Using Views chapter of Using Caché SQL.
Updating Through Views
A view can be used to update the tables on which the view is based. You can INSERT new rows through the view, UPDATE data in rows seen through the view, and DELETE rows seen through the view. INSERT, UPDATE, and DELETE statements can be issued for a view, if the CREATE VIEW statement specified this ability. To allow updating through a view, specify WITH CHECK OPTION (the default) when defining the view.
To prevent updating through a view, specify WITH READ ONLY. Attempting an INSERT, UPDATE, or DELETE through a view created WITH READ ONLY generates an SQLCODE -35 error.
In order to update through a view, you must have the appropriate privileges for the table or view to be updated, as specified by the GRANT command.
Updating through views is subject to the following restrictions:
The WITH CHECK OPTION clause causes an insert or update operation to validate the resulting row against the WHERE clause of the view definition. This ensures that the inserted or modified row is part of the derived view table. There are two available check options:
WITH LOCAL CHECK OPTION — only the WHERE clause of the view specified in the INSERT or UPDATE statement is checked.
WITH CASCADED CHECK OPTION — the WHERE clause of the view specified in the INSERT or UPDATE statement and all underlying views are checked. This overrides any WITH LOCAL CHECK OPTION clauses in these underlying views. CASCADED is the default and is recommended for all updateable views.
If an INSERT operation fails WITH CHECK OPTION validation (as defined above), Caché issues an SQLCODE -136 error.
If an UPDATE operation fails WITH CHECK OPTION validation (as defined above), Caché issues an SQLCODE -137 error.
Examples
The following example creates a view named "CityPhoneBook" from the PhoneBook table:
CREATE VIEW CityPhoneBook AS
     SELECT Name FROM PhoneBook WHERE City='Boston'
The following example creates a view named "GuideHistory" from the Guides table. It lists all titles (from the Title column) and whether or not the person is retired:
CREATE VIEW GuideHistory AS
     SELECT Guides, Title, Retired, Date_Retired 
     FROM Guides
The following Embedded SQL example creates the table MyTest, and then creates a view for this table, MyTestView, which selects one field from MyTest:
  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
  &sql(DROP TABLE Sample.MyTest)
  &sql(DROP VIEW Sample.MyTestView)
CreateTable
  &sql(CREATE TABLE Sample.MyTest (
     TestNum     INT NOT NULL,
     FirstWord   CHAR (30) NOT NULL,
     LastWord    CHAR (30) NOT NULL,
     CONSTRAINT MyTestPK PRIMARY KEY (TestNum))
  )
  IF SQLCODE=0 { WRITE !,"Created table" }
  ELSE { WRITE "CREATE TABLE error SQLCODE=",SQLCODE }
CreateView
  &sql(CREATE VIEW Sample.MyTestView AS
     SELECT FirstWord FROM Sample.MyTest
     WITH CASCADED CHECK OPTION)
  IF SQLCODE=0 { WRITE !,"Created view" }
  ELSE { WRITE "CREATE VIEW error SQLCODE=",SQLCODE }
 
The following Embedded SQL example creates a view MyTestView, which selects two fields from MyTest. The SELECT query for this view contains a TOP clause and an ORDER BY clause:
  DO $SYSTEM.Security.Login("_SYSTEM","SYS")
  &sql(DROP TABLE Sample.MyTest)
  &sql(DROP VIEW Sample.MyTestView)
CreateTable
  &sql(CREATE TABLE Sample.MyTest (
     TestNum     INT NOT NULL,
     FirstWord   CHAR (30) NOT NULL,
     LastWord    CHAR (30) NOT NULL,
     CONSTRAINT MyTestPK PRIMARY KEY (TestNum))
  )
  IF SQLCODE=0 { WRITE !,"Created table" }
  ELSE { WRITE "CREATE TABLE error SQLCODE=",SQLCODE }
CreateView
  &sql(CREATE VIEW Sample.MyTestView AS
     SELECT TOP ALL FirstWord,LastWord FROM Sample.MyTest
     ORDER BY LastWord)
  IF SQLCODE=0 { WRITE !,"Created view" }
  ELSE { WRITE "CREATE VIEW error SQLCODE=",SQLCODE }
 
The following example creates a view named "StaffWorksDesign" from three tables (Proj, Staff, and Works). The columns Name, Cost, and Project provide the data.
CREATE VIEW StaffWorksDesign (Name,Cost,Project)
     AS SELECT EmpName,Hours*2*Grade,PName
     FROM Proj,Staff,Works 
     WHERE Staff.EmpNum=Works.EmpNum 
          AND Works.PNum=Proj.PNum AND PType='Design'
The following example creates a view named “v_3” by selecting from b.table2 and a.table1 using a UNION:
CREATE VIEW v_3(fvarchar)
     AS SELECT DISTINCT * 
     FROM
       (SELECT fVARCHAR2 FROM b.table2 
        UNION ALL
        SELECT fVARCHAR1 FROM a.table1) 
See Also