Logical Views

Previous Next

See Also

Logical views present data from one or more tables to the user. A logical view is treated as a table, and is displayed in an info box. The contents of this info box are determined by the SQL statement of the logical view. Logical views can be painted with the Windows Designer.

Logical views provide a number of potential benefits:

· If the user often wants to view the same data taken from different tables, you can present this data within one view. This prevents the user from writing complex queries, or performing complex navigations by using related and lookup windows.


· You can present calculated values like running totals, currency conversions, or the result of mathematical formulas within a logical view.


· You are able to store confidential information in tables, but keep it out of a logical view. You simply leave certain columns or records out from the SQL statement of the logical view.


· You can use logical views within logical views.


· Logical views can be used to manipulate data:
· Data manipulation is possible provided that the SQL statement is not a UNION ALL statement and that it does not contain a DISTINCT function.


· Only simple column expressions in the SELECT list are manipulable.


· You can insert, update, or delete data if the output list of the SELECT statement of the logical view contains the primary key columns of at least one table. In the case of an update, only columns of these tables can be updated. In the case that the primary key columns of more than one table appear in the output list, manipulations (inserts, updates as well as deletes) are executed on each of these tables


· In the case of an insert, all mandatory columns of an insertable table must be part of the view, or they must have default values.
· Logical views can be used as input for report writers via the USoft ODBC Server.


· In the SQL statement of a logical view you can use UNION ALL. Other set operators (UNION, INTERSECT, MINUS) are not supported.


You use the Authorizer to control who is allowed to manipulate data using logical views.

To define a logical view, you can use the Logical Views info window. If you use the info window, after you have defined the SQL statement you have to check this statement manually, by clicking the Check button and commit.

In the SQL statement of a logical view you cannot use CONNECT BY/START WITH/ORDER BY. Furthermore, logical views cannot be used in relationships, and cannot be supertypes or subtypes. In addition, a logical view cannot be the transition table of a constraint.

A logical view always needs to be re-checked if tables used in the logical view are changed. When you remove a column from a table that is part of a logical view, a message is shown to indicate that it is no longer correct.


It is possible to define a logical view which causes one field in a base table to appear in multiple columns or records of the view. An update on such a field in the logical view implicitly updates multiple fields. Since Update Notify is not supported on multi-table views (i.e. automatically refreshing data on the screen when the data is updated via the Rule Engine), this may yield unexpected results.


The Rules Engine parameter, VERIFY_VIEW_WHERECLAUSE specifies whether manipulations on logical views must be checked to see if the values entered match the WHERE clause of the logical view or not.

If set to 'True' (default), the values inserted or updated in a logical view are checked against the WHERE clause of the logical view. If they do not match, an error message is displayed: 'manipulation does not match WHERE clause'. In this case, you cannot enter data in the logical view that cannot be queried by this logical view. If set to 'False', this check is not performed.

If the setting is not present in the Rules Engine parameters, recreate the Definer repository tables WITHOUT dropping the tables. This action will create the new parameter.


Logical Views and Default Values:

· The Default Value specified for a logical view column overrules the default value of the underlying table, also if no (NULL) default value is specified.


· If the underlying domain has a default value, this default value overrules the value specified at column level.


· If an insert in a logical view results in setting a column value in an underlying table that is not a logical view column, the default value for the underlying table column is taken.

Related Topics

Logical View Examples