CREATE VIEW (SQL)

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, refer to %SYSTEM.SecurityOpens in a new tab 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. 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. For further details see the “Identifiers” chapter of Using InterSystems SQL.

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.

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 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 the Defining and Using Views chapter of Using InterSystems SQL.

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.