Query Language Extension for Data Composition System

A query language extension for the data composition system is performed using special syntax instructions enclosed in braces and placed directly in the query text.

Syntax Elements of Query Language Extension for Data Composition System

SELECT

This clause describes the fields that can be selected by the user for output. This keyword is followed by comma-separated field aliases from the main query selection list that are available for setup.

Example:

{SELECT Products, Warehouse}

A field alias can be followed by a combination of ".*" characters that imply child fields of this field can be used.

Thus, Products.* means Products child fields (e.g., Products.Code) can be used.

 The SELECT element can only be included in the first query of a union.

WHERE

It describes the fields where the user can apply filtering. This clause uses table fields. Selection list field aliases are not allowed. Each union part can contain its own WHERE element.

Examples:

{WHERE Products.*, Warehouse }

{WHERE Document.Data >= &BeginDate, Document.Data <= &EndDate}

If no parameter values are specified, the WHERE clause is no included in the resulting query.

Properties (characteristics) and categories

Query language extension for data composition system allows to specify where to search for types and values of characteristics for one or another database object.

Also you can specify this using additional characteristics of metadata object.

If information on characteristics is specified both in the query (using CHARACTERISTICS structure) and using the metadata dialog, then the CHARACTERISTICS structure has the priority.

Syntax:

CHARACTERISTICS
         TYPE(<Type name>)
         CHARACTERISTICTYPES <Table name> | (<Query union>)
         KEYFIELD <Field>
         NAMEFIELD <Field>
         [VALUETYPEFIELD <Field>]
         CHARACTERISTICVALUES <Table name> | (<Query union>)
         OBJECTFIELD <Field>
         TYPEFIELD <Field>
         [VALUEFIELD <Field>]

TYPE is followed by <Type name> -  name of the type for which characteristics are described.

CHARACTERISTICTYPES is followed by <Table name> | (<Query union>) -  table name or a query to obtain a list of characteristics.

KEYFIELD is followed by <Field> -  name of a field from a table or a CHARACTERISTICTYPES query  which contains a key of characteristic type.

NAMEFIELD is followed by <Field> - name of a field from a table or CHARACTERISTICTYPES query which contains characteristic name.

VALUETYPEFIELD is followed by <Field>  - name of a field from a table of CHARACTERISTICTYPES query which contains characteristic value type.

CHARACTERICTICVALUES is followed by <Table name> | (<Query union>) -  table name or a query to obtain characteristic values.

OBJECTFIELD is followed by <Field> - name of a field from a table of CHARACTERISTICVALUES query which contains an object for which the characteristic is specified.

TYPEFIELD is followed by <Field> - name of a field from a table of CHARACTERISTICVALUES query which contains a characteristic type.

VALUEFIELD is followed by <Field> - name of a field from a table of CHARACTERISTICVALUES query which contains a characteristic value.

Example:

{CHARACTERISTICS TYPE(Catalog.Products)
CHARACTERISTICTYPES

(SELECT
    AddPropertiesTypes.Ref,
    AddPropertiesTypes.Description,
    AddPropertiesTypes.ValueType
FROM
    ChartOfCharacteristicTypes.AddPropertiesTypes AS AddPropertiesTypes)
KEYFIELD

Ref
NAMEFIELD Description
VALUETYPEFIELD ValueType
CHARACTERISTICVALUE InformationRegister.AddProperties
OBJECT FIELD Products
TYPEFIELD PropertyType
VALURFIELD Property
}

In this example characteristics are described for fields of reference to the "Products" catalog type.

Parameters

Besides the main elements, the data composition system can receive elements written in virtual table parameters. In this case the field type depends on the type of the parameter that contains the elements.

Example:

SELECT
ProductsAccountingTurnovers.Product AS Product,
ProductsAccountingTurnovers.Warehouse AS Warehouse,
ProductsAccountingTurnovers.QuantityReceipt AS QuantityReceipt,
ProductsAccountingTurnovers.QuantityExpense AS QuantityExpense,
FROM
AccumulationRegister.ProductsAccounting.Turnovers({&BeginDate},
{&EndDate},
,
{Products.*,
Warehouse.*}) AS ProductsAccountingTurnovers

BeginDate, EndDate, Product and Warehouse fields in this example become available for filtering, i.e. the user can apply filters to them.

Automatic Filling of Available Fields

The following is performed when available query fields are autofilled:

  • All selection list fields and their child fields become available for filtering ordering, grouping, selecting, etc.;
  • Virtual table parameters become available for filtering.

1C:Enterprise Developer's Community