ALTER VIEW (SQL)
ALTER VIEW viewName AS query ALTER VIEW viewName (column, column2, ...) AS query ALTER VIEW viewName ... AS query WITH READ ONLY ALTER VIEW viewName ... AS query WITH [LOCAL | CASCADED] CHECK OPTION
The ALTER VIEW command modifies views created using the CREATE VIEW command or a view projected from a persistent class. The altered view replaces the existing view, so you cannot modify specific columns in a view.
A view is a virtual table based on the result set of a SELECT query or a UNION of such queries. For more details on views, see Defining and Using Views.
ALTER VIEW viewName AS query replaces the existing columns in a view with the columns returned by the SELECT query. The view column names are derived from the column names returned by the result set of the query, which can be:
The column names or aliases of the table or view being queried
The column name of a class query defined as a table-valued function
This statement modifies the NewEmployees view so that it includes only employees hired within the last 12 months. The view column names, Name, Office, and StartDate, match the column names of the source table.
ALTER VIEW NewEmployees AS SELECT Name,Office,StartDate FROM Sample.Employees WHERE DATEDIFF('month',StartDate,CURRENT_DATE) <= 12
ALTER VIEW viewName (column, column2, ...) AS query specifies the names of the columns to include in the view. The column names must correspond in number and sequence with the table columns returned by the SELECT query. Alternatively, you can specify these view column names as column name aliases in the SELECT statement query. These ALTER VIEW statements are equivalent:
ALTER VIEW MyView (MyViewCol1,MyViewCol2,MyViewCol3) AS SELECT TableCol1, TableCol2, TableCol3 FROM MyTable
ALTER VIEW MyView AS SELECT TableCol1 AS ViewCol1, TableCol2 AS ViewCol2, TableCol3 AS ViewCol3 FROM MyTable
The column specification replaces any existing columns specified for the view.
Example: Create and Alter a View
ALTER VIEW viewName ... AS query WITH READ ONLY 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 any specified WITH CHECK OPTION constraints.
Example: Set Read-Only View
ALTER VIEW viewName ... AS query WITH [LOCAL | CASCADED] CHECK OPTION checks that any row being updated or inserted into this view satisfies the WHERE constraints of the view. If the row does not meet these constrains, that row is not updated or inserted. You can specify these check options:
WITH LOCAL CHECK OPTION — Check only the WHERE clause of the view specified in the INSERT or UPDATE statement.
WITH CASCADED CHECK OPTION or WITH CHECK OPTION — Check the WHERE clause of the view specified in the INSERT or UPDATE statement and all underlying views on which that view is based. This option overrides any WITH LOCAL CHECK OPTION clauses in these underlying views and is recommended for all updateable views.
For more details on these options, see The WITH CHECK Option.
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.
To determine if a specified view exists in the current namespace, use the $SYSTEM.SQL.Schema.ViewExists()Opens in a new tab method.
If the view is projected from a persistent class, you can run ALTER VIEW only if the view has the Classtype="view" and DDLAllowed keywords specified. You cannot alter views that are projected from a class query.
The result set from a query that serves as a the basis for the view. You can specify the query as a SELECT statement or a UNION of two or more SELECT statements. For an example that uses a UNION command, see Alter View Using Combined SELECT Queries.
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.
The name of a column included in the modified view. Specify multiple column names in a comma-separated list. You can specify column names after the viewName argument or in the query argument.
Create and Alter a View
This example shows how to create a view and then alter it. The example also shows how to query and delete the view.
Create a view the contains the names of people who live in Massachusetts. This example assumes that a Sample.Person table already exists and contains a Home_State column.
CREATE VIEW MassFolks (vFullName) AS SELECT Name FROM Sample.Person WHERE Home_State='MA'
You can then query the view as you would a regular table.
SELECT * FROM MassFolks
Modify the view to include new columns. Altering a view replaces the column list with a new column list but does not preserve the prior column list. Therefore, this modified view contains only the vMassAbbrev and vCity columns, not the vFullName column.
ALTER VIEW MassFolks (vMassAbbrev,vCity) AS SELECT Home_State,Home_City FROM Sample.Person WHERE Home_State='MA'
Delete the view. You can delete a view similar to how you would delete a regular table.
DROP VIEW MassFolks
Alter View Using Combined SELECT Queries
Alter a view to include the combined results of two SELECT queries. To combine the results, you use a UNION command.
ALTER 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
Set Read-Only View
Modify a view to prevent modifying the underlying table through this view.
ALTER VIEW YoungPeople AS SELECT Name,DOB FROM Sample.Person WHERE DATEDIFF(year,DOB,CURRENT_DATE) <= 18 WITH READ ONLY
If you update any row through this view, the WITH READ ONLY prevents the update.
UPDATE YoungPeople (DOB) VALUES (02/17/2022) WHERE Name='Page,Laura O.'
Validate Table Modifications Made Through a View
Modify this view of honor students to prevent the insertion of students that do not meet the GPA criteria. This examples assumes that a Sample.Student table already exists.
ALTER VIEW HonorsStudent AS SELECT Name, GPA FROM Sample.Student WHERE GPA > 3.0 WITH CHECK OPTION
If you try to insert a student with too low a GPA for this view, the VIEW CHECK OPTION prevents the insertion.
INSERT INTO HonorsStudent (Name, GPA) VALUES ('Waal,Edgar P.',2.9)
Security and 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 'name' 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'.
If you hold appropriate granting privileges, you can assign %ALTER_VIEW and %ALTER privileges by using the GRANT command.
To determine if the current user has %ALTER privileges, call the %CHECKPRIV command. To determine if a specified user has %ALTER privilege, call the $SYSTEM.SQL.Security.CheckPrivilege()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("myUserName","myPassword") &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.
ALTER VIEW cannot be used on a view based on a table projected from a deployed persistent class. This operation fails with an SQLCODE -400 error with the %msg Unable to execute DDL that modifies a deployed class: 'classname'.