DROP VIEW
Synopsis
DROP VIEW view-name [CASCADE | RESTRICT]
Arguments
Argument | Description |
---|---|
view-name | The name of the view to be deleted. A view name can be qualified (schema.viewname), or unqualified (viewname). An unqualified view name takes the default schema name. |
CASCADE RESTRICT | Optional — Specify the CASCADE keyword to drop any other view that references view-name. Specify RESTRICT to issue an SQLCODE -321 error if there is another view that references view-name. The default is RESTRICT. |
Description
The DROP VIEW command removes a view, but does not removes the underlying tables or data.
A drop view operation can also be invoked using the DropView()Opens in a new tab method call:
$SYSTEM.SQL.DropView(viewname,SQLCODE,%msg)
Privileges
The DROP VIEW command is a privileged operation. Prior to using DROP VIEW it is necessary for your process to have either %DROP_VIEW administrative privilege or a DELETE 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 DELETE privilege by invoking the %CHECKPRIV command. You can determine if a specified user has DELETE privilege by invoking the $SYSTEM.SQL.CheckPriv()Opens in a new tab method. You can use the GRANT command to assign %DROP_VIEW privileges, if you hold appropriate granting privileges.
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.
Nonexistent View
To determine if a specified view exists in the current namespace, use the $SYSTEM.SQL.ViewExists()Opens in a new tab method.
If you try to delete a nonexistent view, DROP VIEW issues an SQLCODE -30 error by default. However, this error-reporting behavior can be overridden by setting the system configuration as follows:
-
The $SYSTEM.SQL.SetDDLNo30()Opens in a new tab method call. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL DROP of non-existent table or view setting.
-
Go to the Management Portal, select System, Configuration, General SQL Settings. View the current setting of Allow DDL DROP of Non-existent Table or View.
The default is “No” (0). This is the recommended setting for this option. Set this option to “Yes” (1) if you want DROP VIEW and DROP TABLE for nonexistent views and tables to perform no operation and issue no error message.
VIEW Referenced by Other Views
If you try to delete a view referenced by other views in their queries, DROP VIEW issues an SQLCODE -321 error by default. This is the RESTRICT keyword behavior.
By specifying the CASCADE keyword, an attempt to delete a view referenced by other views in their queries succeeds. The DROP VIEW also deletes these other views. If Caché cannot perform all cascade view deletions (for example, due to an SQLCODE -300 error) no views are deleted.
Associated Queries
Dropping a view automatically purges any related cached queries and purges query information as stored in %SYS.PTools.SQLQueryOpens in a new tab. Dropping a view automatically purges any SQL runtime statistics (SQL Stats) information for any related query.
Examples
The following embedded SQL example creates a view named "CityAddressBook" and later deletes the view. Because it is specified with the RESTRICT keyword (the default), an SQLCODE -321 error is issued if the view is referenced by other views:
&sql(CREATE VIEW CityAddressBook AS
SELECT Name,Home_Street FROM Sample.Person
WHERE Home_City='Boston')
IF SQLCODE=0 { WRITE !,"View created" }
ELSE { WRITE !,"CREATE VIEW error: ",SQLCODE
QUIT }
/* Use the view */
NEW SQLCODE,%msg
&sql(DROP VIEW CityAddressBook RESTRICT)
IF SQLCODE=0 { WRITE !,"View dropped" }
ELSEIF SQLCODE=-30 { WRITE !,"View non-existent",!,%msg }
ELSEIF SQLCODE=-321 { WRITE !,"View referenced by other views",!,%msg }
ELSE { WRITE !,"Unexpected DROP VIEW error: ",SQLCODE,!,%msg }
See Also
-
“Views” chapter in Using Caché SQL
-
SQL configuration settings described in Caché Advanced Configuration Settings Reference.
-
SQLCODE error messages listed in the Caché Error Reference