Create a database view based on a native SQL 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 node in the explorer and set the attributes:
    • Type: Select NativeSQL.
    • 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/Native SQL Query as Text: Define the content of the database view via a native SQL query in a simple text editor ( Native SQL Query as Text ) or in an Alfabet specific text editor with integrated help that provides information about the Alfabet meta-model ( Query ). The query must have a SELECT statement. The column names defined in the SELECT statements are the column names for the database view. Queries defined to read data from the database view must use the defined names to refer to columns of the database view table.
      • Unlike all other native SQL query definitions for configuring Alfabet, the first definition of the SELECT statement is NOT removed from the resulting dataset. The complete definition in the SELECT statement results in database view table columns.
      • The table names defined as for example APPLICATION.NAME will result in a column name NAME only. To avoid problems with name specifications it is recommended to define an alias with the desired name in the SELECT statement for 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.
  4. In the toolbar, click the Save  Save_Expand button to save your configuration and create the database view in the database. The Created attribute of the database view should then be set to True . You need to rescan the explorer tree to view the change. If the Created attribute it is set to False , and an error message is displayed in the attribute Last Error, the query defined for the query attribute needs correction.

You can re-create your database views using the Recreate Database Views option in the context menu of the Database Views explorer node.