Skip to main content

ALTER VIEW

Modifies a view.

Synopsis

ALTER VIEW view-name [(column-commalist)] AS query [WITH READ ONLY]

ALTER VIEW view-name [(column-commalist)] AS query [WITH [level] CHECK OPTION]

Arguments

Argument Description
view-name The view being modified, which has the same naming rules as a table name. A view name can be qualified (schema.viewname), or unqualified (viewname). An unqualified view name takes the default schema name.
column-commalist Optional — The column names that compose the view. If not specified here, the column names can be specified in the query, as shown below.
query The result set (from a query) that serves as a the basis for 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. For further details, refer to CREATE VIEW.

Description

The ALTER VIEW command allows you to modify a view. A view is based on the result set from a query consisting of a SELECT statement or a UNION of two or more SELECT statements. See CREATE VIEW for further information on using a UNION.

To determine if a specified view exists in the current namespace, use the $SYSTEM.SQL.ViewExists()Opens in a new tab method.

The optional column-commalist specifies the names of the columns included in the view. They must correspond in number and sequence with the table columns specified in the SELECT statement. You can also specify these view column names as column name aliases in the SELECT statement. If you specify neither, the table column names are used as the view column names.

These two ways to specify view column names are shown in the following examples:

ALTER VIEW MyView (MyViewCol1,MyViewCol2,MyViewCol3) AS
     SELECT TableCol1, TableCol2, TableCol3 FROM MyTable

is the same as:

ALTER VIEW MyView AS SELECT TableCol1 AS ViewCol1,
     TableCol2 AS ViewCol2,
     TableCol3 AS ViewCol3
     FROM MyTable

The column specification replaces any prior columns specified for the view.

A view query cannot contain host variables or include the INTO keyword. If you attempt to reference a host variable in query, the system generates an SQLCODE -148 error.

Privileges

The ALTER VIEW command is a privileged operation. The user must have %ALTER_VIEW administrative privilege to execute ALTER VIEW. Failing to do so results in an SQLCODE –99 error with the %msg User does not have %ALTER_VIEW privileges.

The user must have %ALTER privilege on the specified view. If the user is the Owner (creator) of the view, the user is automatically granted %ALTER privilege for that view. Otherwise, the user must be granted %ALTER privilege for the view. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have privilege to ALTER the view 'Schema.ViewName'.

You can use the GRANT command to assign %ALTER_VIEW and %ALTER privileges, if you hold appropriate granting privileges.

You can determine if the current user has %ALTER privilege by invoking the %CHECKPRIV command. You can determine if a specified user has %ALTER privilege by invoking the $SYSTEM.SQL.CheckPriv()Opens in a new tab method.

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.

Examples

The following examples create a view then alter that view. Programs are provided to query the view and to delete the view. Note that altering the view replaces the column list with a new column list; it does not preserve the prior column list.

  IF $SYSTEM.SQL.ViewExists("MassFolks")
     {WRITE "View already exists, please run DROP VIEW"  QUIT}
  &sql(CREATE VIEW MassFolks (vFullName) AS
       SELECT Name FROM Sample.Person WHERE Home_State='MA')
  IF SQLCODE=0 { WRITE !,"Created a view",! }
  ELSE { WRITE "CREATE VIEW error SQLCODE=",SQLCODE }
SELECT * FROM MassFolks
  IF 0=$SYSTEM.SQL.ViewExists("MassFolks")
     {WRITE "View doesn't exist"  QUIT}
  &sql(ALTER VIEW MassFolks (vMassAbbrev,vCity) AS
     SELECT Home_State,Home_City FROM Sample.Person WHERE Home_State='MA')
  IF SQLCODE=0 { WRITE !,"Altered view",! }
  ELSE { WRITE "ALTER VIEW error SQLCODE=",SQLCODE }
  DROP VIEW MassFolks

The following embedded SQL example alters a view using a query WITH CHECK OPTION:

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(ALTER VIEW Sample.MyTestView AS
     SELECT FIRSTWORD FROM Sample.MyTest WITH CHECK OPTION)
    IF SQLCODE=0 { WRITE !,"Altered view" }
    ELSE { WRITE "ALTER VIEW error SQLCODE=",SQLCODE }

See Also

FeedbackOpens in a new tab