To create a view on a table that already exists either only in
your project or also on in a metadata catalog, you can create an empty view
in your project and then use the Properties view to create the SELECT statement.
Restrictions
If you are creating a view for change capture, these restrictions apply:
- The view cannot reference more than one table. This includes tables in
the FROM clause or the WHERE clause as in the case of sub-selects.
- The view cannot reference another view.
- The view must reference all of the columns in the base table.
- The base table must not map to record arrays.
Procedure
To create a view with the Properties view:
- In the Data Project Explorer, expand the physical data model in
which you are working. Expand the database in which you are working. Right-click
the schema in which you want to create the view and select Add
Classic Object > View. In
the Data Project Explorer, a view is created under the schema.
- Name the view.
- Select the view, and on the SQL page of the Properties view,
type the SELECT statement.
- On the Privileges page of the Properties view,
grant privileges on the view.
- Optional: Generate the DDL for the view. Right-click
the view and select Generate DDL to open the Generate
DDL wizard. With this wizard, you can generate the SQL
DDL to define the view, and you can choose to run the DDL on a data server
so that the view is created in the metadata catalog for that data server.
You can also edit the generated DDL before you run it.
After you run the
DDL, the view appears on the data server in the Database Explorer. To see
the view, expand the data server and then expand Schemas > the
schema of the view > Views.
If you
want to generate and run the DDL for more than one object at a time, you can
right-click a schema and select Generate DDL. The Generate
DDL wizard will generate the DDL for all of the objects in the
schema.
- Optional: If you created the view on the data server,
run a test query on the view.
- In the Database Explorer, right-click the view and select Data > Sample
Contents.
- Look in the Data Output view to see the
results of the test query.