Skip to main content

CREATE VIEW (SQL)

Creates a view.

Synopsis

CREATE [OR REPLACE] VIEW view-name [(column-commalist)]
  AS select-statement
  [ WITH READ ONLY | WITH [level] CHECK OPTION ] 

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

The CREATE VIEW command is a privileged operation. The user must have %CREATE_VIEW administrative privilege to execute CREATE VIEW. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %CREATE_VIEW privileges. You can use the GRANT command to assign %CREATE_VIEW privileges, if you hold appropriate granting privileges.

To select from the objects referenced in the SELECT clause of a view being created, it is necessary to have the appropriate privileges:

  • When creating a view using Dynamic SQL or via a database driver, you must have SELECT privileges on all the columns selected from the underlying tables (or views) referenced by the view. If you do not have SELECT privilege for a specified table (or view) the CREATE VIEW command will not execute.

    However, when compiling a class that projects a defined view, these SELECT privileges are not enforced on the columns selected from the underlying tables (or views) referenced by the view. For example, if you create a view using a privileged routine (that has these SELECT privileges), you can later compile the view class, because you are the owner of the view, regardless of whether you have SELECT privileges for the tables referenced by the view.

  • To receive SELECT privilege WITH GRANT OPTION for a view, you must have WITH GRANT OPTION for every table (or view) referenced by the view.

  • To receive INSERT, UPDATE, DELETE, or REFERENCES privilege for a view, you must have the same privilege for every table (or view) referenced by the view. To receive WITH GRANT OPTION for any of these privileges, you must hold the privilege WITH GRANT OPTION on the underlying tables.

  • If the view is specified WITH READ ONLY, the view is not granted INSERT, UPDATE, or DELETE privileges, regardless of the privileges you hold for the underlying tables. If the view is later redefined as read/write, these privileges are added when the class projecting the view is recompiled.

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.Security.CheckPrivilege()Opens in a new tab 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.

In embedded SQL, you can use the $SYSTEM.Security.Login()Opens in a new tab 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, see %SYSTEM.SecurityOpens in a new tab.

%CREATE_VIEW privileges are assigned using the GRANT command, which requires you to assign this privilege to a user or role. By default, CREATE VIEW security privileges are enforced. This privileges requirement is configurable system-wide using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method SET status=$SYSTEM.SQL.Util.SetOption("SQLSecurity",0,.oldval); to determine the current setting, call the $SYSTEM.SQL.CurrentSettings()Opens in a new tab method, which displays an SQL security enabled setting.

The default is 1 (enabled). When SQL Security is enabled, 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 method is set to 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. To determine if a table already exists in the current namespace, use the $SYSTEM.SQL.Schema.TableExists("schema.tname")Opens in a new tab method. 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.

InterSystems IRIS 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, InterSystems IRIS 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. InterSystems IRIS 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 must include at least one letter. Either the first character of the view name or the first character after initial punctuation characters must be a letter.

  • InterSystems IRIS supports 16-bit (wide) characters for view names. A character is a valid letter if it passes the $ZNAME test.

  • If the first character of the view name is a punctuation character, the second character cannot be a number. This results in an SQLCODE -400 error, with a %msg value of “ERROR #5053: Class name 'schema.name' is invalid” (without the punctuation character). For example, specifying the view name %7A generates the %msg “ERROR #5053: Class name 'User.7A' is invalid”.

  • Because generated class names do not include punctuation characters, it is not advisable (though possible) to create a view name that differs from an existing view or table name only in its punctuation characters. In this case, InterSystems IRIS substitutes an integer (beginning with 0) for the final character of the name to create a unique class name.

  • A view name may be much longer than 96 characters, but view names that differ in their first 96 alphanumeric characters are much easier to work with.

A view name can be qualified or unqualified.

A qualified view name (schema.viewname) can specify an existing schema or a new schema. If it specifies a new schema, the system creates that schema.

An unqualified view name (viewname) takes the default schema name.

Existing View

To determine if a specified view already exists in the current namespace, use the $SYSTEM.SQL.Schema.ViewExists("schema.vname")Opens in a new tab 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. Ownership of the view transfers to the user who executes the CREATE OR REPLACE VIEW statement.

This keyword phrase provides no functionality not available through ALTER VIEW. It is provided for compatibility with Oracle SQL code.

Without OR REPLACE

By default, if you specify CREATE VIEW, InterSystems IRIS rejects an attempt to create a view with the name of an existing view and issues an SQLCODE -201 error. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL CREATE TABLE or CREATE VIEW for existing table or view setting. The default is 0 (No), which is the recommended setting. If this option is set to 1 (Yes), InterSystems IRIS deletes the class definition associated with the view and then recreates it. This is the same as performing a DROP VIEW and then performing a CREATE VIEW. Note that this setting affects both CREATE VIEW and CREATE TABLE.

