DROP QUERY (SQL)
Synopsis
DROP QUERY [IF EXISTS] name [ FROM className ]
Description
The DROP QUERY command deletes a query. When you drop a query, InterSystems IRIS revokes it from all users and roles to whom it has been granted and removes it from the database.
In order to drop a query, you must have %DROP_QUERY administrative privilege, as specified by the GRANT command. If you are attempting to delete a query for a class with a defined owner, you must be logged in as the owner of the class. Otherwise, the system generates an SQLCODE -99 error (Privilege Violation).
You cannot drop a query if the class definition that contains that query definition is a deployed class. This operation fails with an SQLCODE -400 error with the %msg Unable to execute DDL that modifies a deployed class: 'classname'.
The following combinations of name and FROM className are supported. Note that the FROM clause specifies the class package name and query name, not the SQL names. In these examples, the system-wide default schema name is SQLUser, which corresponds to the User class package:
-
DROP QUERY BonusCalc FROM queryBonusCalc: drops the query SQLUser.BonusCalc().
-
DROP QUERY BonusCalc FROM User.queryBonusCalc: drops the query SQLUser.BonusCalc().
-
DROP QUERY Test.BonusCalc FROM queryBonusCalc: drops the query SQLUser.BonusCalc().
-
DROP QUERY BonusCalc FROM Employees.queryBonusCalc: drops the query Employees.BonusCalc().
-
DROP QUERY Test.BonusCalc FROM Employees.queryBonusCalc: drops the query Employees.BonusCalc().
If the specified query does not exist, DROP QUERY generates an SQLCODE -362 error. If the specified class does not exist, DROP QUERY generates an SQLCODE -360 error. If the specified query could refer to two or more queries, DROP QUERY generates an SQLCODE -361 error; you must specify a className to resolve this ambiguity.
You can also delete a query by removing the query (projected as a stored procedure) from the class definition and then recompiling the class, or by deleting the entire class.
Arguments
IF EXISTS
An optional argument that suppresses the error if the command is executed on a nonexistent query.
name
The name of the query to be deleted. The name is an identifier. Do not specify the query’s parameter parentheses. A name can be qualified (schema.name), or unqualified (name). An unqualified query name takes the system-wide default schema name, unless the FROM className clause is specified.
FROM className
If specified, the FROM className clause deletes the query from the given class. If this clause is not specified, InterSystems IRIS searches all classes of the schema for the query, and deletes it. However, if no query of this name is found, or more than one query of this name is found, an error code is returned. If the deletion of the query results in an empty class, DROP QUERY deletes the class as well.
Examples
The following example attempts to delete myq from the class User.Employee. (Refer to CREATE TABLE for an example that creates class User.Employee.)
DROP QUERY myq FROM User.Employee