Informix Guide to SQL: Syntax SQL Statements

Use the CREATE VIEW statement to create a new view that is based on existing tables and views in the database.

You can create typed or untyped views. If you omit the OF TYPE clause, the rows in the view are considered to be untyped and default to an unnamed-row type. The following statement creates a view that is based on the person table. When you create a view without an OF TYPE clause, the view is referred to as an untyped view.

CREATE VIEW v1 AS SELECT * 
FROM person

Typed views, like typed tables, are based on a named-row type. Each column in the view corresponds to a field in the named-row type. The following statement creates a typed view that is based on the table person. To create a typed view, you must include an OF TYPE clause. When you create a typed view, the named-row type that you specify immediately after the OF TYPE keywords must already exist.

CREATE VIEW v2 OF TYPE person_t AS SELECT * 
FROM person

Except for the statements in the following list, you can use a view in any SQL statement where you can use a table:

The view behaves like a table that is called view. It consists of the set of rows and columns that the SELECT statement returns each time the SELECT statement is executed by using the view. The view reflects changes to the underlying tables with one exception. If a SELECT * clause defines the view, the view has only the columns in the underlying tables at the time the view is created. New columns that are subsequently added to the underlying tables with the ALTER TABLE statement do not appear in the view.

The view name must be unique; that is, a view name cannot have the same name as another database object, such as a table, synonym, or temporary table.

The view inherits the data types of the columns in the tables from which the view is derived. The database server determines data types of virtual columns from the nature of the expression.

To create a view, you must have the Select privilege on all columns from which the view is derived.

The SELECT statement is stored in the sysviews system catalog table. When you subsequently refer to a view in another statement, the database server performs the defining SELECT statement while it executes the new statement.

In DB-Access, if you create a view outside the CREATE SCHEMA statement, you receive warnings if you use the -ansi flag or set DBANSIWARN.

You cannot create a view on a temporary table.

You cannot create a view on the following types of tables in a remote database:

• Typed tables (including any table that is part of a table hierarchy)
• Tables that contain any extended data types

In CREATE VIEW, the FROM clause of the SELECT statement cannot contain the name of a temporary table.

Do not use display labels in the select list. Display labels in the select list are interpreted as column names.

The SELECT statement in CREATE VIEW cannot include the following clauses:

• FIRST
• INTO TEMP
• ORDER BY

For a complete description of SELECT syntax and usage, see SELECT.

The SELECT statement in CREATE VIEW can contain a UNION or UNION ALL operator. A view that contains a UNION or UNION ALL operator in its SELECT statement is known as a union view. Observe the following restrictions on union views:

• If a CREATE VIEW statement defines a union view, you cannot specify the WITH CHECK OPTION keywords in the CREATE VIEW statement.
• All restrictions that apply to UNION or UNION ALL operations in standalone SELECT statements also apply to UNION and UNION ALL operations in the SELECT statement of a union view. For a list of these restrictions, see Restrictions on a Combined SELECT.

For an example of a CREATE VIEW statement that defines a union view, see "Naming View Columns."

The number of columns that you specify in the column parameter must match the number of columns returned by the SELECT statement that defines the view.

If you do not specify a list of columns, the view inherits the column names of the underlying tables. In the following example, the view herostock has the same column names as the ones in the SELECT statement:

CREATE VIEW herostock AS

	SELECT stock_num, description, unit_price, unit, 
unit_descr

		FROM stock WHERE manu_code = 'HRO'

If the SELECT statement returns an expression, the corresponding column in the view is called a virtual column. You must provide a name for virtual columns. In the following example, the user must specify the column parameter because the select list of the SELECT statement contains an aggregate expression.

CREATE VIEW newview (firstcol, secondcol) AS
	SELECT sum(cola), colb

		FROM oldtab

You must also provide a column name in cases where the selected columns have duplicate column names when the table prefixes are stripped. For example, when both orders.order_num and items.order_num appear in the SELECT statement, you must provide two separate column names to label them in the CREATE VIEW statement, as the following example shows:

CREATE VIEW someorders (custnum,ocustnum,newprice) AS 

	SELECT orders.order_num,items.order_num,

			items.total_price*1.5

		FROM orders, items

		WHERE orders.order_num = items.order_num

		AND items.total_price > 100.00

You must also provide column names in the column parameter when the SELECT statement includes a UNION or UNION ALL operator and the names of the corresponding columns in the SELECT statements are not identical. In the following example, the user must specify the column parameter since the second column in the first SELECT statement has a different name from the second column in the second SELECT statement.

CREATE VIEW myview (cola, colb) AS
	SELECT colx, coly from firsttab

	UNION

	SELECT colx, colz from secondtab

If you must provide names for some of the columns in a view, then you must provide names for all the columns; that is, the column list must contain an entry for every column that appears in the view.

You can define a view in terms of other views, but you must abide by the restrictions on creating views that are discussed in the Informix Guide to Database Design and Implementation. For further information, see that manual.

The WITH CHECK OPTION keywords instruct the database server to ensure that all modifications that are made through the view to the underlying tables satisfy the definition of the view.

The following example creates a view that is named palo_alto, which uses all the information in the customer table for customers in the city of Palo Alto. The database server checks any modifications made to the customer table through palo_alto because the WITH CHECK OPTION is specified.

CREATE  VIEW  palo_alto  AS

	SELECT  *  FROM  customer

		WHERE  city  =  'Palo  Alto'

		WITH  CHECK OPTION 

You can insert into a view a row that does not satisfy the conditions of the view (that is, a row that is not visible through the view). You can also update a row of a view so that it no longer satisfies the conditions of the view. For example, if the view was created without the WITH CHECK OPTION keywords, you could insert a row through the view where the city is Los Altos, or you could update a row through the view by changing the city from Palo Alto to Los Altos.

To prevent such inserts and updates, you can add the WITH CHECK OPTION keywords when you create the view. These keywords ask the database server to test every inserted or updated row to ensure that it meets the conditions that are set by the WHERE clause of the view. The database server rejects the operation with an error if the row does not meet the conditions.

However, even if the view was created with the WITH CHECK OPTION keywords, you can perform inserts and updates through the view to change columns that are not part of the view definition. A column is not part of the view definition if it does not appear in the WHERE clause of the SELECT statement that defines the view.

If a view is built on a single table, the view is updatable if the SELECT statement that defined it did not contain any of the following items:

• Columns in the select list that are aggregate values
• Columns in the select list that use the UNIQUE or DISTINCT keyword
• A GROUP BY clause
• A UNION operator

In an updatable view, you can update the values in the underlying table by inserting values into the view.

In addition, if a view is built on a table that has a derived value for a column, that column is not updatable through the view. However, other columns in the view can be updated.

Important: You cannot update or insert rows in a remote table through views with check options.

Related Information

Related statements: CREATE TABLE, DROP VIEW, GRANT, SELECT, and SET SESSION AUTHORIZATION

For a discussion of views, see the Informix Guide to Database Design and Implementation.