To create a view on a table that already exists either only in
your project or also on in a metadata catalog, you can use the SQL editor,
a text editor that lets you write the SELECT statement for the view. After
you create the SELECT statement, you can add the view to a schema in your
project.
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 SQL editor:
- 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 SQL Statements folder and select New
SQL Statement.
- In the New SQL Statement window, follow these
steps:
- Ensure that SELECT is selected in the Statement template field.
- Give the statement a descriptive name.
- Ensure that the SQL editor radio button
is selected.
- Click OK to open the SQL editor.
- Write the SELECT statement for the view.
- Optional: Test the SELECT statement. The
tables that are referenced by the view must already exist on the data server.
To test the SELECT statement, right-click the statement and select Run
SQL. Look in the Data Output view to see
whether the statement ran successfully.
- In the Data Explorer view generate and name
the view:
- In the SQL Statements folder of your
physical data model, right-click the SELECT statement and select Generate > View. The view appears in the same schema as the tables that it references.
- Click the name of the view, pause, then click again to highlight
the name. Give the view the name that you want.
- Select the view and use the Privileges page
of the Properties view to 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.