Define column name and caption in native SQL
When you define a native SQL query, the column names and captions are derived from the SELECT clause. Column names and captions are identical.
When no alias is specified for a column header in the SELECT clause, the property name is used as a column header.
In native SQL queries, names of database columns and database tables in the Alfabet database must be written in upper-case letters.
The following native SQL query finds applications and returns the properties Name and Version:
- SELECT APPLICATION.REFSTR, APPLICATION.NAME, APPLICATION.VERSION
- FROM APPLICATION
The resulting column names and captions are:
| Column Name | Column Caption |
|---|---|
| NAME | NAME |
| VERSION | VERSION |
If two properties have the same name, the column headers in native SQL have the same name too. When specifying native SQL in the context of Alfabet, two column headers with the same name are not allowed. It is recommended that you specify an alias name for the columns. If you do not specify an alias, the first column is given the name of the property and the following column headers are named <property name><number>.
A native SQL query finds applications and returns the name and version of the application and the name of the ICT object the application is assigned to:
- SELECT APPLICATION.REFSTR, APPLICATION.NAME, APPLICATION.VERSION, ICTOBJECT.NAME
- FROM APPLICATION, ICTOBJECT
- WHERE APPLICATION.ICTOBJECT = ICTOBJECT.REFSTR
The resulting column names and captions are:
| Column Name | Column Caption |
|---|---|
| NAME | NAME |
| VERSION | VERSION |
| NAME1 | NAME1 |
Setting an alias in the SELECT clause not only changes the caption but also changes the name of the database column.
Aliases are defined for the native SQL query of the example above to avoid identical column names:
- SELECT APPLICATION.REFSTR, APPLICATION.NAME AS 'Application Name', APPLICATION.VERSION AS 'Application Version', ICTOBJECT.NAME AS 'ICT Object Name'
- FROM APPLICATION, ICTOBJECT
- WHERE APPLICATION.ICTOBJECT = ICTOBJECT.REFSTR
The resulting column names and captions are:
| Column Name | Column Caption |
|---|---|
| Application Name | Application Name |
| Application Version | Application Version |
| ICT Object Name | ICT Object Name |