View Element Expressions

You can create views in the ER model and database views in the DB model using Innovator for Database Architects. You can use elements for the view elements in order to link them to features (attributes or columns).

Features Used

View elements have an Expression property and a Features Used property which can be used to formulate expressions and link them to model elements used.

To do this, work in the Properties tab of the Properties tool window or in the table editor of the view element.

In the Features Used property you use a dialog to link the view element with features that should occur in the Expression. In the ER model these are either entity attributes or attributes of a view, while in the DB model they are table columns or view columns. You can only link features where their owner is referenced by the view of the view element using a From clause.

$ Wildcard for Features Used in the Expression

For every feature newly added to the Features Used property, an additional $ wildcard is inserted into the Expression property. The wildcard comprises a dollar sign followed by the list number of the feature used (e.g. $1).

In the Concrete Expression feature, the $ wildcard is replaced by the name of the feature. When a $ parameter is automatically inserted, the name of the From clause will also be written in before this, separated by a period. The result is the qualified specification of a column in a typical SQL expression.

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 table editor for the view is opened. The lower area of the table editor includes fields for the properties Concrete Expression, Expression and Features Used.
In the title of Features Used click on the selection button .

A dialog opens showing all features already used, i.e. it is currently empty.
Click on Add.

A feature selection dialog opens.
In the area on the right-hand side, select the Salary column.
Exit the feature selection dialog with OK.

The predecessor dialog opens and shows the Salary column.
Exit the dialog with OK.

In the table editor, the Salary column is shown in the Features Used field. The Expression edit field has the content t.$1 and the Concrete Expression display field shows the expression with replaced $ wildcard t.Salary.
Edit the expression in the editing field to be "t.$1 > 5000".
Exit the input by clicking outside the editing field.

The Concrete Expression display field shows the finished WHERE clause t.Salary > 5000.
Click on the 'less than' character in the DDL area on the right next to the Features Used field.

The DDL area opens and shows a DDL Export tab.
Click on Generate DDL.

The info window with the DDL File tab opens. The tab content shows the DDL of the view with the WHERE condition.

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

Position the mouse pointer on the view in the diagram and use the right mouse button to open the context menu.
Select the View Column icon in the mini toolbar.

A new view column is created in the view. The name of the column is automatically selected to be edited.
Confirm with the [Enter] key.

The view column is displayed with the name from the Create template in the view node.
In the Properties tool window in the Properties tab after the Features Used property, click on the selection button .

A dialog opens showing all features already used, i.e. it is currently empty.
Click on Add.

A feature selection dialog opens.
In the area on the right-hand side, select the CreditCardNumbercolumn.
Exit the feature selection dialog with OK.

The predecessor dialog opens and shows the CreditCardNumber column.
Exit the dialog with OK.

The view column is now also called CreditCardNumber because the name of the first used column customizes the name. The Expression property has the content t.$1 and the Concrete Expression property shows the expression with replaced $ wildcard t.CreditCardNumber. The view column is shown expanded in the view node: t.CreditCardNumber AS CreditCardNumber.
Change the name of the column (e.g. "DisplayCreditCard").
Edit the expression in the editing field to be "SUBSTR(t.$1,13,4)".
Exit the input by clicking outside the editing field.

The view column is shown expanded in the view node: SUBSTR(t.CreditCardNumber,13,4) AS DisplayCreditCard.