View Element Expressions

You can create views in the ER model and database views in the DB model using Innovator for Information Architects. You can use elements for the view elements in order to link them to features (attributes or columns). You can use the expressions editor to create these expressions by inserting possible elements as a hyperlink with IntelliSense support.

Purpose

Expressions of view elements place the desired SQL code into create view statements.

Examples:

Filters and organization structures of view

Formulation of WHERE, GROUP BY, ORDER BY and HAVING in view expression.
Functions in view columns

Enhancement of standard expression of column with functions and operators. Also linking of multiple source columns.
Join expression of "From" clause

Automatic creation of key comparison in expression through assignment of foreign key. Alternative writing of any join comparisons.
Sub selects anywhere

Formulation of sub select anywhere in one of the aforementioned expressions following the creation of a sub select for a view

Expressions Editor for View Elements

To open the expressions editor for a view element, open the table editor of a view.

You can edit the view expression itself in the Expression tab. You can edit the expressions of the view attributes, view columns, or "From" clauses in the relevant element cells. To do this, work directly in the editor of the cell or, for more complex expressions, open a dialog with the same IntelliSense support by clicking on the selection icon in the cell.

IntelliSense in the Expressions Editor

The expressions editor helps you to create an expression with the IntelliSense process. You can choose from the elements that can be used in the expression.

When you select an element, a hyperlink is added for the element. You can use this to navigate to it.

In the expression of a view, "From" clauses and features (attributes and columns) of the referenced classifiers are offered.

For a sub select "From" clause, the referenced classifier is also offered for the formulation of a complete sub select.

If you complete the selection of a "From" clause with the entry of a point, the hyperlink of the clause is inserted with the following point and the selection dialog for a referenced feature is opened immediately.

Features Used

If features have been inserted as hyperlinks in an Expression of a view element, these links are saved redundantly in the model. The Features Used relationship can thus be tracked in the impact analysis.

Example: Entering WHERE Clause in the Table Editor

Context

The database view BetterEarner uses a From clause t to reference the Employees table, which includes a table column Salary. You want to formulate a WHERE condition that displays the view Employees with a salary greater than € 5000.

How to proceed

Double-click on the view in the diagram or in the model structure.

The view's table editor is opened.
Jump to the Expression tab.

The tab contains the expressions editor for the view.
Enter "WHERE" into the editor and call IntelliSense support with [Ctrl]+[Space].

A drop-down list for selecting elements appears.
Select the From clause t and then complete the selection by entering a point.

The "From" clause is inserted into the editor as a hyperlink followed by a point. A drop-down list for selecting features appears.
Select the Salary column and adopt it with [Enter].

The column is inserted into the editor as a hyperlink after the point.
Complete the expression by means of text input in the editor.
Select Design>DDL>Show.

A dialog with the standard DDL of the view appears.

Example: Entering SQL Function for a View Column in the Properties Tab

Context

The database view CreditCard uses a From clause t to reference the Customers table, which includes a table column CreditCardNumber. You want the view to show only the last four digits of the credit card number.

How to proceed

Double-click on the view in the diagram or in the model structure.

The view's table editor is opened.
Select the Design>New>View column command in the Column tab.

A new view column is created in the view. The name of the column is automatically selected to be edited.
Change the name of the column (e.g. "DisplayCreditCard") and confirm the entry of the name with the [[Enter] key.
Enter "SUBSTR(" into the Expression editor and call IntelliSense support with [Ctrl]+[Space].

A drop-down list for selecting elements appears.
Select the From clause t and then complete the selection by entering a point.

The "From" clause is inserted into the editor as a hyperlink followed by a point. A drop-down list for selecting features appears.
Select the CreditCardNumber column and adopt it with [Enter].

The column is inserted into the editor as a hyperlink after the point.
Complete the expression by means of text input in the editor.
Exit the input by clicking outside the editing field.