Alter regular views

You can alter a regular view using Sybase Central or Interactive SQL.

In Sybase Central, you can alter the definition of views, procedures, and functions on the object's SQL tab in the right pane. You edit a view in a separate window by selecting the view and then choosing File » Edit In New Window. In Interactive SQL, you can use the ALTER VIEW statement to alter a view. The ALTER VIEW statement replaces a view definition with a new definition, but it maintains the permissions on the view.

You cannot rename an existing view. Instead, you must create a new view with the new name, copy the previous definition to it, and then drop the old view.

If you use the ALTER VIEW statement to alter a view owned by another user, you must qualify the name by including the owner (for example, GROUPO.EmployeeConfidential). If you don't qualify the name, the database server looks for a view with that name owned by you and alters it. If there isn't one, it returns an error.

View alterations and view dependencies

If you want to alter the definition for a regular view, and there are other views dependent on the view, there may be additional steps to make after the alteration is complete. For example, after you alter a view, the database server automatically recompiles it, enabling it for use by the database server. If there are dependent regular views, the database server disables and re-enables them as well. If they cannot be enabled, they are given the status INVALID and you must either make the definition of the regular view consistent with the definitions of the dependent regular views, or vice versa.

To determine whether a regular view has dependent views, use the sa_dependent_views system procedure. See sa_dependent_views system procedure.

For information about how views are impacted by schema alterations to underlying objects, see View dependencies.

To alter a regular view (Sybase Central)

Use the SQL Anywhere 12 plug-in to connect to the database as a user with DBA authority or as the owner of the regular view.
In the left pane, double-click Views.
Select the view.
In the right pane, click the SQL tab and edit the view's definition.

Tip

If you want to edit multiple views, you can open separate windows for each view rather than editing each view on the SQL tab in the right pane. You can open a separate window by selecting a view and then choosing File » Edit In New Window.
Choose File » Save.

To alter a regular view (SQL)

Use the SQL Anywhere 12 plug-in to connect to the database as a user with DBA authority or as the owner of the regular view.
Execute an ALTER VIEW statement.

Examples

This example shows that when you alter a regular view, you are effectively replacing the definition of the view. In this case, the view definition is being changed to have column names that are more informative.

CREATE VIEW DepartmentSize ( col1, col2 ) AS
   SELECT DepartmentID, COUNT( * )
   FROM Employees GROUP BY DepartmentID;
ALTER VIEW DepartmentSize ( DepartmentNumber, NumberOfEmployees ) AS
   SELECT DepartmentID, COUNT( * )
   FROM Employees GROUP BY DepartmentID;

The next example shows that when you are changing only an attribute of the regular view, you do not need to redefine the view. In this case, the view is being set to have its definition hidden.

ALTER VIEW DepartmentSize SET HIDDEN;

See ALTER VIEW statement.

Discuss this page in DocCommentXchange.