>>-CREATE VIEW--view--+-------------------------+--AS----------->
                      |    .-,------.           |
                      |    V        |           |
                      +-(----column-+--)--------+
                      |  (1)                    |
                      '--------OF TYPE row_type-'
 
                                  (2)
>--| Subset of SELECT Statement |-------+-------------------+--><
                                        '-WITH CHECK OPTION-'
 

1. Dynamic Server only
2. See page Subset of SELECT Statements Valid in View Definitions

A view is a virtual table, defined by a SELECT statement. Except for the statements in the following list, you can specify the name or synonym of a view in any SQL statement where the name of a table is syntactically valid:

• ALTER FRAGMENT
• CREATE INDEX
• CREATE TABLE
• CREATE TRIGGER
• RENAME TABLE
• START VIOLATIONS TABLE
• STOP VIOLATIONS TABLE
• TRUNCATE
• UPDATE STATISTICS

You must specify the name of a view when you use the CREATE TRIGGER statement to define an INSTEAD OF trigger on a view, but the syntax and functionality are different from those of a trigger defined on a table.

Updating Through Views prohibits non-updatable views in INSERT, DELETE, or UPDATE statements (where other views are valid).

To create a view, you must have the Select privilege on all columns from which the view is derived. You can query a view as if it were a table, and in some cases, you can update it as if it were a table; but a view is not a table.

The view consists of the set of rows and columns that the SELECT statement in the view definition returns each time you refer to the view in a query.

In some cases, the database server merges the SELECT statement of the user with the SELECT statement defining the view and executes the combined statements. In other cases, a query against a view might execute more slowly than expected, if the complexity of the view definition causes the database server to create a temporary table (referred to as a materialized view). For more information on materialized views, see the IBM Informix Performance Guide.

The view reflects changes to the underlying tables with one exception. If a SELECT * specification defines the view, the view has only the columns that existed in the underlying tables when the view was defined by CREATE VIEW. Any new columns that are subsequently added to the underlying tables with the ALTER TABLE statement do not appear in the view.

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.

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 if you set the DBANSIWARN environment variable.

The following statement creates a view that is based on the person table. When you create a view like this, which has no OF TYPE clause, the view is referred to as an untyped view.

CREATE VIEW v1 AS SELECT * FROM person