Create View Transformation
This transformation creates a view that can be used in data mining operations. One common reason for creating a view is to eliminate attributes (columns) from a data set.
This transformation provides a visual query builder that creates a new view based on existing tables and views. You use the query builder to create a SELECT
statement that defines the new view. The created view is saved in the connected user's schema.
Follow these steps to create a view:
- Select the tables or views to use.
- Define the view to create.
- Create the view.
Select Tables or Views
First, select the tables or views to use. The left pane of the query builder is a schema viewer. Expand a database connection to see all the schemas that the connection has been granted access to. Expand each schema to see the tables and views it contains and expand each table or view to see its columns. To use a table or view in a query, drag it to the right pane.
Define the View
The tabs in the right pane of the display allow you to visually define a SELECT
statement. The right pane of the query viewer has the following tabs:
- Columns: Drag tables or views displayed in the left pane to the pane associated with this tab. Use this tab to view database tables or views, to view the columns in the table or view, to view the primary keys (an icon that looks like a push-tack indicates a primary key) of the tables or views, to show tables or views that are related through a foreign key relationship (a line connects the keys in the tables), to create new relationships between columns (by drawing lines), and to select columns from different tables or views to include in the new view. You can also create a view consisting of a subset of the columns of an existing table or view. To select a column in a table or view, click the checkbox for the column name.
Each table or view includes an Options menu just above the list of attributes for the table or view. The Options menu contains the following selections; any selections that do not apply are unavailable (grayed out):
- Show Related Tables: Show the tables related to this one.
- Hide Related Tables: Do not show the related tables.
- Create outer join from table_name to: Create an outer join from the current table table_name to the selected table.
- Remove outer join from table_name to: Remove the outer join from the current table table_name to the selected table.
- Create Where Clause: Create complex constraints or conditions graphically that can be used as a
WHERE
clause in the SELECT
the new view. Use the diagram that is displayed to create the WHERE
clause. Select column names and specify relationships among them with pulldown lists.
- Edit Attributes: View the columns that were chosen to include in the new view. The display names become the column names in the new view. If duplicate column names exist as a result of table join, you must rename the duplicate columns so that the names are unique. The width field and visible field are used for the view results; the width field allows user to specify the column width in the view results and the visible field allows user to specify if the column is visible in the view results.
- Show SQL: View the
SELECT
statement resulting from selections that you have made. You cannot modify this SQL.
- View Results: View the results of the view creation without actually creating the view itself. Right click on the column header to sort the results or filter the results to search for or examine particular attributes.
Note: Only 100 rows are displayed.
Create the View
To create the view, select File | Create View from the query builder menu bar.
You can specify a name for the new view and an optional comment describing the new view.
Click OK to create the view. If any problems occur, messages are displayed to explain the problems. The new view is created in the connected user's schema.
After it is successfully created, the view is added to the navigator tree and its details (schema and data) are displayed.
Copyright © 2006, 2008, Oracle. All rights
reserved.