Create View
You are looking at an older version of the documentation. The latest version is found here.
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 Data Virtuality Studio.
SQL
CREATE [PRIVATE] VIEW <schema_name>.<viewname>[column_name column_type, ..., PRIMARY KEY(column_name)] AS queryExpression;;PRIMARY KEY added in v2.4.20
The PRIVATE keyword is reserved for internal use and should not be specified for user-defined views.
Examples
SQL
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 ;;State
Once a view has been created, it will have a state:
| State | Description |
|---|---|
READY | The view can be used |
WARNING | The view can be used, but the backing data source uses a stored copy of metadata.
|
FAILED | The view failed to load. A view in this state cannot be used |
Most probably, the data source was unavailable when the Data Virtuality Server started or during the last metadata refresh. To fix this, it is usually enough to refresh the underlying data source