To create a view on a table that already exists either only in
your project or also in a metadata catalog, you can use the SQL builder, a
graphical utility that builds the SELECT statement for the view for you. 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 builder:
- 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 builder radio button
is selected.
- Click OK to open the SQL builder.
The title that appears for the SQL builder is the name that you gave
to the SELECT statement. For example, if your SELECT statement is named TEST,
the title for the SQL builder is TEST.
- In the SQL builder, add the tables on which to base the SELECT
statement for your view. You can add a table in one of two ways:
- Right-click the middle section of the SQL builder and select Add
Table. In the Add Table window, select a
table to add to the SQL builder and click OK.
- Left-click one of the tables in the schema in which you are creating
the view and drag the table to the middle section of the SQL builder.
- Build the SELECT statement for the view. For help building
a SELECT statement, press F1 while in the SQL builder and follow the link
to the online help for the SQL builder.
- 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 once, pause, then click it 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.