Create View
Usage
Views can be created only in virtual schemas, for example, in the default schema views. Once created, they will appear in the respective virtual schemas section of the CData Virtuality Studio. An existing view will be replaced If CREATE OR REPLACE is used. An existing view will not be replaced if the view definition (including formatting and comments) has not changed.
CREATE [OR REPLACE] [PRIVATE] VIEW <schema_name>.<viewname>[column_name column_type, ..., PRIMARY KEY(column_name), FOREIGN KEY(column_name) REFERENCES another view(column name) ] AS queryExpression;;Syntax Rules
PRIMARY KEY, which uniquely identifies each record in a table, must contain a unique value and cannot be null;FOREIGN KEY, which refers to the primary key of another view;The number of columns in
queryExpressionmust not be changed.
The PRIVATE keyword is reserved for internal use and should not be specified for user-defined views.
Examples
CREATE VIEW views.countryregion AS
SELECT *
FROM adventureworks.countryregion ;;
CREATE VIEW virtual_schema_name.creditcard AS
SELECT number, expiryDate, ccv
FROM adventureworks.creditcard ;;
CREATE VIEW virtual_schema_name.creditcard(number integer, expiryDate date, PRIMARY KEY(number)) AS
SELECT number, expiryDate
FROM adventureworks.creditcard ;;
CREATE VIEW views.userOrder(userId INTEGER, userName INTEGER, orderId INTEGER, FOREIGN KEY(orderId) REFERENCES views.orders(orderId)) AS
SELECT u.userId, u.userName, o.orderId
FROM views.users u
JOIN views.orders o ON u.userId = o.userId;;State
Once a view has been created, it will have one of the following states:
State | Description |
|---|---|
| The view can be used |
| The view can be used, but the backing data source uses a stored copy of the metadata.
|
| The view failed to load. A view in this state cannot be used |
Most probably, the data source was unavailable when the CData Virtuality Server started or during the last metadata refresh. To fix this, it is usually enough to refresh the underlying data source