Creating Views 

Use

You use this procedure to create database views. You can create view columns, join conditions, and where clauses graphically, by directly writing an SQL statement, and by completing the required information in the relevant tabs in the view editor.

Database views are dynamic logical tables composed from data taken from database tables that are part of the physical database schema. You can specify how data from original tables is combined into a view using join conditions and where clauses. Join conditions combine data from two or more tables depending on the type of the join. The default join type is inner join. Inner joins find the intersection of two tables. Another join type is left outer join. This join type returns all values from left table with matching values from right table. A where clause returns data that matches a certain condition.

Prerequisites

You have created your tables. For more information, see Creating Tables.

Procedure

Creating Views

...

       1.      Select the project in the Dictionary Explorer and choose New → View from the context menu.

       2.      Specify the prefix and suffix for the view and choose Next.

       3.      Select the tables to include in the view and choose Next.

       4.      Select the columns to include in the view and choose Finish. The SAP NetWeaver Developer Studio creates the view and opens it.

       5.      To save your changes, choose Save.

Editing Views Graphically

...

Once you create a database view, the Database View Editor opens the view and displays the tables and columns included in the view.

When you edit views graphically, each operation you perform updates the relevant tabs in the editor.

       1.      To add tables to the view, choose the Add Tables button. The Show Tables dialog opens.

       2.      Select the tables to add to the view and choose OK. The Database View Editor updates the graphical representation of the view with the new tables.

       3.      To remove tables from the view, select the table and choose Hide from the context menu.

       4.      To add columns to the database view, select the graphical representation of the table and choose Add Columns to db view from the context menu. The Add View Columns dialog opens.

       5.      Select the relevant columns and choose OK.

       6.      To create a join condition, choose Join Condition.

       7.      Click the view column in the table you want to join and drag the cursor to the view column in the second table. The Database View Editor creates the join connection and updates the graphical representation the view with two lines. The first line connects  the tables and denotes the type of the join condition, for example, Inner Join. The second line connects the view columns and denotes the conditional operator type, for example equals.

You create a view and include two database tables, TMP_ADDRESS and TMP_EMPLOYEE. You create a join condition between the two ID columns in the tables:

       8.      By default, the Database View Editor creates inner join conditions with the equals operator. To change the join condition type, double-click the join type. The join type changes to a dropdown list box with the available join condition types.

       9.      Select the appropriate join condition type. The Database View Editor updates the graphical representation of the view.

You double-click the join condition type:

You select the relevant join condition type and the Database View Editor updates the graphical representation:

The Database View Editor also updates the Join Condition tab and the SQL Statement tab.

   10.      To change the join condition, double-click the conditional operator and select the appropriate value from the dropdown list box.

You double-click the conditional operator and select the less than or equal to operator from the dropdown list box:

The Database View Editor updates the graphical representation of the view:

The Database View Editor also updates the Join Condition tab and the SQL Statement tab.

   11.      To delete a join condition, select the condition and choose Delete from the context menu.

   12.      To save your changes, choose Save.

Editing Views with the View Editor Tabs

...

       1.      To add join conditions to the view, choose the Join Conditions tab.

                            a.      Choose Add Join Condition.

                            b.      Select the tables, corresponding columns, and conditions as necessary.

       2.      To add where clauses, choose the Where Clause tab.

                            a.      Choose Add Where Clause.

                            b.      Select the table, column, operator, value, and condition as necessary.

       3.      To edit view columns, choose the View Columns tab.

       4.      You can also edit views by directly entering SQL statements. Choose the SQL Statement tab and enter the statement for the view.

       5.      To save your changes, choose Save.