Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 150 / 536

Working with views
130
CREATE VIEW
viewname AS
Inserting and deleting
on views
UPDATE
,
INSERT
, and
DELETE
statements are allowed on some views, but not
on others, depending on their associated
SELECT
statement.
You cannot update, insert into or delete from views in the following cases:
• Views containing aggregate functions, such as
COUNT(*)
• Views containing a
GROUP BY
clause in the
SELECT
statement
• Views containing a
UNION
operation
In all these cases, there is no way to translate the
UPDATE
,
INSERT
, or
DELETE
into an action on the underlying tables.
Modifying views
You can modify a view using the ALTER VIEW statement. The ALTER VIEW
statement replaces a view definition with a new definition; it does not modify
an existing view definition.
The ALTER VIEW statement maintains the permissions on the view.
Example
For example, to replace the column names with more informative names in the
DepartmentSize view described above, you could use the following statement:
ALTER VIEW DepartmentSize
(Dept_ID, NumEmployees)
AS
SELECT dept_ID, count(*)
FROM Employee
GROUP BY dept_ID
Permissions on views
A user may perform an operation through a view if one or more of the
following are true:
• The appropriate permission(s) on the view for the operation has been
granted to the user by a DBA.
• The user has the appropriate permission(s) on all the base table(s) for the
operation.