Create a database view based on an Alfabet query

  1. Open the Meta-Model tab in Alfabet Expand.
  2. Right-click the Database Views explorer node and select Add New Database View
  3. Click the new database view and in the attribute window, set the Type attribute to AQL . The attributes are changing to the attributes relevant for Alfabet query language based attributes.
  4. Set the attributes:
    • Name: Define a name for the database view configuration object. The name must be unique. It is used to identify the database view object in the explorer of Alfabet Expand.
    • Technical Name: Define the name of the virtual database table that shall be created via the database view. Queries that want to read data from the database view must refer to the database view with this name. The technical name must be in compliance with the rules that apply to table names on your database server. Special characters and reserved keywords as well as names of already existing database tables must not be used.
    • Query/AQL Query as Text: Define the content of the database view via an Alfabet query in a simple text editor ( AQL Query as Text ) or in the Alfabet query builder ( Query ). Sort properties and Alfabet query language instructions must not be defined for the Alfabet query. The definition of Show Properties is optional:
      • If Show properties are defined for the Alfabet query, the number of columns correspond to the number of show properties defined. Show Properties of the type RoleType and Indicator are ignored. Only Show Properties of the type Property are used to create columns in the database view.
      • If no show properties are defined, all properties except for properties of the type ReferenceArray are added to the database view. Optionally, properties of defined data types can be excluded from the database view table by setting the attribute Filter Show Properties of the database view.

      The column names in the database view are derived from the query in the following way:

      • The column name is concatenated as object class caption followed by property caption delimited by a whitespace. For example for the property Start Date of an Application , the resulting column name is Application Start Date. Please note that special characters are not stripped from the captions when building the database view column captions. If no caption is defined, the Name of the object class or object class property is used.
      • If Show Properties are defined and an Alias is defined for a property in the Show Properties, the defined Alias is used instead of the concatenated column name.
      • If an Alias is defined for the object class in the Alfabet query, the alias is used in the column name instead of the class name. An alias can be used for example to avoid name abbreviation by the system for column names longer than 30 characters.
      • If the resulting column name is longer than 30 characters, the name will be cut after 30 characters, corresponding to the string length restrictions that apply on Oracle® database servers. Depending on the property caption, this might cause problems if the resulting column names are identical in the first 30 characters. Alias definitions in the query can help solving such problems.
    •  Filter Show Properties: This attribute is only relevant if the Alfabet query defining the database view has no Show Properties. If set to True, the following additional attributes are added to the attribute section. You can use the attributes to exclude object class properties with a defined data type from the database table view:
      • Exclude Boolean Properties: Select True to exclude all object class properties with the attribute Property Type set to Boolean from the database view.
      • Exclude Color Properties: Select True to exclude all object class properties with the attribute Name containing the string Color from the database view.
      • Exclude Icons: Select True to exclude all object class properties with the attribute Name containing the string Icon from the database view.
      • Exclude Last Update: Select True to exclude all object class properties the attribute Name set to either LAST_UPDATE , LAST_UPDATE_USER , CREATION_DATE or CREATION_USER from the database view.
      • Exclude Reference Properties: Select True to exclude all object class properties with the attribute Property Type set to Reference from the database view.
    •  Description: Optionally, enter information about the purpose of the database view. This information is displayed to solution designers in Alfabet Expand only.
  5. In the toolbar, click the Save  Save_Expand button to save your changes.