Caché SQL Reference
ALTER VIEW
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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
view-name The view being modified, which has the same naming rules as a table 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() 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, Caché generates an SQLCODE -148 error.
Privileges
The ALTER VIEW command is a privileged operation. Prior to using ALTER VIEW it is necessary for your process to have either %ALTER_VIEW administrative privilege or an %ALTER object privilege for the specified view. Failing to do so results in an SQLCODE -99 error (Privilege Violation). 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() method. You can use the GRANT command to assign %ALTER_VIEW or %ALTER 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.
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