In its simplest form, the SELECT clause takes the form of a comma-separated list of the fields that you want to retrieve from the database. The fields that you specify in this clause have to exist in the table that you specify in the FROM clause (which defines the database table from which you want to retrieve the data).
Most of the retrieved fields are presented as columns in the resultant query. However, some database fields have a special role (see Fields that Perform Special Functions), and as such are not actually displayed as columns in the resulting query. You need to be aware of which ‘special’ fields you need to include in the comma-separated list, in order to obtain the query results and functionality that you require.
Specify the fields in the SELECT clause in the order in which you want those fields to be displayed as columns in the resultant query. (The exceptions to this are the ‘special’ fields which are not displayed as columns in the resultant query, and any fields that you prefix with an underscore (see below).)
In the SELECT clause, include the fields that you want to use in the SQL query’s ORDER BY (and GROUP BY) clauses.
If you need to include a field in the SELECT clause, but do not want that field to be displayed in the resultant query, assign an alias to that field and prefix the alias with an underscore (_). The syntax for this is:
<field> AS "_<alias>"
(with the field’s alias enclosed in double straight quotation marks)
Example:
"CurrentQualityDesc" AS "_CurrentQualityDesc"
To retain a column as a ‘fixed’ column, so that it remains on view whenever the horizontal scroll bar is used on a List, assign an alias to the field that represents that column, and prefix the alias with a tilde (~). The syntax for this is:
<field> AS "~<alias>"
(with the field’s alias enclosed in double straight quotation marks)
Example:
The ‘Name’ column on a List is to remain on view whenever a user scrolls across the List data.
The ‘Name’ column is displayed by specifying the FullName database field. (‘Name’ is the field’s display name—the string that is displayed in the language that is associated with the logged on user’s specified Locale.)
To ensure that the column remains on view, the SELECT clause includes this syntax:
"FullName" AS "~FullName"
To right-align a column’s content, assign an alias to the field that represents that column, and prefix the alias with the ‘greater than’ sign (>). The syntax for this is:
<field> AS "><alias>"
(with the field’s alias enclosed in double straight quotation marks)
Example:
The entries in the ‘Value’ column on a Points List are to be right-aligned.
The ‘Value’ column is displayed by specifying the CurrentValueFormatted database field. (‘Value’ is the field’s display name—the string that is displayed in the language that is associated with the logged on user’s specified Locale.)
To right-align the ‘Value’ column’s content, the SELECT clause includes this syntax:
"CurrentValueFormatted" AS ">CurrentValueFormatted"
By default, column headings are displayed as a single line. For column headings to span multiple lines in a List, assign an alias to the field that represents a column, and insert \n within the alias, at each point at which the heading is to continue onto a separate line.
Example:
The heading for the ‘Number of Updates’ column on a Points List is to span multiple lines.
The ‘Number of Updates’ column is displayed by specifying the ProcessCount database field. (‘Number of Updates’ is the field’s display name—the string that is displayed in the language that is associated with the logged on user’s specified Locale.)
For the heading to span a second line, the SELECT clause includes this syntax:
"CProcessCount" AS "Numberof\nUpdates"
This results in the text ‘Number of’ appearing on one line, with ‘Updates’ appearing on the line below.
You can also use the SELECT Clause to assign color properties to specific fields. This enables you to:
- Control the number of columns in which entries change color
- Specify the color attributes that determine the color of those entries.
For more information, see Lists in Which only the Entries in Particular Columns Change Color in the ClearSCADA Guide to Lists.
Whenever you assign as alias to a field, you have to specify that alias, rather than the field name, in any WHERE, ORDER BY, or GROUP BY clauses.