From the Management Portal, System Administration, Configuration, SQL and Object Settings, SQL you can set this option (and other similar create, alter, and drop options) system-wide by selecting the Ignore redundant DDL statements check box.

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 you omit the column-commalist, the following apply:

  • The column names of the SELECT source table are used to access and display the data when using the view.

  • If any of the SELECT source table column names have column aliases, the column aliases are the names used to access and display the data when using the view.

  • If the SELECT source table column names have table aliases, the table aliases are not used in the names used to access and display the data when using the view.

If you omit the list of column names, you must also omit the parentheses.

If you specify the column-commalist, the following apply:

  • A column name list must specify the enclosing parentheses, even when specifying a single field. You must separate multiple column names with commas. Whitespace and comments are permitted within a column-commalist.

  • The number of column names must correspond to the number of columns specified in the SELECT statement. Mismatch between the number of view columns and query columns results in an SQLCODE -142 error at compile time.

  • The names of column names must be valid identifiers. They may be different names than the SELECT column names, the same names as the SELECT column names, or a combination of both. The specified order of the view column names corresponds to the order of the SELECT column names. Because it is possible to assign a view column the name of an unrelated SELECT column, you must exercise caution when assigning view column names.

  • A column name must be unique. Specifying a duplicate column name results in an SQLCODE -97 error. Column names are converted to corresponding class property names by stripping out punctuation characters; column names that differ only in punctuation characters are permitted, but discouraged.

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 Columns and View Columns

  • Data from multiple SELECT columns can be concatenated into a single view column. For example:

    CREATE VIEW MyView (fullname) AS SELECT firstname||' '||lastname FROM MyTable
  • Multiple view columns can refer to the same SELECT column. For example:

    CREATE VIEW MyView (lname,surname) AS SELECT lastname,lastname 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 a SELECT clause of any complexity, specifying any combination of tables or views. There are, however, a few restrictions on the SELECT clause of a view definition:

  • Can only include an ORDER BY clause if this clause is paired with a TOP clause. If you wish to include all of the rows in the view, you can use a TOP ALL clause. You can include a TOP clause without an ORDER BY clause. However, if you include an ORDER BY clause without a TOP clause, an SQLCODE -143 error is generated. If you project an SQL view from a view class, the query of which contains an ORDER BY clause, the ORDER BY clause is ignored in the view projection.

  • Cannot contain host variables. If you attempt to reference a host variable in the SELECT clause, the system generates an SQLCODE -148 error.

  • Cannot include the INTO keyword. A view that specifies a SELECT with an INTO clause can be created, but execution of this view fails with an SQLCODE -25 error.

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 example:

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

Note that an unqualified view name, such as in the above example, defaults to the default schema name (for example, the initial schema default SQLUser.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, InterSystems IRIS 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 Defining and Using Views.

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 (viewname). An unqualified view name takes the default schema name. Note that you cannot use the same name for a table and a view in the same schema.

column-commalist

An optional argument. 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

An optional argument specifying 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

An optional argument that 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.

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.

Note:

If the view is based on a sharded table, you cannot INSERT, UPDATE, or DELETE through a view WITH CHECK OPTION. Attempting to do so results in an SQLCODE -35 with the %msg INSERT/UPDATE/DELETE not allowed for view (sample.myview) based on sharded table with check option conditions.

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 view cannot be a class query projected as a view.

  • The view’s class cannot contain the class parameter READONLY=1.

  • The view’s SELECT statement cannot contain a DISTINCT, TOP, GROUP BY, or HAVING clause, or be part of a UNION.

  • The view’s SELECT statement cannot contain a subquery.

  • The view’s SELECT statement can only list value expressions that are column references.

  • The view’s SELECT statement can have only one table reference; it cannot contain FROM clause JOIN syntax or arrow syntax in the select-list or WHERE clause. The table reference must specify either an updateable table or an updateable view.

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. WITH CASCADED CHECK OPTION is recommended for all updateable views.

    If you specify WITH CHECK OPTION, the check option defaults to CASCADED. The keyword CASCADE is a synonym for CASCADED.

If an INSERT operation fails WITH CHECK OPTION validation (as defined above), InterSystems IRIS issues an SQLCODE -136 error.

If an UPDATE operation fails WITH CHECK OPTION validation (as defined above), InterSystems IRIS 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 example creates the table MyTest, and then creates a view for this table, MyTestView, which selects one field from MyTest:

CREATE TABLE Sample.MyTest (
     TestNum     INT NOT NULL,
     FirstWord   CHAR (30) NOT NULL,
     LastWord    CHAR (30) NOT NULL,
     CONSTRAINT MyTestPK PRIMARY KEY (TestNum))

CREATE VIEW Sample.MyTestView AS
     SELECT FirstWord FROM Sample.MyTest
     WITH CASCADED CHECK OPTION

The following 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:

CREATE TABLE Sample.MyTest (
     TestNum     INT NOT NULL,
     FirstWord   CHAR (30) NOT NULL,
     LastWord    CHAR (30) NOT NULL,
     CONSTRAINT MyTestPK PRIMARY KEY (TestNum))

CREATE VIEW Sample.MyTestView AS
     SELECT TOP ALL FirstWord,LastWord FROM Sample.MyTest
     ORDER BY LastWord)

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

FeedbackOpens in a new tab