Home | Previous Page | Next Page   Managing Databases > Granting and Limiting Access to Your Database > Using Views >

Creating Views

The following example creates a view based on a table in the stores_demo database: 

CREATE VIEW name_only AS
SELECT customer_num, fname, lname FROM customer

The view exposes only three columns of the table. Because it contains no WHERE clause, the view does not restrict the rows that can appear.

The following example is based on the join of two tables: 

CREATE VIEW full_addr AS
SELECT address1, address2, city, state.sname,
   zipcode, customer_num
   FROM customer, state
   WHERE customer.state = state.code

The table of state names reduces the redundancy of the database; it lets you store the full state names only once, which can be useful for long state names such as Minnesota. This full_addr view lets users retrieve the address as if the full state name were stored in every row. The following two queries are equivalent: 

SELECT * FROM full_addr WHERE customer_num = 105

SELECT address1, address2, city, state.sname,
   zipcode, customer_num
   FROM customer, state
   WHERE customer.state = state.code AND customer_num = 105

However, be careful when you define views that are based on joins. Such views are not modifiable; that is, you cannot use them with UPDATE, DELETE, or INSERT statements. For a discussion about how to modify with views, see page Modifying with a View.

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'

This view exposes all columns of the customer table, but only certain rows. The following example is a view that restricts users to rows that are relevant to them: 

CREATE VIEW my_calls AS
   SELECT * FROM cust_calls WHERE user_id = USER

All the columns of the cust_calls table are available but only in those rows that contain the user IDs of the users who can execute the query.

Typed Views (IDS)

You can create a typed view when you want to distinguish between two views that display data of the same data type. For example, suppose you want to create two views on the following table:

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

The following statements create two typed views, name_age and name_salary, on the emp table: 

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

When you create a typed view, the data that the view displays is of a named row type. For example, the name_age and name_salary views contain VARCHAR and INTEGER data. Because the views are typed, a query against the name_age view returns a column view of type name_age whereas a query against the name_salary view returns a column view of type name_salary. Consequently, the database server is able to distinguish between rows that the name_age and name_salary views return.

In some cases, a typed view has an advantage over an untyped view. For example, suppose you overload the function myfunc() as follows: 

CREATE FUNCTION myfunc(aa name_age_t) ......;
CREATE FUNCTION myfunc(aa name_salary_t) .....;

Because the name_age and name_salary views are typed views, the following statements resolve to the appropriate myfunc() function: 

SELECT myfunc(name_age) FROM name_age; 
SELECT myfunc(name_salary) FROM name_salary;

You can also write the preceding SELECT statements using an alias for the table name: 

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

If two views that contain the same data types are not created as typed views, the database server cannot distinguish between the rows that the two views display. For more information about function overloading, see IBM Informix: User-Defined Routines and Data Types Developer's Guide.

Duplicate Rows from Views

A view might produce duplicate rows, even when the underlying table has only unique rows. If the view SELECT statement can return duplicate rows, the view itself can appear to contain duplicate rows.

You can prevent this problem in two ways. One way is to specify DISTINCT in the select list in the view. However, when you specify DISTINCT, it is impossible to modify with 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. Building a Relational Data Model discusses primary and candidate keys.)

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]