Define the SELECT clause

The Alfabet software reads the result table returned via the SELECT clause to evaluate the data required to execute the process for which the query was defined. Therefore, the caption and order of the columns in the report is crucial for the execution of the functionality in which the query is involved. The required order and caption of report columns is described in the context of the documentation of the relevant functionality.

In Alfabet, the evaluation of SELECT clauses differs from the interpretation by SQL parsers. When defining the native SQL query, you must take into account that your result table is subject to the following changes:

• The first column of the result set is not displayed in the query results. It must specify the REFSTR of the object class selected for further processing by Alfabet components. All but the basic functionality to display the query results in a table is disabled when the REFSTR is not specified in the first column of the SELECT clause. For example, features like expandable report tables and the display of results in standard Alfabet report formats cannot be used.

  If you define a configured report of the type NativeSql and you do not want users to navigate to objects from the report, you can specify NULL as first column instead of the REFSTR of an object class. The first column is then ignored and navigation to objects from the report is disabled.

• Names of database columns and database tables in the Alfabet database must be written in upper case letters in native SQL queries while names of temporary tables resulting from WITH statements must be written in lower case letters. The database table and column names are defined by the Tech Name attributeof the object class or object class property. The value of that attribute is not necessarily identical with the value of the Name attribute of the object class or object class property.
• When no alias is specified for a column header in the SELECT clause, the property name is used as a column header. When specifying native SQL in the context of Alfabet, multiple column headers with the same name are not allowed. If two properties have the same name, for example, if you display the NAME of applications and the NAME of local components, the first column will have the name of the property and the following column headers are re-named to <property name><number>, which will result in NAME1 for the second column header in the example. It is recommended that you specify an alias name for the columns.
• Column headers of the query result set are all converted to all upper-case letters if not written in inverted commas. In Alfabet, column headers of the query result set are often used for further processing and must meet specific output formats. Although in most cases the Alfabet components adapt to this database condition, it is recommended that you either write alias names in inverted commas or only use upper-case letters in your alias specifications.