The basis of a view is a SELECT statement. When you create a view, you define a SELECT statement that generates the contents of the view at the time the view is accessed. A user also queries a view using a SELECT statement. The database server merges the SELECT statement of the user with the one defined for the view and then actually performs the combined statements.

The result has the appearance of a table; it is similar enough to a table that a view even can be based on other views, or on joins of tables and other views.

Because you write a SELECT statement that determines the contents of the view, you can use views for any of the following purposes:

• To restrict users to particular columns of tables

You name only permitted columns in the select list in the view.

• To restrict users to particular rows of tables

You specify a WHERE clause that returns only permitted rows.

• To constrain inserted and updated values to certain ranges

You can use the WITH CHECK OPTION (discussed on page 11-31) to enforce constraints.

• To provide access to derived data without having to store redundant data in the database

You write the expressions that derive the data into the select list in the view. Each time you query the view, the data is derived anew. The derived data is always up to date, yet no redundancies are introduced into the data model.

• To hide the details of a complicated SELECT statement

You hide complexities of a multitable join in the view so that neither users nor application programmers need to repeat them.

Creating Views

CREATE VIEW name_only AS

SELECT customer_num, fname, lname FROM customer

GLS

The following example creates a view based on a table that is available when a locale other than the default U.S. English locale using the ISO8859-1 code set has been enabled. In the example, the view, column, and table names contain non-English characters.

CREATE VIEW çà_va AS

SELECT numéro, nom FROM abonnés;  
 

CREATE VIEW full_addr AS

SELECT address1, address2, city, state.sname, zipcode

	FROM customer, state

	WHERE customer.state = state.code

SELECT * FROM full_addr WHERE customer_num = 105



SELECT address1, address2, city, state.sname, zipcode

	FROM customer, state

	WHERE customer.state = state.code

		AND customer_num = 105

The following example restricts the rows that can be seen in the view:

CREATE VIEW no_cal_cust AS

	SELECT * FROM customer WHERE NOT state = 'CA'

CREATE VIEW my_calls AS

	SELECT * FROM cust_calls WHERE user_id = USER

Duplicate Rows from Views

You can prevent this problem in two ways. One way is to specify DISTINCT in the select list in the view. However, specifying DISTINCT makes it impossible to modify through the view. The alternative is to always select a column or group of columns that is constrained to be unique. (You can be sure that only unique rows are returned if you select the columns of a primary key or of a candidate key. Primary and candidate keys are discussed in Chapter 8, "Building Your Data Model.")

Restrictions on Views

Because it must be merged with the user's query, the SELECT statement on which a view is based cannot contain any of the following clauses:

INTO TEMP	The user's query might contain INTO TEMP; if the view also contains it, the data would not know where to go.
UNION	The user's query might contain UNION. No meaning has been defined for nested UNION clauses.
ORDER BY	The user's query might contain ORDER BY. If the view also contains it, the choice of columns or sort directions could be in conflict.

When the Basis Changes

When a table or view is dropped, any views in the same database that depend on it are automatically dropped.

The only way to alter the definition of a view is to drop and re-create it. Therefore, if you change the definition of a view on which other views depend, you must also re-create the other views (because they all have been dropped).

When a table is renamed, any views in the same database that depend on it are modified to use the new name. When a column is renamed, views in the same database that depend on that table are updated to select the proper column. However, the names of columns in the views themselves are not changed. For an example of this, recall the following view on the customer table:

CREATE VIEW name_only AS

	SELECT customer_num, fname, lname FROM customer

RENAME COLUMN customer.lname TO surname

SELECT fname, surname FROM customer



SELECT fname, lname FROM name_only

Universal Server permits you to base a view on tables and views in external databases. Changes to tables and views in other databases are not reflected in views. Such changes might not be apparent until someone queries the view and gets an error because an external table changed.

Creating Typed Views

CREATE TABLE emp
(
	name		VARCHAR(30),
	age		INTEGER,
	salary	 	INTEGER
);

CREATE ROW TYPE name_age_t
( 	name		VARCHAR(20),
	age		INTEGER);



CREATE VIEW name_age OF TYPE name_age_t AS

	SELECT name, age FROM emp;


