Selection Fields Description
Selection field list
Selection field
Field description
Expression
Field alias
Field group
List of nested fields
Nested field
Empty table description
Following the required keyword SELECT (and qualifying keywords ALLOWED, DISTINCT and TOP), selection field list is specified in the query text. These fields will be processed when selecting data in the query. The query result will also have a field set defined in this list. Selection fields are described as follows:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||
Selection field list consists of a single or multiple comma separated items. Instead of listing fields, you can use an asterisk * in the selection list. It means that the query result should contain all fields that exist in the source tables of the query included in the list of sources.
Note: Specifying an asterisk * in the selection field list excludes virtual fields of the source tables from the result.
Each <Selection field> contains a description of the field and an optional field alias.
Field description defines how values of the field should be generated. In the simplest case, selection field is a link to a field in the source table. A link can include specification of the table containing this field, or it can be defined without specifying the table. Field dereferencing is described in Field dereferencing.
In general, selection field can represent an expression, rather than just a source table field reference.
Query results can be grouped using aggregate functions specified in the selection fields as expressions.
You can assign an alias to any selection field. <Field alias> can later be used for more convenient referencing of this field. If you assign an alias to a selection field, you can access this field in the future using its alias in the ORDER BY and TOTALS clauses as well as when working with query results. This can be more convenient, and in some cases it is the only option.
The AS keyword can precede the field alias. You can omit this word at all, but when indicated it improves the clarity and readability of the query text.
Field aliases are defined according to the rules for assigning identifier variables. Aliases in a query cannot be the same.
Assigning aliases does not affect data selection in the query.
<Field group> can be specified only when the selection field refers to a nested table. In this case, you can define which fields must be processed in the selection from the nested table. If the field group is omitted, all fields of the nested table are processed. A field in the selection list can refer to a nested table of a query data source. In this case, the field of query result will be of QueryResult type, i.e. contain a nested query result generated based on the nested source table.
By default, all the fields of the nested table (data source) are included in the nested result. You can explicitly define a group of fields which must be included in the nested query result.
<List of nested fields> consists of a single or multiple comma separated items. If the list consists of one item, it does not need to be enclosed in parentheses.
Instead of listing nested fields, you can use an asterisk * which means that the nested query result should contain all the fields available in the nested table.
<Nested field> can represent some expression. In the simplest situation <Expression> is a link to a field in a nested table.
If the query contains a merge, and some parts of the merge include nested tables while others do not, it is required to add empty nested tables to the selection list fields. Use the keyword EMPTYTABLE followed by the field aliases in parentheses, which are to make up the nested table.
Example:
// Select Number and Content fields
// from the Document.Invoice virtual table
SELECT Reference.Number, EMPTYTABLE.(Number, Article, Quantity) AS Content
FROM Document.ExpInvoice
MERGE ALL
SELECT Reference.Number, Content.(LineNumber, Article, Quantity)
FROM Document.Invoice Document.Invoice.Content.*