Creating views on existing tables with the Properties view

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.

Before you begin

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

  1. 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.
  2. Name the view.
  3. Select the view, and on the SQL page of the Properties view, type the SELECT statement.
  4. On the Privileges page of the Properties view, grant privileges on the view.
  5. 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 Data Source 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.

  6. Optional: If you created the view on the data server, run a test query on the view.
    1. In the Data Source Explorer, right-click the view and select Data > Sample Contents.
    2. Look in the Data Output view to see the results of the test query.