CREATE ROW TYPE name_salary_t
( 	name		VARCHAR(20),
	salary		INTEGER);





CREATE VIEW name_salary OF TYPE name_salary_t AS

	SELECT name, salary FROM emp

In some cases, a typed view has a distinct advantage over an untyped view. For example, suppose you have overloaded a function foo(). Depending on the argument types that you specify, the database server calls a different function foo(). (For more information about function overloading, see Extending INFORMIX-Universal Server: User-Defined Routines.) Because the name_age and name_salary views are typed views, the following statements resolve to the appropriate foo() function, according to the type of the view that is associated with the alias p:

SELECT foo(p) FROM name_age p; 
SELECT foo(p) FROM name_salary p;

Modifying Through a View

No modification is possible on a view when its SELECT statement contains any of the following features:

• A join of two or more tables

Many anomalies arise if the database server tries to distribute modified data correctly across the joined tables.

• An aggregate function or the GROUP BY clause

The rows of the view represent many combined rows of data; the database server cannot distribute modified data into them.

• The DISTINCT keyword or its synonym UNIQUE

The rows of the view represent a selection from among possibly many duplicate rows; the database server cannot tell which of the original rows should receive the modification.

When a view avoids all these things, each row of the view corresponds to exactly one row of one table. Such a view is modifiable. (Of course, particular users can modify a view only if they have suitable privileges. Privileges on views are discussed beginning on page 11-32.)

Deleting Through a View

Updating a View

When a column is derived from a simple arithmetic combination of a column with a constant value (for example, order_date+30), the database server can, in principle, figure out how to invert the expression (in this case, by subtracting 30 from the update value) and perform the update. However, much more complicated expressions are possible, most of which cannot easily be inverted. Therefore, the database server does not support updating any derived column.

The following example shows a modifiable view that contains a derived column and an UPDATE statement that can be accepted against it:

CREATEVIEWcall_response(user_id,received,resolved,duration

		)AS

	SELECT user_id,call_dtime,res_dtime,res_dtime

		call_dtime

		FROM cust_calls

		WHERE user_id = USER



UPDATE call_response SET resolved = TODAY

	WHERE resolved IS NULL

A view can return duplicate rows even though the rows of the underlying table are unique. You cannot distinguish one duplicate row from another. If you update one of a set of duplicate rows (for example, by using a cursor to update WHERE CURRENT), you cannot be sure which row in the underlying table receives the update.

Inserting into a View

When a modifiable view contains no derived columns, you can insert into it as if it were a table. However, the database server uses null as the value for any column that is not exposed by the view. If such a column does not allow nulls, an error occurs, and the insert fails.

Using the WITH CHECK OPTION Clause

To avoid updating a row of a view so that it no longer satisfies the conditions of the view, you can add the WITH CHECK OPTION clause when you create the view. This clause asks the database server to test every inserted or updated row to ensure that it meets the conditions set by the WHERE clause of the view. The database server rejects the operation with an error if the conditions are not met.

In the previous example, the view named call_response is defined as the following example shows:

CREATEVIEWcall_response(user_id,received,resolved,duration)AS

	SELECT user_id,call_dtime,res_dtime,res_dtime-call_dtime

		FROM cust_calls

		WHERE user_id = USER

UPDATE call_response SET user_id = 'lenora'

	WHERE received BETWEEN TODAY AND TODAY-7

CREATEVIEWcall_response(user_id,received,resolved,duration) AS

	SELECT user_id,call_dtime,res_dtime,res_dtime-call_dtime

		FROM cust_calls

		WHERE user_id = USER

WITH CHECK OPTION

You can use the WITH CHECK OPTION clause to enforce any kind of data constraint that can be stated as a Boolean expression. In the following example, you can create a view of a table in which all the logical constraints on data are expressed as conditions of the WHERE clause. Then you can require all modifications to the table to be made through the view.

CREATE VIEW order_insert AS

	SELECT * FROM orders O

		WHERE order_date = TODAY -- no back-dated entries

			AND EXISTS -- ensure valid foreign key

				(SELECT * FROM customer C

					WHERE O.customer_num = C.customer_num)

			AND ship_weight < 1000 -- reasonableness checks

			AND ship_charge < 1000

WITH CHECK OPTION