Creating Database Views

In a DB diagram, you create a new database view using the context menu. 'From' clauses, which reference column sets (table or view) used in the view, are created using the carousel on a view. If you want to reference existing column sets using a From clause, add these to the diagram from other tool windows using drag-and-drop. Modeling union views and sub selects is supported by specific stereotypes in the element type view.

Automatic Creation of View Columns

In the steps described below, View columns are created automatically when a column set is referenced by a From clause for the first time in the View. The source column is referenced via a hyperlink in the Expression property of the view column.

Creating a New Database View

How to proceed

Position the mouse pointer at your chosen position in the diagram and click on the view template in the mini toolbar of the context menu.

A new view is created in the diagram. The name of the view is automatically selected to be edited.
Enter the name for the table and confirm with the [Enter] key.

Adding an Existing Column Set

How to proceed

Select the column set in another tool window and drag-and-drop it to your chosen position in the diagram.

A node that shows the existing column set is created in the diagram.

Creating a New From Clause (Complete)

How to proceed

Select a view in the diagram.

The carousel appears next to the view.
Select the From Clause icon in the carousel and drag it onto the column set to be referenced.

The From clause is created and represented in the diagram as an edge with an arrow to the referencing column set. A corresponding column is created and linked in the view for each column in the referencing column set.
Delete the columns that are not required in the view.

Creating a New From Clause (for Selected Columns)

How to proceed

Select one or more columns in a column set and drag-and-drop these onto a view.

The From clause is created and represented in the diagram as an edge with an arrow to the referencing column set. A corresponding column is created and linked in the view for each column selected.

Creating a New From Clause (Join)

How to proceed

Drag-and-drop a foreign key from the section of a table onto a view.

Two From clauses are created and shown in the diagram. The first of these references the table with the referencing key, and the second the table with the foreign key. The second From clause receives the Join condition in the Expression property (comparison of the key columns).

Using a Sub Select

Context

Graphic models are not suitable for the complete mapping of SQL grammar. Sub selects can occur anywhere in expressions of view elements. They are formulated in the expressions editor with the help of an Intellisense drop-down list. To enable this, a special sub select "From" clause must be created so that the Intellisense drop-down list offers the required elements in the expressions editor.

There is a separate compartment in the diagram node of a view for sub selects; in the table editor, there is a separate tab.

How to proceed

Select a view in the diagram.

The carousel appears next to the view.
Select the sub select icon in the carousel and drag it onto the column set to be referenced.

The sub select clause is created and represented in the diagram as an edge with an arrow to the referencing column set.
Use the expressions editor of the desired element to formulate the sub select.

Modeling a Union View

Context

A union view is a simple form of a combined view in which the select statements are linked with UNION or UNION ALL.

Special view stereotypes are configured for modeling combined views: 'Combined View' for the view itself, 'Select in Combined View' for the select statements that are configured as content elements of the combined view.

Develop a select statement just like a normal view. i.e. you can also use the table editor for the select statements. The select statements are displayed in a compartment within the combined view in the diagram. This means that you drag the "From" clauses from the carousel of a select statement selected in the compartment onto the referencing classifier.

Unlike with the normal view, a combined view's View columns are not linked to the select statement's columns by 'Features Used'; the order is evaluated instead. To check or adjust the order of the columns in the select statements, select Design>Adjust>Adjust Select Statements in the combined view's table editor.

How to proceed

Position the mouse pointer at your chosen position in the diagram and click on the Combined view template in the context menu's mini toolbar.

A new view is created in the diagram. The name of the view is automatically selected to be edited. If you use the create template from the standard profiles, two select statements are already initially contained in the view.
Enter the name for the table and confirm with the [Enter] key.
If you want to create further select statements, click on the Select template in the context menu's mini toolbar.

A new select statement is created in the view. The name is automatically selected for editing.
For each select statement, create at least one "From" clause. To do so, select the select statement in the compartment of the combined view.

The carousel appears next to the select statement.
Select the From Clause icon in the carousel and drag it onto the column set to be referenced.

The From clause is created and represented in the diagram as an edge with an arrow to the referencing column set. A corresponding column is created and linked in the view for each column in the referencing column set.
Jump to the combined view's table editor.
Select Design>Adjust>Adjust Select Statements.

The dialog of the same name appears.
Delete the superfluous columns and adjust the order of the columns of the combined view and select statements.
To change the union type of a select statement from UNION to UNION ALL, select the select statement, select Properties from the context menu and change the Join Kind property in the dialog.

Modeling a Combined View

Context

To create a combined view, proceed as when modeling a union view (see above).

You can then perform two additional actions to link the select statements as desired:

For the join kind of a select statement, you can select INTERSECT or EXCEPT as set operators in addition to UNION and UNION ALL.

In some databases, the key word MINUS is used instead of EXCEPT. The DDL generator generates the correct key word from the join kind EXCEPT.
Set parentheses to determine the order of combined select statements.

How to proceed

Open the table editor of the combined view and jump to the Database View tab.

The select statements are displayed in the rows of the table.
Change the set operator in the Join Kind column.
To set parentheses, select at least two select statements so that the correct setting of parentheses is possible and select Design>Parentheses>Set.

The setting of the parentheses is displayed in the Join Kind column.
To remove parentheses, select the select statements with the opening and corresponding closing parentheses and select Design>Parentheses>Remove.

These parentheses will be removed.
Alternatively, to remove parentheses you can select just one select statement with either at least one opening or at least one closing parenthesis.

The innermost set of parentheses is removed.