CREATE VIEW

Use the CREATE VIEW command to create a view.

Syntax

CREATE [ OR REPLACE ] VIEW [database_name.]view_name
[ (view_column_name,...) ]
[ TRANSIENT | PERSISTENT ]
AS SELECT_cmd;

If there is a possibility that a view already exists with the same name as the one you want to create, use the optional OR REPLACE keywords. If the view exists, it is replaced by the one you are creating. If the view does not exist, a new one is created.

The view name must be unique within the database and comply with the ObjectServer naming conventions. The following additional restrictions apply to the creation of views:
  • If you do not specify a database name, the view is created in the alerts database.
  • You cannot create a view on a view.
  • You cannot create a view on any table in the catalog database.

You can specify either a TRANSIENT or PERSISTENT storage type, depending on your data storage requirements. A transient view is destroyed when the client that created it disconnects. A persistent view is mirrored on disk. When the ObjectServer restarts, the view is recreated.

The SELECT_cmd is any SELECT command (including aggregate SELECT commands), with the following restrictions:
  • You must specify all of the column names explicitly, rather than using a wildcard (*), in the selection list.
  • If you include virtual columns, you cannot update them.
  • If you do not specify a database name, the default is alerts.
  • You cannot specify a GROUP BY clause.
  • You can only have a subquery containing a WHERE clause in an aggregate SELECT statement.
  • You cannot use virtual columns in an aggregate SELECT statement.
  • If you create an aggregate view, you cannot perform an aggregate SELECT on it.

Example

create view alerts.myview persistent as select Severity, LastOccurrence, Summary
from alerts.status order by Severity, LastOccurrence;