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
Arguments
viewName
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.
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.
column
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.
Examples
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'.