User's Guide
   PART 1. Working with Databases
     CHAPTER 3. Working with Database Objects       

Working with views

Views are computed tables. You can use views to show database users exactly the information you want to present, in a format you can control.

Similarities between views and base tables 

Views are similar to the permanent tables of the database (a permanent table is also called a base table) in many ways:

• You can assign access permissions to views just as to base tables.

• You can perform SELECT queries on views.

• You can perform UPDATE, INSERT, and DELETE operations on some views.

• You can create views based on other views.

Differences between views and permanent tables 

There are some differences between views and permanent tables:

• You cannot create indexes on views.

• You cannot perform UPDATE, INSERT, and DELETE operations on all views.

• You cannot assign integrity constraints and keys to views.

• Views are recomputed each time they are invoked. Views refer to the information in base tables, but do not hold copies of that information.

Benefits of tailoring access 

Views are used to tailor access to data in the database. Tailoring access serves several purposes:

• Improved security     By not allowing access to information that is not relevant.

• Improved usability     By presenting users and application developers with data in a more easily understood form than in the base tables.

• Improved consistency     By centralizing in the database the definition of common queries.

  Creating views

A SELECT statement operates on one or more tables and produces a result set that is also a table: just like a base table, a result set from a SELECT query has columns and rows. A view gives a name to a particular query, and holds the definition in the database system tables.

Example 

Suppose that you frequently need to list the number of employees in each department. You can get this list with the following statement:

SELECT dept_ID, count(*)
FROM employee
GROUP BY dept_ID

You can create a view containing the results of this statement as follows:

CREATE VIEW DepartmentSize AS
SELECT dept_ID, count(*)
FROM employee
GROUP BY dept_ID

The information in a view is not stored separately in the database. Each time you refer to the view, the associated SELECT statement is executed to retrieve the appropriate data. On one hand, this is good because it means that if someone modifies the employee table, the information in the DepartmentSize view will be automatically up to date. On the other hand, if the SELECT statement is complicated it may take a long time for SQL to find the correct information every time you use the view.

  To create a view in Sybase Central:

1. Connect to the database.

2. Click the Views folder for that database.

3. Double-click Add View.

4. Enter the table and the columns to be used. In this case employee and dept_ID. From the File menu select Execute Script and from the File menu select Close.

     For more information, see the Sybase Central online Help.

  Using views

Restrictions on SELECT statements 

There are some restrictions on the SELECT statements that you can use as views. In particular, you cannot use an ORDER BY clause in the SELECT query. It is a characteristic of relational tables that there is no significance to the ordering of the rows or columns, and using an ORDER BY clause would impose an order on the rows of the view. You can use the GROUP BY clause, subqueries, and joins in view definitions.

To develop a view, you should tune the SELECT query by itself until it provides exactly the results you need in the format you want. Once you have the SELECT query just right, you can add a

CREATE VIEW viewname AS

phrase in front of the query to create the view.

Updating views 

UPDATE, INSERT, and DELETE statements are allowed on some views, but not on others, depending on its associated SELECT statement.

Views containing aggregate functions, such as COUNT(*), cannot be updated. Views containing a GROUP BY clause in the SELECT statement cannot be updated. Also, views containing a UNION operation cannot be updated. In all these cases, there is no way to translate the UPDATE into an action on the underlying tables.

  Using the WITH CHECK OPTION clause

Even when INSERT and UPDATE statements are allowed against a view, it is possible that the inserted or updated rows in the underlying tables may not meet the requirements for the view itself: the view would have no new rows even though the INSERT or UPDATE does modify the underlying tables.

Examples using the WITH CHECK OPTION clause 

The following set of examples illustrates the meaning and usefulness of the WITH CHECK OPTION clause. This optional clause is the final clause in the CREATE VIEW statement.

  To create a view displaying the employees in the sales department.

1. Type the following statements:

CREATE VIEW sales_employee
AS SELECT   emp_id,
      emp_fname,
      emp_lname,
      dept_id
FROM employee
WHERE dept_id = 200 

The contents of this view are as follows:

SELECT *
   FROM sales_employee

emp_id	emp_fname	emp_lname	dept_id
129	Philip	Chin	200
195	Marc	Dill	200
299	Rollin	Overbey	200
467	James	Klobucher	200
641	Thomas	Powell	200
...

• Transfer Philip Chin to the marketing department.     This view update causes the entry to vanish from the view, as it no longer meets the view selection criterion.

  UPDATE sales_employee
  SET dept_id = 400
  WHERE emp_id = 129

• List all employees in the sales department     Inspect the view.

  SELECT *
  FROM sales_employee

emp_id	emp_fname	emp_lname	dept_id
195	Marc	Dill	200
299	Rollin	Overbey	200
467	James	Klobucher	200
641	Thomas	Powell	200
667	Mary	Garcia	200
...

When a view is created WITH CHECK OPTION, any UPDATE or INSERT statement on the view is checked to ensure that the new row matches the view condition. If it does not, the operation causes an error and is rejected.

The following modified sales_employee view rejects the update statement, generating the following error message:

Invalid value for column 'dept_id' in table 'employee'

• Create a view displaying the employees in the sales department (second attempt)     Use WITH CHECK OPTION this time.

  CREATE VIEW sales_employee
  AS SELECT emp_id, emp_fname, emp_lname, dept_id
      FROM employee
      WHERE dept_id = 200
  WITH CHECK OPTION

The check option is inherited 

If a view (say V2) is defined on the sales_employee view, any updates or inserts on V2 that cause the WITH CHECK OPTION criterion on sales_employee to fail are rejected, even if V2 is defined without a check option.

  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

An INSERT, DELETE, or UPDATE operation is allowed either if permission on the view has been granted or if permission on the underlying tables has been granted.

UPDATE permissions can be granted only on an entire view. Unlike tables, UPDATE permissions cannot be granted on individual columns within a view.

  Deleting views

To delete a view from the database, you use the DROP statement. The following statement removes the DepartmentSize view:

DROP VIEW DepartmentSize

Dropping a view in Sybase Central 

To drop a view in Sybase Central, right-click the view you wish to delete and select Delete from the pop-up menu.

     For more information, see the Sybase Central online Help.

  Views in the system tables

All the information about views in a database is held in the system table SYS.SYSTABLE. The information is presented in a more readable format in the system view SYS.SYSVIEWS. For more information about these, see SYSTABLE system table, and SYSVIEWS system view.

You can use Interactive SQL to browse the information in these tables. Type the following statement in the Interactive SQL command window to see all the columns in the SYS.SYSVIEWS view:

SELECT * 
FROM SYS.SYSVIEWS

To extract a text file containing the definition of a specific view, use a statement such as the following:

SELECT viewtext FROM SYS.SYSVIEWS
   WHERE viewname = 'DepartmentSize';
OUTPUT TO viewtext.sql
FORMAT ASCII