Guidelines for Using Views

There are certain restrictions, both on the SELECT statements you use to create views, and on your ability to insert into, delete from, or update them.

Restrictions on SELECT Statements

You cannot use an ORDER BY clause in the SELECT query. A characteristic of relational tables is that there is no significance to the ordering of the rows or columns, and using an ORDER BY clause imposes an order on the rows of the view. You can use the GROUP BY clause, subqueries, and joins in view definitions.

Scalar value subqueries are supported only within the top-level SELECT list (not in a view, a derived table, or a subquery). Sometimes views or derived tables used in the FROM clause of the top level SELECT are simple enough that they can be “flattened” up into the top level SELECT. As a result of this, the preceding rule is actually enforced only for subqueries, nonflattened views, and nonflattened derived tables. For example:

CREATE VIEW test_view AS SELECT testkey,(SELECT COUNT(*) FROM tagtests WHERE tagtests.testkey = testtrd.testkey ) FROM testtrd

SELECT * FROM test_view  
Msg 21, Level 14, State 0:  
SQL Anywhere Error -1005004: Subqueries are allowed only as arguments of  
comparisons, IN, and EXISTS,  
-- (opt_Select.cxx 2101)

To develop a view, tune the SELECT query by itself until it provides exactly the results you need in the format you want. Once you have the correct SELECT query, you can add a phrase in front of the query to create the view. For example:

CREATE VIEW viewname AS

Guidelines for Inserting and Deleting from 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 that contain:

Aggregate functions, such as COUNT(*)
A GROUP BY clause in the SELECT statement
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.

Warning! Do not delete views owned by the dbo user ID, which owns system objects. Deleting such views or changing them into tables may cause unexpected problems.