Views

Views are derived, virtual objects of the database and belong to its external scheme. They come into existence as the dynamic result of stored Select statements. Views points to elements (columns, rows) of one or several database tables (basic tables) or other views.

With the help of views you can set up task-specific user views on data already on modelling level while simultaneously hiding the logical structure of the data model as far as possible. Within views you can execute calculations using functions of the connected database (in SQL syntax). Their application can replace calculations executed via userexits or procedures (e.g. for calculation fields) and, thus, considerably reduce the programming efforts during application development.

Views in DataView are only created with the help of the Entity model object. As an alternative for DataView- or foreign format tables, entities can also be mapped as DataView views or foreign format views in the database. For this purpose you select V or W in the Mapping Type field of the Entity Data form. The Table Fields sub-list will then display a specific attribute record for view fields.


View Fields

If the mapping type View is selected, the sub-list Table Fields will display a specific attribute record for creating view fields. The easiest possibility to create view fields is to import the required basic fields from table-field assignments of existing tables via drag&drop. You will then only have to assign the corresponding view field names.

Field name
Syntax for a few fields is VIEWNAME.COLUMNNAME. The first part VIEWNAME is taken from the header form and is compulsory.

Sequence, type, multilinguality
The known table field attributes also exist for view fields. They are automatically taken into the view of fields during drag&drop of basic fields, but can be altered later.

In case of type changes it must the made sure that data from basic fields can be securely and completely incorporated by the DataView field.

The language list of a multilingual view field may contain less language IDs, but never different ones.

Path name
This is the name of the controlling basic field for the DataView field. Basic fields are really existing table fields in the database. If several viewers were put on top of each other", the complete path to the basic field is to be specified using the following syntax:

TABLENAME.COLUMNNAME>VIEWNAME1.COLUMNNAME> ... >VIEW NAMEn. COLUMN NAME

The names/paths are automatically entered into the view field list during drag&drop of fields.

Calculation strings and -> group functions are allowed and have to be specified in SQL syntax. They are stored in the database unchecked when the view is created.

After saving the few fields are -- just as table fields -- automatically taken into the field master and can be displayed in masks.


View Condition

The View Condition sub-form contains the Where condition for the view. The condition is part of the DataView definition and automatically gets active at each query to the view.

The structure of a view condition is not limited by DataView. When you create them, you have to care for a useful data selection yourself and, for instance, incorporate all required key fields if several linked basic tables are used. The condition is to be specified using SQL syntax and stored in the database unchecked when the view is created. Because the syntax depends on the connected database, the form contains the condition field SQL-Text/ORACLE.


View Formats

DataView knows two view formats: The DataView view and the foreign view. For classification the terms main table and auxiliary table are introduced. The main table is the table which the first field in the view points to (depending on sequence entry). All other tables used are auxiliary tables.

DataView view (V)
If the main table of the view is a DataView table, the view automatically contains the DataView system fields C_ID, C_VERSION, C_UIC, C_GIC, C_AGG_OGW, and for relation tables additionally C_ID_1 and C_ID_2.

If auxiliary tables of the view of DataView tables, DataView automatically creates C_ID fields with the names TABLENAME_ID and TABLENAME_VERSION and inserts them into the view. Basic fields are the corresponding system fields C_ID and C_VERSION of the incorporated DataView tables.

If foreign format tables are part of the view -- irrespective of whether they are main or auxiliary tables -- no system fields for these tables can be incorporated into the view.

Note! In lists with DataView as the controlling entity you can, just as for lists with a DataView table as controlling entity, displaye the system fields via [Ctrl+A]. The different number of system fields will quickly show you, whether the list points to a view or to table.

Foreign format view (W.)
The view never contains any system fields, even if the main table or any auxiliary table are DataView tables.

Note! It is not sufficient only to specify type W. Additionally, you will always have to specify which attribute is available as key. The specification of key fields is done in the foreign key flag F of the table-field assignment.

If DataView tables are part of the view and put into a relation using DataView relations, these relations will have to be replaced by links.


Grouping

Groupings are required if you want to use evaluations like counting, calculating average values or calculating sums in queries to the DataView. For this purpose the view definition must contain a SELECT with GROUP BY a (-> refer to SQL documentation of the connected database).

You have the grouping if you calculate data from basic field contents for at least one view, and in path name using the group functions SUM(), AVG(), MIN(), MAX(), CONT() or they statistic functions STDDEV(), VARIANCE(). All other basic fields without grouping function are then automatically taken into the column list of the GROUP BY statement; in case of DataView views additionally all system-internal C_ID fields. Because the view does not have to contain fields of all participating tables, DataView automatically also searches the view condition for table names.

Because grouping requires identical values in the grouped columns, but system-internal columns in DataView tables may contain different values (ID numbers), groupings are usually only useful within foreign views.


Creating and Modifying Views

Views are created with Create Table/View in the header form. DataView will then store the view in the database. If you update a view definition, the function (as opposed to tables) cannot modify the view in the database. It can only completely delete and then recreate it.

Note! In case of changes to basic tables you should always check, whether views are affected. Especially when you delete basic fields or change types of basic fields, you will explicitly have to maintain view fields and then recreate the view.


Important Limitations for Views

The identification of records in the view is always done with the help of the entries in the key fields of the controlling basic tables (C_IDs in case of DataView tables, foreign key entries in case of foreign format tables).

If a view points to several basic tables, only data queries can be executed. Update, Insert, or Delete operations are bound to the DataView-internal procedures and not possible via such a view. However, this restriction can be avoided by creating derived -> data classes mapped as views.

View fields cannot be indexed and cannot be assigned any uniqueness IDs.

Ist an einer View-Maske das Zugriffsformular installiert, kann nach Öffnen des Formulars der Datensatzzugriff weder eingesehen noch geändert werden. Soll der Datensatzzugriff sichtbar sein, müssen Sie explizit die Systemfelder C_CRE_DAT and C_UPD_DAT in den View aufnehmen.


Example for a View

Let's assume you quickly have need a mask in our Meditronic sample application, which displays the price of all materials from suppliers in Berlin in Euro. The solution would be a view to the master table of suppliers with an additional view field for calculating the Euro price. The figure above displays the definition of the view (MED-VIEW-ART) and its fields in the Entity Data form. The view fields MED_VIEW_ART.NUM,.BEZ and .VKP point to equally named fields in the material master table, the contents of the view-only field MED_VIEW_ART.EURO is calculated with the SQL string NVL (MED_ARTIKEL.VKP, 0.0)/1.) in path name. Because the material entity in theMeditronic data model is linked to the supplier entity with a DataView relation, the number of hits can be limited to materials from suppliers in Berlin by a view condition. The condition is MED_ARTIKEL.C_ID IN (SELECT C_ID_1 FROM MED_ART_LIEF WHERE C_ID_2 IN (SELECT C_ID FROM MED_PARTNER WHERE ORT='Berlin')) and it is entered in the View Condition sub-list (not visible in the figure).