1C:Enterprise 8.3. Developer Guide. Contents

WORK WITH QUERIES

A special Query object is used by the system to generate and execute database table queries. Query objects are convenient to use when you need to get a complex data selection grouped and sorted as needed. The summary of the accounting register condition for a specific point in time is a classical example of query use. You can also use the query mechanism to get information for different points in time.

8.1. QUERY DATA SOURCES (TABLES)

You can use database tables as query language data sources. The tables are divided into two main classes: real and virtual.

Real tables are stored in the database, i.e. are interpreted on the basis of an existing database table. If a real table is used, calculated fields may be present and their values are calculated as a function of several real fields.

Virtual tables are not stored in the database. When calling information from virtual tables, the system automatically collects information from real database tables to execute the query. A virtual table can be parameterized, i.e. real filling of a virtual table can be defined by values of parameters whose actual values are specified in the query text.

Each virtual table has a name which is used in queries for identification purposes. The table name can be defined in English and Russian. For example, Catalog.Goods. Table and field names may not be the same as query language keywords.

The so-called object tables form a separate subclass of tables. A real database table must be used as an object table. The notional difference of object tables from any other tables is expressed in the term itself – object tables are used to store 1C:Enterprise system object statuses, such as catalogs, documents and so on. Each object table has a matching object type in the 1C:Enterprise system. For example, one table corresponds to the objects of Catalog.Goods type, while another table corresponds to the objects of Catalog.Contractors type. Each individual record of an object table stores the status of an individual object of a corresponding type. Accordingly, each object table has a Ref field which is a reference to the current record. Moreover, object tables have a way of obtaining the user representation of an object from the record field contents.

Object tables can also be hierarchical. Hierarchical tables have a special Parent field that contains a reference to the record which, according to the hierarchy, is a parent of the current record.

The following can be used as table fields:

A virtual or real table field A nested table.

The main difference between a standard field and a nested table is that a standard field corresponds to a single value within a record, while a nested table corresponds to a QueryResult type value with a predefined set of columns. An example of a nested table is a document or catalog tabular section.

The NULL type value can be used as a table field value type. Such values are included in table record fields, for which the given field is undefined or meaningless. For example, values of this type are included in the records (belonging to catalog folders) for the fields that are defined only for the items of this catalog.

8.2. QUERY LANGUAGE

As described earlier, executing a query requires generating query text. Query text represents guidelines used for query execution. The query text describes infobase tables used as query data sources, table fields that require processing, grouping rules, result sorting, etc.

The guidelines are composed in a special language – query language and consist of separate parts – sections, clauses, words, functions and comments. This chapter will describe functions and methods to use with all query language constructs.

8.2.1. Syntax Diagram for Query Language Constructs

This chapter describes query language syntax using a set of rules. Each rule describes one language construct.

Each construct can contain keywords, separators (periods, commas, parentheses) and other language constructs.

<Language Construct>

THIS_IS_KEYWORD
<This_is_language_construct>,  <This_is_language_statement>
THIS_IS_FUNCTION  ( <This_is_language_construct> )

The rules that describe the query language include language constructs enclosed in angle brackets. Keywords and function names are capitalized.

Language constructs can contain optional items, e.g. keywords. The rules that describe the query language include optional items enclosed in brackets:

[THIS_IS_OPTIONAL_WORD]
[<This_is_optional_construct>]

In some cases, alternative elements can be used in a language construct. These elements are separated by a vertical bar:

THIS_WORD  | OR_THIS_WORD
<This_construct>  | <Or_this_construct>

Descriptions of all constructs include examples explaining their order of use in the query language.

8.2.2. Comments in Query Language

Query text can include comments. Comments are sections of strings, beginning with the a sequence of // characters and continuing to the end of the string:

// This is a comment

Comments are ignored when executing the query.

NOTE

The query wizard removes comments from the text.

8.2.3. Use of Predefined Configuration Data

Query text can contain the following predefined configuration data:

enumeration values

predefined data for:

○ catalogs

○ charts of characteristic types

○ charts of accounts

○ charts of calculation types

empty references

business process route point values

The query text can also contain system enumeration values which can be assigned to fields in the database tables: AccumulationRecordType, AccountType and AccountingRecordType.

Predefined configuration data and system enumeration values can be referenced in queries using literals of functional type:

VALUE(<ValuePresentation>)

For system enumerations, the value presentation looks like the following:

Valid names of system enumerations are listed above. The list of allowable values is given in each description.

For predefined configuration data, the value presentation looks like the following:

Predefined values can have one of the following types:

Catalog

ChartOfCharacteristicTypes

ChartOfAccounts

ChartOfCalculationTypes

Enum

The name of a metadata object matches the name indicated in the Designer.

For enumerations defined in the configuration, the value is specified as the name of the corresponding metadata object of the EnumValue type. For other types of predefined values, the value is specified as a predefined data item name indicated in the Designer or as EmptyRef (specifies an empty reference).

For business process route points, the value presentation looks like the following:

BusinessProcess.<MetadataObjectName>.RoutePoint.<RoutePointName>

Some fragments of queries explaining the use of predefined data in queries are given below:

WHERE  City = VALUE(Catalog.Cities.Moscow)

WHERE  City = VALUE(Catalog.Cities.EmptyRef)

WHERE  ProductType = VALUE(Enum.ProductTypes.Service)

WHERE  RecordType = VALUE(AccumulationRecordType.Receipt)

WHERE  RoutePoint = VALUE(BusinessProcess.Match.RoutePoint.Accept)

8.2.4. Query language keywords

The list below provides query language keywords.

AUTOORDER	MAX	AUTOORDER	MAX
BOOLEAN	BETWEEN	BOOLEAN	BETWEEN
IN	MONTH	IN	MONTH
OUTER	MIN	OUTER	MIN
INNER	MINUTE	INNER	MINUTE
ASC	BEGINOFPERIOD	ASC	BEGINOFPERIOD
ALL	NOT	ALL	NOT
CASE	WEEK	CASE	WEEK
SELECT	UNDEFINED	SELECT	UNDEFINED
CAST	OVERALL	CAST	OVERALL
WHERE	UNION	WHERE	UNION
YEAR	TOP	YEAR	TOP
DATE	PERIODS	DATE	PERIODS
DATETIME	LIKE	DATETIME	LIKE
TENDAYS	FULL	TENDAYS	FULL
DAY	HALFYEAR	DAY	HALFYEAR
DAYOFYEAR	INTO	DAYOFYEAR	INTO
WEEKDAY	RIGHT	WEEKDAY	RIGHT
FOR UPDATE [OF]	PRESENTATION	FOR UPDATE [OF]	PRESENTATION
DATEADD	EMPTYTABLE	DATEADD	EMPTYTABLE
IS	DISTINCT	IS	DISTINCT
ISNULL	ALLOWED	ISNULL	ALLOWED
VALUE	GROUP BY	VALUE	GROUP BY
AND	SECOND	AND	SECOND
HIERARCHY	JOIN … ON	HIERARCHY	JOIN … ON
HIERARCHY	ESCAPE	HIERARCHY	ESCAPE
FROM	SUBSTRING	FROM	SUBSTRING
OR	SECOND	OR	SECOND
HAVING	AVG	HAVING	AVG
ELSE	REFS	ELSE	REFS
INDEX BY	STRING	INDEX BY	STRING
TRUE	SUM	TRUE	SUM
TOTALS … BY	TYPE	TOTALS … BY	TYPE
AS	VALUETYPE	AS	VALUETYPE
QUARTER	THEN	QUARTER	THEN
WHEN	ONLY	WHEN	ONLY
COUNT	DESC	COUNT	DESC
ENDOFPERIOD	ORDER BY	ENDOFPERIOD	ORDER BY
END	HOUR	END	HOUR
LEFT	NUMBER	LEFT	NUMBER
FALSE	DROP	FALSE	DROP

NOTE

Keyword names are not case sensitive.

8.2.5. Main Sections of Query Text

You can describe the query text using the following rule:

<Query text>

<Query  description>
[<Query  union>]
[<Results  ordering>]
[AUTOORDER]
[<Totals  description>]

As you can see from this rule, the query text consists of several parts or sections:

<Query description> – this is the only required section in the query text and in many cases this is all you need. The section describes query data sources, selection fields, groups, etc. In its turn, it is described by a whole set of rules as explained below.

<Query union> – the query language allows you to consolidate the results of several queries. For a description of the query union see page 1-463.

The <Results ordering> section helps define the string ordering conditions in the query result. For a description of query result ordering see page 1-464.

AUTOORDER allows you to include automatic ordering string mode in the query result. For a description of this mode see page 1-467.

The <Totals description> section lets you specify what totals must be calculated in the query. For a description of this section see page 1-468.

8.2.6. Query Description

As was earlier noted, a query description section must be included in the query text, defining the following:

fields to be included in the query result

query data sources – source tables

conditions affecting data selection in the query

order for groups of query results

The query description section consists of several interrelated clauses:

<Query description>

SELECT  [ALLOWED] [DISTINCT] [TOP <Count>]
<List of selection fields>
[FROM  <Source list>]
[WHERE  <Filter criterion>]
[GROUP  BY <Group fields>]
[HAVING  <Filter criterion>]
[FOR  UPDATE [<Top level tables list>]]

The query begins with the required SELECT keyword.

The ALLOWED keyword means that the query selects only those records for which the current user has rights. If you do not indicate this keyword, the query selects records for which the user does not have rights and an error occurs. This keyword can only be present in the top-level SELECT clause and is applied throughout the entire query, including the nested queries.

Using the DISTINCT keyword, you can indicate that duplicate strings must not be included in the result.

The TOP <Count> clause allows you to give the maximum number of rows in the query result. The first rows will be shown (according to the ordering rules for query results). The Count value should be an integer. You can also use the query language to order nested queries if they contain the TOP statement.

The <List of selection fields> section describes the fields which must be included in the query result (for rules of selection field description see page 1-450).

The FROM <Source list> clause indicates the data sources, i.e. infobase tables processed in the query. You can omit the source description only if the sources are completely defined in the list of selection fields (for rules of query data source description see page 1-453).

The WHERE <Filter criterion> clause allows you to filter off the query results. Records are only included in the result if this clause is true for them (for rules of filter criteria description see page 1-460).

You can use the FOR UPDATE clause to indicate the need to lock the data which you read during the transaction. These data become inaccessible for other sessions. In the file mode version, the specified tables are locked, while in the client/server mode the lock is only applied to the selected records. The lock is released after the transaction is completed.

The GROUP clause allows you to describe the order of query result grouping (see page 1-461).

The HAVING clause allows you to apply conditions to group results (see page 1-462).

All the examples in this chapter contain query text and query result. It is implied that the query text is passed to the Execute() method of the Query object as a parameter.

The following is a simple example of a query consisting of a single SELECT operator and a list of selection fields.

Example:

//  A list of consignment notes must be added to the report.

Select
Document.Invoice.Ref

Result:

Fig. 179. Query Result

8.2.6.1. Using DISTINCT Keyword

In many situations it is desirable not to include duplicate rows in a report.

Example:

//  It is necessary to find out to what contractors
//  the goods were shipped in a particular period.

Select
Document.Invoice.Contractor

Result:

Fig. 180. Query Result

As you can see, there are many duplicate lines in the query result, reducing its clarity. To prevent repetition, you can use the DISTINCT keyword in the query description.

Example:

Select  Distinct
Document.Invoice.Contractor

Result:

Fig. 181. Query Result

8.2.6.2. Using TOP Keyword

Sometimes you need to limit the count of rows in a report. To accomplish this you must specify the TOP keyword in the query description, followed by the required count of rows.

Example:

//  You want to select five most expensive goods.
//  Selection must be in the descending order of goods price.

Select  Top 5
Catalog.Nomenclature.Description,
Catalog.Nomenclature.PurchasePurchasePrice

Order  by
Catalog.Nomenclature.PurchasePurchasePrice  Desc

Result:

Fig. 182. Query Result

8.2.7. Selection Fields Description

A list of selection fields is given after the required SELECT keyword (and clarifying words DISTINCT and TOP) 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: <List of selection fields>

<Selection field>[, <Selection field>[, …]] | *

<Selection field>

<<Field description>> [[AS] <Field alias>]

<Field description>

<Empty table description>

EMPTYTABLE.(<Aliases list>)

<Aliases List>

[<Field alias>][,<Aliases list>]

The selection field list consists of one or more comma-separated items. Each selection field consists of the selection field description and an optional field alias.

Instead of fields listing, you can use an asterisk (*) in the selection list. It means that the query result should contain all fields that exist in the query's source tables (data sources) included in the list of sources.

NOTE 1

When using an asterisk (*) in the selection fields list, the result will not include virtual fields of the source tables.

NOTE 2

Retrieval of large selections (more than 64 MB) requires sufficient free space on the hard drive used to store the server and client's temporary files.

Field description describes how field values must be generated. In the simplest case, a selection field is a reference to a field in the source table. The reference can include specification of the table containing this field or it can be defined without specifying the table. For information about dereferencing fields see page 1-474.

In general, a selection field can represent an expression, rather than just a source table field reference (for details see page 1-470).

Query results can be grouped (see page 1-461) using aggregate functions (see page 1-474) specified in the selection fields as expressions.

You can assign an alias to any selection field (see page 1-451). You can use the field alias for easy access to the field.

You can use fields group only when the selection field references a nested table (see page 1-452). In this case you can define which fields must be processed in the selection from the nested table. If the fields group is omitted, all fields of the nested table are processed.

8.2.7.1. Field Aliases in the Selection List

If you assign an alias to a selection field, then you can access this field, 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, but when indicated it improves the clarity and readability of the query text.

Field aliases are defined according to the rules of variable identifier assignment. Aliases in a query cannot be the same.

Assigning aliases does not affect data selection in the query.

Example:

//  You want to select
//  product and group names from the Goods catalog.

Select
Catalog.Nomenclature.Name  AS Product,
Catalog.Nomenclature.Parent.Name  As Group
From
Catalog.Nomenclature

Result:

Fig. 183. Query Result (Fragment)

Please note that fields in the query result are named Product and Group. If you did not specify field aliases, the fields in the query result would be named Name and Name1 (field names in the query result cannot be same, therefore 1 is automatically added to the second field name) which is much less convenient.

8.2.7.2. Nested Tables in the Selection Fields List

A field in the selection list can refer to nested tables of query data sources. In this case the field in the query result will have the QueryResult type, containing a nested query result, generated based on the nested source table.

By default, all fields of the nested table (data source) are included in the nested result. You can overtly define field groups which must be included in the nested query result. The group of nested result fields is described by the following rules: <Fields group>

( <List of nested fields> ) | *

<List of nested fields>

<Nested field>[, <Nested field>[, …]]

<Nested field>

<Expression> [[AS] <Field alias>]

A list of nested fields consists of one or more comma-separated items. If the list consists of one item, then it does not need to be enclosed in parentheses.

Instead of nested fields listing, you can use an asterisk ("*"); this means that the nested query result must contain all fields of the nested table.

A nested field can represent an expression (see page 1-473). In the simplest case an expression is a reference to the nested table field.

Each nested field can have an alias. Like the field aliases of the selection list, these field aliases can be used to access the fields more conveniently (see page 1-451).

You can assign aliases to nested fields regardless of whether or not the alias of the nested table itself is given.

Example:

//  Consignment note specifications (the document, assortment and //quantity)  must be added to the report.

Select
Document.Invoice.Ref,
Document.Invoice.Contents.(Nomenclature  As Product, Count)

Result:

Fig. 184. Query Result (Fragment)

Please note that the Content field in the query result appears as a nested table with Nomenclature and Count fields.

8.2.7.3. Empty Nested Tables in the Selection List

If the query contains a union and some parts of the union include nested tables, while others do not, it is necessary to add empty nested tables to the selection list fields. Use the EMPTYTABLE keyword followed by the field aliases in parentheses; these fields will make up the nested table.

Example:

SELECT  Ref.Number, EMPTYTABLE.(Number, Article, Quantity) AS Content
FROM  Document.Invoice
UNION  ALL
SELECT  Ref.Number, Content.(LineNumber, Nomenclature, Quantity)
FROM  Document.Invoice

8.2.8. Query Sources Description

The purpose of the FROM clause is to designate a list of source tables (data sources) used in the SELECT operator.

The FROM clause is optional in the query language. It can be omitted if data sources are fully qualified in the description of the selection fields list specified in the SELECT clause. Please note that the set of examples in the previous sections did not contain the FROM clause.

The FROM keyword is followed by the source list. Generally, the source list is described by the following set of rules: <Source list>

<Source>[, <Source>[, …]]

<Source>

<Source description> [ <Joining list> ]

<Source description>

<Table> [[AS] <Source alias>]

<Table>

<Table name>[(<Parameters>)] | ( <Query description> )

Query data sources are listed in the list of sources (comma-separated). Each source in the list must include a description; additionally you can indicate a joining list – rules for joining a source with other sources. For a description of join specification see page 1-454.

If you use an infobase table as a data source, the source description contains a table name.

If the source table is virtual, then you can indicate its formation parameters. For a detailed description of virtual table parameters, see Script – Working with Queries – Query tables in the built-in help.

You can also use a nested query as a query data source. In this case the source description contains a description of the query. For a description of nested queries see page 1-460.

The data source description can also contain a source alias. The alias can be used to access this source more conveniently.

8.2.8.1. Join Specifications

When defining several sources in the source list, there is a selection from the second source table for each record from the first source table and so on. This way, all possible combinations of all records from all specified sources are included in the query result.

Example:

SELECT
Contractors.Ref as Contractor,
PriceTypes.Ref AS PriceType
FROM
Catalog.Contractors AS Contractors,
Catalog.PriceTypes AS PriceTypesResult

Result:

Fig. 185. Query Result (Fragment)

The query result contains combinations of all contractors with all price types. This result does not have meaning on its own. Usually, combinations of records from different source tables must be limited by certain conditions. In the query language you can describe a joining of sources, by indicating the sources themselves and defining conditions by which record combinations are included in the query result.

There are different ways of joining; these are described by the following rules: <Joining list>

<Join> [<Joining list>]

<Join>

[INNER]  JOIN <Source description> BY <Filter criterion> |
LEFT  [OUTER] JOIN <Source description> BY <Filter criterion> |
RIGHT  [OUTER] JOIN <Source description> BY <Filter criterion> |
FULL  [OUTER] JOIN <Source description> BY <Filter criterion>

Generally, a joining list can contain and describe a single join (two sources) or multiple joins of multiple sources.

Source description contains a description of the source table (see page 1-453).

Filter criterion contains conditions used to join data from the query source tables in the selection. For information about rules of condition description in the query language see page 1-488.

The LEFT, RIGHT and FULL keywords specify the nature of the join. INNER and OUTER are optional; they improve the clarity and readability of the query text.

Joined sources are not equal and in some cases the result depends on which table is indicated first, before the JOIN keyword (to the left) and which is second (to the right of the keyword).

Two simple tables can be used to describe the joins. One of the tables is named Companies, consists of two fields (Name and Telephone Number) and contains the following data:

Fig. 186. Companies Table

The other table is named Contacts, consists of three fields (Name, Telephone Number and Company), the latter being a reference to a Companies table item. The table contains the following data:

Fig. 187. Contacts Table

NOTE

In 1C:Enterprise terms, both tables are catalogs.

Inner Join

An inner join means that only record combinations meeting the specified condition from both initial data source tables must be included in the query result.

The remaining records are not included in the result.

Example:

SELECT
Companies.Name AS Company,
Contacts.Name AS Contact
FROM
Catalog.Contacts AS Contacts
INNER JOIN Catalog.Companies AS Companies
BY Contacts.Company = Companies.Ref

Result:

Fig. 188. Query Result

Left Outer Join

A left outer join means that record combinations from both source tables meeting the specified criterion must be included in the query result. However, as opposed to inner joining, records from the first source (indicated on the left of the JOIN keyword) without corresponding records from the second source must also be included in the query result.

This way, all records from the first source will be included in the query result; these will be joined with the records from the second source when executing the specified condition. Query result strings without the corresponding record from the second source will contain NULL in the fields based on the records from this source.

Example:

SELECT
Contacts.Name AS Contact,
Companies.Name AS Company
FROM
Catalog.Contacts AS Contacts
LEFT JOIN Catalog.Companies AS Companies
BY Contacts.Company = Companies.Ref

Result:

Fig. 189. Query Result

Right Outer Join

A right outer join means that record combinations from both source tables meeting the specified criterion must be included in the query result. Additionally records from the second source (indicated on the right of the JOIN keyword) without the corresponding records from the first source must be included in the query result.

Therefore, all records from the second source will be included in the query result; these will be joined with the records from the first source which meet the specified condition. Query result strings without the corresponding records from the first source will contain NULL in the fields based on the records from this source.

NOTE

In practice, the right outer join can be cast to the left outer join. This is exactly what the Designer's query wizard does.

Example:

SELECT
Contacts.Name AS Contact,
Companies.Name AS Company
FROM
Catalog.Contacts AS Contacts
RIGHT JOIN Catalog.Companies AS Companies
BY Contacts.TelephoneNumber = Companies.TelephoneNumber

Result:

Fig. 190. Query Result

Full Outer Join

A full outer join means that record combinations from both source tables meeting the specified criterion must be included in the query result. Additionally records without matches from both sources must also be included in the query result.

Therefore, all records from both sources will be included in the query result; these will be joined with each other if the specified condition is met. Query result strings without the corresponding records from any source will contain NULL in the fields based on the records from this source.

Example:

SELECT
Contacts.Name AS Contact,
Companies.Name AS Company
FROM
Catalog.Companies AS Companies
FULL JOIN Catalog.Contacts AS Contacts
BY (Contacts.TelephoneNumber = Companies.TelephoneNumber)

Result:

Fig. 191. Query Result

8.2.8.2. Data Source Aliases

If you assign an alias to a data source, you will be able to access this source using the alias in the future (and you will not be able to access it by the table name).

This can be more convenient and in some cases it is the only option.

Aliases are defined according to rules of variable identifier assignment. Aliases in a query cannot be the same.

The AS keyword can precede the source alias. You can omit this word, but when indicated it improves the clarity and readability of the query text.

Assigning source aliases in itself does not affect data selection in the query.

Example:

SELECT
Product.Name,
Product.Parent
FROM
Catalog.Nomenclature AS Product

This example demonstrates the use of the Product alias assigned to the Catalog. Nomenclature source table in the selection fields list.

8.2.8.3. Nested Tables in the Source List

The source list can also include nested tables, i.e. tabular sections of catalogs and documents.

Example:

//  Consignment note specifications (the document, assortment and quantity)
//  must be added to the report.
//  The source list includes the Content nested table –
//  a tabular section of a consignment Note:
//  The selection is limited to eight records to prevent overloading the example.

SELECT  TOP 8
InvoiceContent.Ref,
InvoiceContent.Nomenclature,
InvoiceContent.Count
FROM
Document.Invoice.Content AS InvoiceContent

Result:

Fig. 192. Query Result

Please note that when you indicate a nested table in the source list, you can access both fields from the nested table and fields from the top-level table (containing the nested table) through the Ref field. In this case you access the Ref.FieldDescription field of the document itself.

8.2.8.4. Nested Queries in the Source List

You can use a nested query as a source table in the source list. In this case the source description contains a description of the nested query. A description of a nested query is composed just like any other description (see page 1-446).

Using a nested query as a data source does not differ from using an infobase table. All fields described in the selection fields list of the nested query are accessible as fields of such a source.

Example:

SELECT
NestedSelect.Ref,
NestedSelect.Nomenclature,
NestedSelect.Count
FROM
(SELECT TOP 8
InvoiceContent.Ref AS Ref,
InvoiceContent.Nomenclature AS Nomenclature,
InvoiceContent.Count AS Count
FROM
Document.Invoice.Content  AS InvoiceContent) AS NestedSelect

The result will be exactly like in the previous example.

8.2.9. Query Result Filtration

The WHERE <Filter criterion> clause allows you to specify filter criteria for data from the source tables (query sources); the query will only process the records if they meet the specified condition.

Example:

//  You want to know which contractors are persons.

SELECT
Contractors.Description
FROM
Catalog.Contractors AS Contractors
WHERE  Contractors.Type = VALUE(Enum.ContractorTypes.Person)

Result:

Fig. 193. Query Result

NOTE

The field in the clause WHERE does not have to be entered into the selection list.

You can define a filter criterion as a simple logical expression or a more complex expression, where simple logical expressions are connected to each other by the logical operators AND or, NOT. For a description of criteria in the query language see page 1-488.

8.2.10. Query Result Groups

Source data in the query can be grouped using aggregate functions specified as fields in the selection list. This means that strings in the query result will contain calculation results of the indicated aggregate functions, calculated (grouped) by source table record.

Aggregate functions themselves are specified in the selection fields list. You must indicate the fields to be used for grouping in the GROUP BY <Grouping fields> clause. Source table records containing similar values for the specified fields, will be grouped in the query.

The grouping fields list contains references to comma-separated source table fields (query sources):

<Grouping fields>

<Dereferencing fields> [, <Dereferencing fields> [, …]]

IMPORTANT!

When grouping the query results, you must specify the aggregate functions in the selection fields list; additionally you can only indicate the fields used for grouping.

The only exception is when aggregate functions are applied to nested table fields. In this case you can access top-level table fields in the selection fields list, without grouping the results by these fields.

When using aggregate functions, you do not have to specify the GROUP BY clause; in this case all query results will be grouped into a single string.

Example:

//  You want to obtain statistics on the product sales:
//  maximum, minimum and average prices in invoices.

Select
Invoice.Nomenclature,
Avg (Invoice.Price) As Avg,
Max (Invoice.Price) As Max,
Min (Invoice.Price) As Min,

From
Document.Invoice.Content AS Invoice

Group  By
Invoice.Nomenclature

Result:

Fig. 194. Query Result (Fragment)

8.2.11. Conditions for Aggregate Function Values

The HAVING <Filter criterion> clause allows you to apply conditions to aggregate function values. You cannot indicate aggregate function conditions in other query language constructs, e.g., in the WHERE clause.

Example:

//  You want to select the goods that have had more than 20 pieces sold.

SELECT
Invoice.Nomenclature,
SUM(Invoice.Count) AS Count
FROM
Document.Invoice.Content AS Invoice

GROUP  BY
Invoice.Nomenclature

HAVING
SUM(Invoice.Count) > 20

Result:

Fig. 195. Query Result

IMPORTANT!

You can only use aggregate functions and fields used for grouping in filter criteria.

8.2.12. Query Union

In the query language, you can merge several queries; in this case records received from each of the merged queries will be consolidated into a single query result.

Each query in a union gathers data independently, but operations such as ordering results and calculating totals take place within the results of merged queries.

Query result fields will be named as described in the selection fields list of the first query. Selection fields from the remaining queries are mapped to the result fields according to their order in the selection fields list. Merged queries must have a similar count of fields in the selection fields list.

If merged query selection fields have a different type, then query result fields will have a composite type.

Query union is described by the following rule:

<Query union>

UNION  [ALL]
<Query  description>
[<Query  union>]

Query union begins with the required UNION keyword, followed by a description of the query to be merged with. Then you can join another query, etc.

By default, identical strings in the query result generated by different queries are replaced by one string. If you need to leave duplicate strings, use the ALL keyword.

Example:

SELECT
Receipt.Nomenclature AS Product,
SUM(Receipt.Count) AS Receipt,
SUM(0) AS GoodsConsumption
FROM
Document.ReceiptOfGoods.Content AS Receipt

GROUP  BY
Receipt.Nomenclature

UNION

SELECT
GoodsConsumption.Nomenclature,
SUM(0),
SUM(GoodsConsumption.Count)
FROM
Document.Invoice.Content AS GoodsConsumption

GROUP  BY
GoodsConsumption.Nomenclature

Result:

Fig. 196. Query Result (Fragment)

8.2.13. Query Result Ordering

The ORDER BY clause allows you to sort lines in the query result.

<Results ordering>

ORDER BY <Order conditions>

<Order conditions>

<Order  field> [<Order>]
[,  <Order field> [<Order>][, …]]

<Order field>

<Expression> <Order> ASC | DESC | HIERARCHY | HIERARCHY DESC

In the ORDER BY clause, conditions for ordering query results are separated by comma. Selections are first ordered by the first condition, then by the second, etc.

The order condition generally can appear as an expression (see page 1-470). Lines of the query result will be ordered by the expression values calculated for each line.

You can order values in ascending or descending order or, if the hierarchical structure property is set for a table, you can use ordering by hierarchy (see page 1-465). You can indicate order for each field independently. For a description of value comparison rules see page 1-490.

Ordering condition fields do not have to be included in the query result.

Example:

//  Select five most expensive goods,
//  ordering them in descending order by their price.

SELECT  TOP 5
Nomenclature.Description,
Nomenclature.PurchasePurchasePrice
FROM
Catalog.Nomenclature AS Nomenclature

ORDER  BY
Nomenclature.PurchasePurchasePrice DESC

Result:

Fig. 197. Query Result

8.2.13.1. Ordering by Hierarchy

You can order catalogs by catalog hierarchy.

Example:

SELECT
Catalog.Nomenclature.Description,
Catalog.Nomenclature.FullDescr
ORDER  BY
Catalog.Nomenclature.Description Hierarchy

Result:

Fig. 198. Query Result (Fragment)

You can only sort hierarchically by field, but not by operation: the order field must contain a reference to the source table field (query data source).

IMPORTANT!

It makes sense to order by hierarchy only if the catalog table is defined as the source, rather than some other table containing a reference to the catalog only.

Example:

SELECT
Invoice.Contractor.Description,
Invoice.Number,
Invoice.Warehouse,
FROM
Document.Invoice AS Invoice

In this example, hierarchical ordering is not possible, since there is no a link to the catalog and folders from this catalog are not included in the query result.

To apply ordering by hierarchy, you must link to the catalog.

Example:

SELECT
Contractors.Description AS Description,
Invoice.Number,
Invoice.Warehouse
FROM
Catalog.Contractors AS Contractors
LEFT  JOIN Document.Invoice AS Invoice
BY  Contractors.Ref = Invoice.Contractor
ORDER  BY
Description HIERARCHY

Result:

Fig. 199. Query Result (Fragment)

8.2.13.2. Ordering within Nested Tables

In the ORDER BY clause, you can define ordering conditions for records from nested tables; moreover, they can be combined with ordering conditions from the top-level table.

The order of table fields from one level (nested or top level) relative to each other is important, but the order of table fields from one level relative to another level is not important: ordering always takes place first by top level table, then by nested table.

Example:

//  Consignment note specifications (the document, assortment and // quantity)  must be added to the report.
//  The documents must be ordered by number,
//  while its content must be ordered by goods names.

Select
Document.Invoice.Ref,
Dcument.Invoice.Content.(Nomenclature As Product, Count)
Order  By
Document.Invoice.Number,
Document.Invoice.Content.Nomenclature.Description

8.2.14. Autoorder Results

The AUTOORDER clause allows you to enable automatic field generation for ordering query results.

Autoorder functions according to the following principles:

If the ORDER BY clause is indicated in the query, then each reference to a table located in this clause will be replaced by fields used to sort the table by default (for catalogs it is a code or description, for documents it is a document date). If the field for ordering refers to a hierarchical catalog, then hierarchical sorting by this catalog will take place.

If the ORDER BY clause is not included in the query, but the TOTALS clause is, then the query result will be ordered by fields included in the TOTALS clause and following the BY keyword, in the same sequence; if totals are calculated by reference fields ordering by default sorting fields of the referenced tables is applied.

If ORDER BY and TOTALS are not included in the query, but the GROUP BY clause is, then the query result will be ordered by fields included in the clause, in the same sequence; if grouping by reference fields is applied, the result will be ordered by default sorting fields of the referenced tables.

If none of the aforementioned clauses (ORDER BY, TOTALS and GROUP BY) is included in the query, then the result is ordered by default sorting fields for the tables from which data are selected, in the order of their appearance in the query.

8.2.15. Calculation of Query Totals

The TOTALS clause allows you to define which totals must be calculated in the query. In calculating totals, aggregate functions are calculated by selection with identical field values (check points). Totals are added to the query result as totals rows.

The totals calculation procedure is described by the following rules:

<Totals description>

<Totals> [<Totals description>]

<Totals>

TOTALS [<List_of_totals_fields>] BY [OVERALL] <List of check points>

<List of totals_fields>

<Totals_field> [,<List_of_totals_fields> [, …]]

<Totals_field>

<Aggregate_function> | <Expression> [[AS] <Field_alias>]

<List of check points>

<Check point> [, <Check point> [, …]]

<Check point>

<Expression>  [[ONLY] HIERARCHY] | [PERIODS(Second | Minute | Hour | Day | Week | Month |
Quarter  | Year | TenDays| HalfYear
[,<DATE  type literal> | <Parameter ID>]
[,<DATE  type literal> | <Parameter ID>])] [[AS] Field alias]

The totals description begins with the required TOTALS keyword.

A list of aggregate functions contains a list of aggregate functions (see page 1-474) which must be calculated in the totals.

The OVERALL keyword means that an overall totals row must be generated for the entire query result. For a description of overalls calculation see page 1-471.

In addition to overalls, you can also calculate totals by check points. To do this, indicate <List of check points> after the required BY keyword. Each check point contains an expression calculated when executing the query. Totals rows will be calculated and added to the query result for each combination of expression values.

If a check point is a reference to a catalog, you can calculate totals by catalog hierarchy (see page 1-469). To do this, use the HIERARCHY keyword after the reference.

8.2.15.1. Totals Calculation in Nested Tables

The current program version does not support totals calculation by nested tables.

8.2.15.2. Totals by Hierarchy

You can calculate totals by hierarchy. To do so, you must indicate the HIERARCHY keyword after the name of the field to be calculated. Totals by check points and totals by hierarchy for check points will be calculated in the result.

Example:

SELECT
Document.Nomenclature AS Nomenclature,
Document.Count AS Count,
Document.Ref.Number,
Document.Ref.Contractor
FROM
Document.Invoice.Content AS Document
ORDER  BY
Document.Nomenclature
TOTALS
SUM(Count)
BY
Nomenclature HIERARCHY

Result:

Fig. 200. Query Result (Fragment)

If necessary, you can calculate value totals by hierarchy only, without calculating totals in the check points. To do so, use the ONLY keyword before the HIERARCHY keyword.

Example:

SELECT
Document.Nomenclature AS Nomenclature,
Document.Count AS Count,
Document.Ref.Number,
Document.Ref.Contractor
FROM
Document.Invoice.Content AS Document
ORDER  BY
Document.Nomenclature
TOTALS
SUM(Count)
BY
Nomenclature ONLY HIERARCHY

Result:

Fig. 201. Query Result (Fragment)

8.2.15.3. Date Addition

If a Date type field is used to calculate totals, then you can supplement results with dates in a specified period. To do this, use the PERIODS keyword, followed by the period type in parentheses (Second, Minute, Hour, Day, Week, Month, Quarter, Year, TenDays, HalfYear) and the beginning and ending dates of the corresponding period. If the beginning and ending dates are not specified, then the first and the last dates in the result will be used.

Example:

//  Obtain a number of purchases for individual customers by time of
//  the selected day
SELECT
ReceiptOfGoods.Contractor,
BEGINOFPERIOD(ReceiptOfGoods.Date, HOUR) AS Period,
COUNT(ReceiptOfGoods.Ref) AS PurchaseCount
FROM
Document.ReceiptOfGoods AS ReceiptOfGoods
GROUP  BY
ReceiptOfGoods.Contractor,
BEGINOFPERIOD(ReceiptOfGoods.Date, HOUR)
TOTALS
SUM(PurchaseCount)
BY
Period  PERIODS(MINUTE, DATETIME(2006,6,28), DATETIME(2006,6,28))

Result:

Fig. 202. Query Result

This kind of presentation is possible only if all period records are used as the dimension source in the course of result tabbing by the Period group.

8.2.15.4. Calculation of Overalls

To calculate totals for the entire table, use the OVERALL keyword in the TOTALS clause. In this case aggregate function values are calculated for all table records.

Example:

SELECT
Document.Nomenclature,
Document.Count AS Count,
Document.Ref.Number,
Document.Ref.Contractor
FROM
Document.Invoice.Content AS Document
TOTALS
SUM(Count)
BY
OVERALL

Result:

Fig. 203. Query Result (Fragment)

8.2.15.5. Using Totals and Groups Together

If totals are used with groups and a list of aggregate functions is not specified for totals, then it will be auto-generated from aggregate fields of the selection list. If the query contains a union, then aggregate functions from the first query are used.

Example:

SELECT
Document.Nomenclature AS Nomenclature,
Document.Ref.Contractor AS Contractor,
SUM(Document.Count) AS Count
FROM
Document.Invoice.Content AS Document
GROUP  BY
Document.Nomenclature,
Document.Ref.Contractor
TOTALS  BY
Nomenclature,
Contractor

Result:

Fig. 204. Query Result (Fragment)

8.2.15.6. Totals Aliases

You can assign aliases to totals fields (check points), for which totals are read, to access them later using the 1C:Enterprise script. To accomplish this, you must specify the name of the alias after the check point expression, as it is done in the selection fields list.

Example:

SELECT
Document.Nomenclature AS Nomenclature,
Document.Count AS Count,
Document.Ref.Number,
Document.Ref.Contractor
FROM
Document.Invoice.Content AS Document
ORDER  BY
Document.Nomenclature
TOTALS
SUM(Count)
BY
Nomenclature ONLY HIERARCHY AS Goods

If an alias is not specified, the system will provide a unique name for the total. In the above example, the total will have the Goods name.

8.2.16. Expressions in the Query Language

Expressions can be used in various query language constructs. Query language expressions are described as follows:

<Expression>

<Dereferencing  field> |
<Aggregate  function> |
<Built-in  function> |
<Case  operation> |
<Type  cast>[.<Dereferencing field>] |
<Value>  |
<Expression>  <Binary operation> <Expression> |
<Unary  operation> <Expression> |
(  <Expression> )

<Binary operation>

+ | – | * | /

<Unary operation>

– | +

In the simplest case, an expression is a reference to a field of the query data source table. The reference can include specification of the table containing this field or it can be defined without specifying the table. For information about dereferencing fields see page 1-474.

Expressions in the selection field list and in HAVING, TOTALS or ORDER BY clauses can be represented by aggregate functions (see page 1-474).

An expression can be a built-in function of the query language (see page 1-477). You can also use case operations (see page 1-485) and value type cast operations (see page 1-486).

Expressions can include values of logical, numeric, string and other constants; query parameter values can also be used (see page 1-487). Binary and unary operations can be applied to values of appropriate types within expressions. Checking the divider for zero value is recommended when the division operation is used.

8.2.16.1. Dereferencing Fields

In the simplest case, query language expressions appear as references to fields of infobase tables. References are generally described by the following rules: <Dereferencing field>

[<Table>.]<Field name>[.<Field name>[…]]

<Table>

Dereferencing of fields begins with the name of the table containing this field. If the field name is unique (it exists in only one of the tables indicated in the source list), the table can be omitted.

If the field has a reference type, the query language allows you to access fields in the referenced table, etc. Field names are period-separated.

If a source alias is assigned to the source table in the source list, you can use it instead of the table name in dereferencing fields of this table. Otherwise the table name is specified (see query data source description).

8.2.16.2. Aggregate Functions of Query Language

The query language provides aggregate functions used when grouping query results and calculating totals. You can use aggregate functions to summarize values of a given parameter. The following aggregate functions are defined:

<Aggregate function>

SUM  ( <Expression> ) |
AVG  ( <Expression> ) |
MIN  ( <Expression> ) |
MAX  ( <Expression> ) |
COUNT  ( [DISTINCT] <Expression> | * )

Example:

SELECT
Invoice.Nomenclature.Description,
SUM(Invoice.Sum) AS Sum
AVG (Invoice.Sum) AS Avg,
MAX(Invoice.Sum) AS Max,
MIN(Invoice.Sum) AS Min,
COUNT(Invoice.Sum) AS Count
FROM
Document.Invoice.Content AS Invoice

GROUP  BY
Invoice.Nomenclature,
Invoice.Nomenclature.Description
TOTALS  BY
OVERALL

Result:

Fig. 205. Query Result (Fragment)

You can use aggregate functions in the selection fields list and in the HAVING, TOTALS or ORDER BY clauses.

SUM

Description:

This function calculates the arithmetic sum of all entries in the field value selection.

You can only indicate fields containing a numerical value as a function parameter.

If the field cannot contain a numerical value, then using the SUM function will produce an error. You can apply this function to the field, only if the field can contain numerical values (has a composite data type). But if there is a non-numerical value among the field values (besides NULL), then an error will occur.

AVG

Description:

This function calculates the average value of all entries in the field value selection.

You can only use references to fields containing numerical values as a function parameter.

If the field cannot contain a numerical value, then using the AVG function will produce an error. You can apply this function to the field, only if the field can contain numerical values (has a composite data type). But if there is a non-numerical value among the field values (besides NULL), then an error will occur.

MIN

Description:

This function calculates the minimum value of all entries in the field value selection.

You can use references to fields that contain values of every type as a function parameter.

In defining the minimum value, value comparison rules are applied, as described in the "Value Comparison Rules" section.

MAX

Description:

This function calculates the maximum value of all entries in the field value selection.

You can use expressions that contain values of every type as a function parameter. It can not be used either with expressions of ValueStorage or with MomentInTime type, or with strings of unlimited length.

In defining the maximum value, value comparison rules are applied, as described in the "Value Comparison Rules" section.

COUNT

Description:

This function calculates the count of parameter values in the selection. As opposed to other aggregate functions, you can use COUNT in three ways:

This function allows you to calculate the count of values that are not equal to NULL in a given field.

This function allows you to calculate the count of various values that are not equal to NULL in a given field; To do so, you must use the DISTINCT keyword before the field specification.

This function can calculate the count of rows in the query result. To do so, use an asterisk ("*") as a function parameter.

You can use references to fields that contain values of every type as a function parameter.

Example:

SELECT
COUNT(*) AS Total,
COUNT(DISTINCT Invoice.Nomenclature) AS Different
FROM
Document.Invoice.Content AS Invoice

Result:

Fig. 206. Query Result

8.2.16.3. Built-in Functions of Query Language

Built-in functions defined in the query language can be used in expressions of the selection field list (see page 1-450) and in the filter criteria of the WHERE clause (see page 1-460).

The following built-in functions are defined:

<Built-in function>

SUBSTRING  ( <Expression>, <Value>, <Value> ) |
YEAR  ( <Expression> ) |
QUARTER  ( <Expression> ) |
MONTH  ( <Expression> ) |
DAYOFYEAR  ( <Expression> ) |
DAY  ( <Expression> ) |
WEEK  ( <Expression> ) |
WEEKDAY  ( <Expression> ) |
HOUR  ( <Expression> ) |
MINUTE  ( <Expression> ) |
SECOND  ( <Expression> ) |
BEGINOFPERIOD(<Expression>,  Minute | Hour | Day | Week |
Month | Quarter | Year | TenDays | HalfYear) |
ENDOFPERIOD(<Expression>,  Minute | Hour | Day | Week |
Month | Quarter | Year | TenDays | HalfYear) |
DATEADD(<Expression>,  Minute | Hour | Day | Week |
Month | Quarter | Year | TenDays | HalfYear, <Expression>) |
DATEDIFF(<Expression>,  <Expression>, Second | Minute | Hour |
Day | Month | Quarter | Year) |
VALUETYPE(<Expression>)  |
PRESENTATION(<Expression>)  |
ISNULL(<Expression>,  <Expression>)

SUBSTRING

Description:

This function is used to extract a substring from a string. This function has three parameters.

A string that requires obtaining a substring. It's an expression of the String type;

The position of the character where the substring is to begin. It's a value of the Number type;

Length of the obtained substring. It is a value of Number type.

If the first parameter specifies a string, the result of the function is a string (possibly of zero length). If the first parameter specifies a NULL value, the result of the function is NULL. Other values are invalid and produce an error.

Example:

SELECT  TOP 8
Contractors.Description,
SUBSTRING(Contractors.Description, 3, 5) AS Substring
FROM
Catalog.Contractors AS Contractors

Result:

Fig. 207. Query Result

YEAR

Description:

This function calculates the year number from a value of the Date type.

The function's parameter is an expression of the Date type.

If the parameter contains a value of the Date type, the result of the function is a value of the Number type. If the parameter specifies a NULL value, the result of the function is NULL. Other values are invalid and produce an error.

QUARTER

Description:

This function calculates the quarter number from a value of the Date type. The number of the quarter is between 1 and 4.

The function's parameter is an expression of the Date type.

MONTH

Description:

This function calculates the month number from a value of the Date type.

The number of the month is between 1 and 12.

The function's parameter is an expression of the Date type.

DAYOFYEAR

Description:

This function calculates the day number within a year from a value of the Date type. The number of the day is between 1 and 366. The function's parameter is an expression of the Date type.

DAY

Description:

This function calculates the day number within a month from a value of the Date type. The number of the day is between 1 and 31.

The function's parameter is an expression of the Date type.

WEEK

Description:

This function calculates the week number within a year from a value of the Date type.

The function's parameter is an expression of the Date type.

WEEKDAY

Description:

This function calculates the day number within a week from a value of the Date type. The number of the day is between 1 (Monday) and 7 (Sunday).

The function's parameter is an expression of the Date type.

HOUR

Description:

This function calculates the hour of a day from a value of the Date type. An hour value is between 0 and 23.

The function's parameter is an expression of the Date type.

MINUTE

Description:

This function calculates the minute of an hour from a value of the Date type. A minute value is between 0 and 59.

The function's parameter is an expression of the Date type.

SECOND

Description:

This function calculates the second of a minute from a value of the Date type. A second value is between 0 and 59.

The function's parameter is an expression of the Date type.

BEGINOFPERIOD

Description:

This function is used to extract a certain date from a given date.

The function's parameter is an expression of the Date type and the period type is one of the following: Minute, Hour, Day, Week, Month, Quarter, Year, TenDays, HalfYear.

Example:

SELECT BEGINOFPERIOD(DATETIME(2002, 10, 12, 10, 15, 34), MONTH)

Result:

Fig. 208. Query Result

Example:

SELECT BEGINOFPERIOD(DATETIME(2002, 10, 12, 10, 15, 34), DAY)

Result:

Fig. 209. Query Result

ENDOFPERIOD

Description:

This function is used to extract a certain date from a given date.

The function's parameter is an expression of the Date type and the period type is one of the following: Minute, Hour, Day, Week, Month, Quarter, Year, TenDays, HalfYear.

Example:

SELECT ENDOFPERIOD(DATETIME(2002, 10, 12, 10, 15, 34), MONTH)

Result:

Fig. 210. Query Result

Example:

SELECT ENDOFPERIOD(DATETIME(2002, 10, 12, 10, 15, 34), YEAR)

Result:

Fig. 211. Query Result

DATEADD

Description:

This function is used to add values to a date.

The first parameter is a source date which is to be supplemented by the value defined by the second and third parameters; it is an expression of the Date type.

The second parameter, the increment type, is one of the following: Second, Minute, Hour, Day, Week, Month, Quarter, Year, TenDays, HalfYear.

The third parameter is the value which is to be added to the date specified by the first parameter. It is a value of the Number type (fractional part is ignored).

Example:

SELECT DATEADD(DATETIME(2002, 10, 12, 10, 15, 34), MONTH, 1)

Result:

Fig. 212. Query Result

Example:

SELECT DateAdd(DateTime(2002, 10, 12, 10, 15, 34), Day, 5)

Result:

Fig. 213. Query Result

DATEDIFF

Description:

This function is used to obtain differences between two dates.

The first parameter is an expression of the Date type. The second parameter is an expression of the Date type. The third parameter, the difference type, is one of the following: Second, Minute, Hour, Day, Month, Quarter, Year.

Example:

SELECT DATEDIFF(DATETIME(2002, 10, 12, 10, 15, 34), DATETIME(2002, 10, 14, 9, 18, 06), DAY)

Result:

Fig. 214. Query Result

Example:

SELECT DATEDIFF(DateTime(2002, 10, 12), DateTime(2002, 11, 03), MONTH)

Result:

Fig. 215. Query Result

IMPORTANT!

This function calculates the calendar difference between two dates; therefore, you cannot use it in places where you must calculate the count of bank or work days between two dates.

VALUETYPE

Description:

The function that obtains the value type in a query.

Parameters:

The function's parameter is an expression of any type.

The return value is a Type value.

Example:

SELECT
VALUETYPE(NomenclatureAccounting.Recorder) AS Document
FROM
AccumulationRegister.NomenclatureAccounting AS  NomenclatureAccounting

PRESENTATION

Description:

This function returns the string presentation of a value of arbitrary type.

The function's parameter is an expression of any type.

The return value is a String value.

The result of the function cannot be used within other functions, except for the PRESENTATION function.

Example:

SELECT
PRESENTATION(Document.Contractor) AS Recipient,
PRESENTATION(Document.Date) AS Date
FROM
Document.Invoice AS Document

Result:

Fig. 216. Query Result

ISNULL

Description:

This function replaces a NULL value with another value.

Function parameters:

The first parameter is an expression of any type;

The second parameter is an expression of any type.

The return value is that of the first parameter if it is not NULL; otherwise it is the value of the second parameter.

The second parameter is cast to the type of the first parameter if the type of the first parameter is String or Number.

Example:

//  Obtain sum based on Count field. If no record
//  exists, display 0
SELECT
ISNULL(SUM(InvoiceContent.Count), 0)
FROM
Document.Invoice.Content AS InvoiceContent

8.2.16.4. Case Operations in Query Language

Query language expressions can use case operations which allow you to obtain one of the possible values meeting the specified conditions. Case operations are described by the following set of rules:

<Case operation>

CASE
<Case  alternatives>
[ELSE  <Expression>]
END

<Case alternatives>

<Single  case>
[<Case  alternatives>]

<Single case>

WHEN  <Logical expression>
THEN  <Expression>

In case operations, you can specify an unlimited number of alternative single cases WHEN … THEN. These are processed in the query in a sequence. If the logical expression is True, then the case operation processing is completed. The operation result is a value of the expression specified after the THEN keyword. For a description of logical expressions see page 1-489.

The value of the expression specified after the ELSE keyword is used as the case operation result if the predicate was False in all the previously indicated alternative single cases.

Example:

SELECT
Nomenclature.Description,
CASE
WHEN Nomenclature.IsFolder = TRUE
THEN "This is a folder"
WHEN Nomenclature.PurchasePrice > 1000
THEN "1000 -"
WHEN Nomenclature.PurchasePrice > 100
THEN "100 – 1000"
WHEN Nomenclature.PurchasePrice > 10
THEN "10 – 100"
WHEN Nomenclature.PurchasePrice > 0
THEN "0 – 10"
ELSE "Not specified"
END AS Price
FROM
Catalog.Nomenclature AS Nomenclature

Result:

Fig. 217. Query Result (Fragment)

8.2.16.5. Type Cast in Query Language

Fields of source tables can have a composite type. These fields have to cast their values to a particular type. The query language provides you with a type cast feature. You can use it in the selection field list and the filter criterion of the WHERE clause.

<Type cast>

CAST ( <Expression> AS <Value type> )

<Value type>

Boolean  |
Number  [(Length[, Precision])]|
String  [(Length)]|
Date  |
<Table  name>

<Length>

Number

<Precision>

Number

The expression is cast to one of the primitive types or a data reference type. In the latter case the table name refers to the corresponding infobase table.

If the expression contains the required value type in its composite type, then type casting is possible and the result for each value of the specified type will be the value itself. For values of other types, the result of type cast will be NULL.

If the expression does not contain the required value type in its composite type, the query execution will be completed with an error because type casting cannot be performed.

8.2.16.6. Constants and Parameters in Query Language

Query language expressions can contain directly specified values of Boolean, Number, String or Date type. Query parameter values can also be used:

<Value>

TRUE  |
FALSE  |
<NUMBER  type literal> |
<STRING  type literal> |
<DATE  type literal> |
<TYPE  type literal> |
<Parameter  name> |
UNDEFINED  |
NULL

<Number type literal>

<Integer>[.<Integer>]

<String type literal>

"<Character sequence>"

<Date type literal>

DATETIME  ( <Integer>, <Integer>, <Integer>[,
<Integer>, <Integer>,  <Integer>] )

Values of Boolean, Number and String types are specified in the same way as in the 1C:Enterprise script.

Date type values are specified using the DATETIME keyword followed by the sequence of year, month, day, hour, minute and second. The last three components are optional.

IMPORTANT!

The maximum date you can specify using the DATETIME literal is 12/31/3999 23:59:59.

You can pass multiple parameters to a query (see a description of the Query object). Parameter values can be used in the query language expressions. In this case you need to specify the "&" character followed by the parameter name. <Type type literal>

TYPE(<Type name>)

<Type name> is a name of a primitive type or a name of a table, the type of reference to which must be obtained. The result of this expression will be a Type value for the specified type.

Example:

//  Retrieval of the String type
TYPE(String)

//  Retrieval of a type referencing the Nomenclature catalog
TYPE(Catalog.Nomenclature)

Type values can be used in comparison ordering and grouping operations of the query language.

Example:

SELECT
VALUETYPE(Balance.Recorder)
FROM
AccumulationRegister.NomenclatureAccounting AS Balance
WHERE
VALUETYPE(Balance.Recorder) = TYPE(Document.Invoice)

A Type value can be passed as a query parameter.

Example:

SELECT
VALUETYPE(Balance.Recorder)
FROM
AccumulationRegister.NomenclatureAccounting AS Balance
WHERE
VALUETYPE(Balance.Recorder) = &Type

When Type values are compared, they have the following order (the type listed first is the smallest):

NULL

Undefined

Boolean

Number

Date

String

References to a table

Other types

8.2.17. Conditions in Query Language

The query language uses filter criteria to filter data in the WHERE, HAVING and JOIN clauses. The following rules describe these criteria: <Filter criterion>

<Logical component> [OR <Logical component>]

<Logical component>

<Logical factor> [AND <Logical factor>]

<Logical factor>

NOT  <Logical factor> |
(  <Filter criterion> ) |
<Logical  expression>

In the simplest case, a condition is an expression with a result having a logical type value. Logical expression are described in the next section.

Conditions can also be defined as more complex expressions, where simple logical expressions are joined using the logical operators AND or, NOT.

Logical operators have the following priorities:

NOT has the highest priority.

AND is next in priority.

OR has the lowest priority.

Simple logical expressions are calculated first, then NOT, then AND and finally OR. In order to set another calculation order, you can use parentheses ().

8.2.17.1. Logical Expressions in Query Language

You can use the following logical expressions in case operations and filter criteria:

<Logical expression>

<Expression>  |
<Expression>  <Comparison operation> <Expression> |
<Expression>  [NOT] IN [HIERARCHY] ( <Value list> ) |
<Expression>  [NOT] IN [HIERARCHY] ( <Query description> ) |
<Expression>  [NOT] BETWEEN <Expression> AND <Expression> |
<Expression>  IS [NOT] NULL |
<Expression>  REFS <Table name> |
<Expression>  [NOT] LIKE <STRING type literal>
[ESCAPE <STRING type literal>]

<Comparison operation>

> | < | = | >= | <= | <>

<Value list>

<Expression>[, <Expression> [, …]]

A logical expression can be:

A normal query language expression if its result has a logical type;

A comparison operation for two query language expressions carried out according to the value comparison rules described in the "Value Comparison Rules" section;

An operator for checking equality or inequality of the expression value with one of the listed values or with values from the results of another query;

An operator for checking whether the expression value falls into a certain range;

An operator for checking the expression value against NULL;

An operator for checking reference expression values for references to a certain table;

An operator for checking string values for matching a template.

Value comparison rules used to compare values are described below.

8.2.17.2. Value Comparison Rules

Since you can compare values of different types in the query language, value comparison rules have been defined.

These rules are used for:

Comparing values in comparison operators

Defining the maximum and minimum values in the MIN and MAX aggregate functions

Ordering query result records according to the order specified in the ORDER BY clause

If value types differ from each other, then the relationship between values is defined based on type priority:

NULL type (the lowest)

Boolean type

Number type

Date type

String type

Reference types

The relationship between different reference types is defined based on internal table reference numbers corresponding to one type or another.

If the data are of the same type, then their values are compared according to the following rules:

For Boolean type True value is greater than False.

For Number type common number comparison rules are applied.

For Date type earlier dates are less than later dates.

For String type comparison is performed according to established national features of the database. The string comparison operation ignores ending spaces, unlike string comparison in 1C:Enterprise script where ending spaces are used in comparison operations. For example, comparing the strings "bb" and "bb " in 1C:Enterprise script returns False (strings are not equal), but it returns True in the query language (strings are equal).

Reference types are compared based on their values (entry number, etc.).

Fields of the UniqueIdentifier type cannot be compared to other fields.

You cannot compare open-ended fields (open-ended strings, ValueStorage, ValueType field from charts of characteristic types tables).

IMPORTANT!

Any comparison operation with at least one NULL value results in False.

8.2.17.3. Operator for Value Matching Check

IN operator form to check if a value matches one of the listed

IN operator allows you to check if an expression value in its right part matches one of the values described in its left part. If at least one match is found, the result of the operator is True; else it is False. You can inverse operator action by using NOT. Values are compared according to the rules described in the "Value Comparison Rules" section.

Example:

SELECT
Nomenclature.Description
FROM
Catalog.Nomenclature AS Nomenclature
WHERE
Nomenclature.Parent.Description IN("Home Appliances",  "Office Equipment")

IN operator form to check membership in hierarchy

You can check if a catalog belongs to a specific hierarchy. IN HIERARCHY operator returns True if the left-hand expression value is a reference to a catalog item and is included in a value array in the right part or hierarchically belongs to a group included in the array.

Example:

//  A reference to any group of the Nomenclature catalog
//  is passed to the query as a Group parameter.

SELECT
Nomenclature.Description
FROM
Catalog.Nomenclature AS Nomenclature
WHERE
Nomenclature.Ref IN HIERARCHY(&Group)

The query result can act as a value array that is checked for matching. In this case you need to specify a query description to the right of IN operator.

Example:

SELECT
Nomenclature.Description
FROM
Catalog.Nomenclature AS Nomenclature
WHERE
Nomenclature.Ref IN HIERARCHY
(SELECT
Nomenclature.Ref
FROM
Catalog.Nomenclature  AS Nomenclature
WHERE
Nomenclature.Description  = "Clothes")

IN operator form to check for matching with one of the query results

Below is an example of how you can use this operator:

Example:

//  Select product names included
//  in the invoices
SELECT
Goods.Description
FROM
Catalog.Nomenclature AS Goods
WHERE
Goods.Ref IN
(SELECT
InvoiceContent.Nomenclature
FROM
Document.Invoice.Content AS InvoiceContent)

Result:

Fig. 218. Query Result (Fragment)

To obtain an inverse result (i.e. if you need to determine that the value does not match any of the query results) you should use the following query:

Example:

//  Select product names included
//  in the invoices
SELECT
Goods.Description
FROM
Catalog.Nomenclature AS Goods
WHERE
(NOT Goods.Ref IN
(SELECT
InvoiceContent.Nomenclature
FROM
Document.Invoice.Content AS InvoiceContent))

Result:

Fig. 219. Query Result (Fragment)

Please note that you can call table fields that encountered in the external query before the operation from IN operation query.

Example:

//  Select product names included
//  in the invoices
SELECT
Goods.Description
FROM
Catalog.Nomenclature AS Goods
WHERE
Goods.Ref IN
(SELECT
InvoiceContent.Nomenclature
FROM
Document.Invoice.Content AS InvoiceContent
WHERE
InvoiceContent.Nomenclature = Goods.Ref)

Result:

Fig. 220. Query Result (Fragment)

Use of IN / NOT IN operation for multiple fields

Nested query syntax:

(expression1, expression2, …, expressionN) IN (SELECT expression1, expression2, …, expressionN …)

Value table syntax:

(expression1, expression2, …, expressionN) IN (&Parameter)

The parameter should be a value table where the first N columns will be used in the IN operation.

8.2.17.4. Value-Within-Range Check Operator

The BETWEEN operator allows you to check whether or not an expression value indicated to its left falls into a range specified to its right. If the value is within the range, the operator returns True; otherwise it returns False. You can inverse the operator action by using NOT. For value comparison rules, see page 1-490.

Example:

SELECT
Nomenclature.Description,
Nomenclature.PurchasePrice
FROM
Catalog.Nomenclature AS Nomenclature
WHERE
Nomenclature.PurchasePrice BETWEEN 100 AND 1000

8.2.17.5. NULL Check Operator

The ISNULL operator allows you to check if an expression value to its left is NULL. If the value is NULL, the operator returns True; else – False. You can inverse the operator action by using NOT.

Example:

SELECT
Nomenclature.Description,
Nomenclature.PurchasePrice
FROM
Catalog.Nomenclature AS Nomenclature
WHERE
Nomenclature.PurchasePrice IS NULL

8.2.17.6. Reference Value Check Operator

REFS operator allows you to check if an expression value to its right references the table specified to its left. If the above condition is true, the operator returns True; otherwise it returns False. For information about dereferencing fields see page 1-474.

Example:

SELECT
Invoice.Number,
Invoice.Date,
FROM
Document.Invoice AS Invoice
WHERE
Invoice.Contractor REFS Catalog.Contractors

8.2.17.7. String-Like-Template Check Operator

LIKE operator allows you to compare an expression value specified on its left side with a template string specified on its right side. The expression value must be of the String type. If the expression value matches the template, the operator returns True; else – False.

The following characters in a template string are for system use only and have non-string character meaning:

% (percent): a sequence of any number of arbitrary characters;

_ (underscore): a single arbitrary character;

[…] (one or more characters in square brackets): any character listed in the square brackets. Enumerations can contain ranges, such as a-z, meaning a random character within the range, including ends of the range;

[^…] (a negation character followed by one or more characters in square brackets): any character besides those listed after the negation mark.

Any other character represents itself only and does not have any additional meaning.

If you must use one of these symbols as itself, it must be preceded by an escape character. The escape character itself (any appropriate symbol) is defined in the same operator after the ESCAPE keyword.

For example, the following template:

"%ABC[0-9][abcd]\_abc%" ESCAPE "\"

represents a substring consisting of a sequence of characters:

The letter A

The letter B

The letter C

A digit

One of the letters a, b, c or d

An underscore

The letter a

The letter b

The letter c

Additionally this sequence can be preceded by a custom set of characters.

8.3. EXECUTION AND USE OF QUERIES IN THE 1C:ENTERPRISE SCRIPT

A special set of objects provided in the 1C:Enterprise script permits to create queries, select, and process query results. These objects are used to generate queries, tab through query entries, etc.

8.3.1. Major Techniques and Practices

We will use several examples to illustrate work with queries in the 1C:Enterprise script. Here is a typical example of using a query.

//  Generate a query
Query  = New Query("SELECT Product.Description Description,
|Product.Parent.Description ParentDescription
|FROM Catalog.Goods Product");

//  Execute the query and write the result to the QueryResult variable.
QueryResult  = Query.Execute();

//  Obtain the query result selection.
Selection  = QueryResult.Select();

//  While the selection contains records ...
While  Selection.Next() Do

// ... display the result fields in the message window.
Product = Selection.Description;
Parent = Selection.ParentDescription;
Message("Product: " + Product + " Parent: " +  Parent);
EndDo;

As you can see from this example, work with queries is based on three main objects:

Query – an object that executes the query. In the example, this object is represented by the Query variable.

QueryResult – an object that contains the query result. In the example, this object is represented by the QueryResult variable.

QueryResultSelection – an object that can be used to select (i.e. tab through) records in the result. In the example, this object is represented by the Selection variable.

Review the QueryResultSelection object in more detail.

Use the following query for this purpose:

SELECT
InvoiceContent.Nomenclature AS Nomenclature,
InvoiceContent.Count AS Count
FROM
Document.Invoice.Content AS InvoiceContent

ORDER  BY
InvoiceContent.Nomenclature
TOTALS
SUM(Count)
BY
Nomenclature HIERARCHY

Result:

Fig. 221. Working Selection

In this table we have added column No.1 which is not present in the query result. We will use it later to identify query result records. Totals records in the table are shown in italic, while the totals records for hierarchical catalog levels are shown in bold.

8.3.1.1. Query Result Iteration Methods

Linear Result Iteration

The linear method of result iteration is the simplest. In the linear method, the selection yields records in the same sequence as in the query results. In the example above, this will be records 1, 2, 3, 4, 5 and so on, up to 20.

To get a linear selection from the result, call the Select() method of the QueryResult object without any parameters or with the QueryRe- sultIteration.Linear parameter.

Example:

SelectionMode  = QueryResultIteration.Linear;
Selection1  = QueryResult.Select(SelectionMode);
//  which is equivalent to the record
Selection1  = QueryResult.Select();

Hierarchical Result Iteration

The next method for result iteration is called hierarchical. This iteration is used only for records on a single level. To get a hierarchical selection from the result, call the Select() method for the QueryResult object with the QueryResultIteration.ByGroupsWithHierarchy parameter.

Example:

SelectionMode  = QueryResultIteration.ByGroupsWithHierarchy;
Selection2  = QueryResult.Select(SelectionMode);

In this example, selection from the results using hierarchical iteration will be completed only for records 1 and 11, as these are the only two records that are located at the highest level. To illustrate this, let us assume that the result is a tree, where totals records are nodes and detailed records are leaves. Here is what we will get (see fig. 222).

From this picture, you can see that only records 1 and 11 are located at the top level of the tree and that only these records will be included in the first pass of hierarchical iteration.

The next question would be how to obtain other records of the query result. To do this, you can get another selection from the QueryResultSelection object. This selection will be used for iteration over the subordinate records of the current selection record. In this example, when the Selection2 object is to be placed into record No 1, we will ask it for a hierarchical selection. This way, we will get a selection that will return records 2 and 7. When Selection2 is to be placed in record No 11, the final hierarchical selection will return records 12 and 16.

This is how the hierarchical iteration for query results works.

Fig. 222. Hierarchical Query Result Iteration

Please note that you can obtain nested selections of any type from the parent selection. Thus, if you request a linear Selection2, placed into record 1, it would return records 2 to 10. Review an example of this technique.

Example:

Procedure  ExecuteQuery()
// Generate a query.
Query = New Query;

// Set the query text
Query.Text = "SELECT
|InvoiceContent.Nomenclature AS Nomenclature,
|InvoiceContent.Count AS Count
|FROM
|Document.Invoice.Content AS InvoiceContent
|ORDER BY
|InvoiceContent.Nomenclature
|TOTALS
|SUM(Count)
|BY
|Nomenclature HIERARCHY";

// Execute the query and write the result to the variable
// QueryResult.
QueryResult = Query.Execute();

// Obtain the query result selection.
SelectionMode = QueryResultIteration.ByGroupsWithHierarchy;
Selection = QueryResult.Select(SelectionMode);

OutputRecursively(Selection);

EndProcedure

Procedure  OutputRecursively(Selection)
// While the selection contains records ...
While Selection.Next() Do
// ... display result fields in the message window
Product = Selection.Description;
Count = Selection.Count;
Message("Product: " + Product + " Count: " +  Count);

// Continue subordinate records selection
SelectionMode = QueryResultIteration.ByGroupsWithHierarchy;
OutputRecursively(Selection.Select(SelectionMode,  Selection.Group()));
EndDo;
EndProcedure

Result Iteration by Groups

The third and the final method of result iteration is by groups. It is similar to hierarchical iteration, but with one difference: records with hierarchical results are treated as detailed records rather than totals records. To get a result for a group selection query you have to call the Select() method for the QueryResult object with the QueryResultIteration.ByGroups parameter.

Example:

SelectionMode  = QueryResultIteration.ByGroups;
Selection2  = QueryResult.Select(SelectionMode);
//  After interation of all the records is complete, we get the following:
//  1, 2, 7, 11, 12, 16.

Example:

Procedure  ExecuteQuery()
// Generate a query.
Query = New Query;

// Set the query text
Query.Text = "SELECT
|InvoiceContent.Nomenclature AS Nomenclature,
|InvoiceContent.Count AS Count
|FROM
|Document.Invoice.Content AS InvoiceContent
|ORDER BY
|InvoiceContent.Nomenclature
|TOTALS
|SUM(Count)
|BY
|Nomenclature HIERARCHY";

// Execute the query and write the result to the variable
// QueryResult.
QueryResult = Query.Execute();

// Obtain the query result selection
SelectionMode = QueryResultIteration.ByGroups;
Selection = QueryResult.Select(SelectionMode);

// While the selection contains records ...
While Selection.Next() Do
// ... display result fields in the message window
Product = Selection.Description;
Count = Selection.Count;
Message("Product: "+Product+" Totals for product:  "+Count);

OutputChildRecords(Selection.Select());
EndDo;
EndProcedure

Procedure  OutputChildRecords(Selection)
// While the selection contains records ...
While Selection.Next() Do
// ... display result fields in the message window
Product = Selection.Description;
Count = Selection.Count;
Message("Product: "+Product+" Count: "+Count);
EndDo;
EndProcedure

8.3.1.2. Working with Selection

The QueryResultSelection object is used to cycle through query result records. A selection can be represented as an object that contains a reference to the current result record and grants the program access to all fields of the current record. Three methods are used for query record navigation:

Next() – go to the next result record according to the selection iteration order. At the first call, the selection will be positioned to the first record. When all records are selected, this method will indicate this by returning a False value.

NextByFieldValue() – get the next record with a value in the given field that is different from the value in the same field of the current record.

FindNext() – find a record with the specified values of certain fields.

Use of NextByFieldValue() Method

You can use this method to group results by field values.

Example:

SELECT
Doc.Nomenclature,
Doc.Ref.Contractor AS Contractor,
Doc.Count
FROM
Document.Invoice.Content AS Doc

ORDER  BY
Doc.Nomenclature.Description,
Contractor

Result:

Fig. 223. Query Result

You get a linear selection from the query result and can use the NextByFieldValue() method for iteration.

Example:

Selection  = QueryResult.Select();
While  Selection.NextByFieldValue("Product") Do
// there we'll get records with numbers 1, 5, 9, 12
While Selection.NextByFieldValue("Contractor") Do
// there we'll get records with numbers 1, 2, 3, 4 at first
// then we'll get records with numbers 5, 6, 7
// then we'll get records with numbers 9, 10, 11
// then we'll get records with numbers 12, 13, 14, 15
EndDo;
EndDo;

Please note that record 8 is not selected in the internal cycle, since it has the same Recipient field value as the previous record.

If you use the Next() method to get records by field value, all records with the same field value specified during the previous call of the NextByFieldValue() method will be selected.

Example:

Selection  = QueryResult.Select();
While  Selection.NextByFieldValue("Product") Do
// there we'll get records with numbers 1, 5, 9, 12
While Selection.Next() Do
// there we'll get records with numbers 1, 2, 3, 4 at first
// then we'll get records with numbers 5, 6, 7, 8
// then we'll get records with numbers 9, 10, 11
// then we'll get records with numbers 12, 13, 14, 15
EndDo;
EndDo;

Methods for Defining the Current Record Type

When a selection is placed into a record, you can use this selection to determine the characteristics of the record. You can use the following methods to get record characteristics:

Level() – determines the record level in the query result.

RecordType() – determines whether the record belongs to one of the following types:

○ Group total

○ Total by hierarchy

○ Detailed record

○ Overall

Group() – determines the name of the field that was used for calculating totals.

To illustrate how this method works, review what it returns for a query used as an example at the beginning of this chapter:

SELECT
InvoiceContent.Nomenclature AS Nomenclature,
InvoiceContent.Count AS Count
FROM
Document.Invoice.Content AS InvoiceContent

ORDER  BY
InvoiceContent.Nomenclature
TOTALS
SUM(Count)
BY
Nomenclature HIERARCHY

Result:

Fig. 224. Record Hierarchy

8.3.2. Work with Temporary Tables

The 1C:Enterprise query language permits using temporary tables in queries. Use of temporary tables helps to increase query performance and makes the text of complex queries easier to understand.

Work with temporary tables is supported by two components:

1C:Enterprise script TempTablesManager object that stores temporary table data;

query language syntax that allows creation of new and use of existing temporary tables.

8.3.2.1. Temporary Tables Manager

The temporary tables manager is used to manage the lifetime of temporary tables created during the application operation.

Any number of the temporary tables manager instances can be created in one application, each storing its own set of temporary tables. Each temporary table is uniquely identified with a name and all the temporary tables within one temporary table manager must have unique names.

NOTE

Temporary table names have to meet the requirements imposed upon variable names in the 1C:Enterprise script (see page 1-120).

The temporary tables manager instance can be created using the New wizard.

Example:

TempTablesManager = New TempTablesManager;

All the temporary tables created in the given manager instance exist only while the temporary tables manager instance exists. If the manager instance is destroyed, all of its temporary tables are also deleted.

The temporary tables manager can be forced to close by using the Close() method. This will delete all tables created in it. Further work with this manager will become impossible.

8.3.2.2. Creation of Temporary Tables

Temporary tables are created using the Query object of the 1C:Enterprise script.

The query is linked to the temporary tables manager through the TempTab- lesManager query property. It specifies the manager instance where the temporary tables must be created.

Example:

TempTablesManager  = New TempTablesManager;
Query  = New Query;
Query.TempTablesManager  = TempTablesManager;

A temporary table can be created based on database data or external source data (for example, a value table).

In order to create a temporary table based on database data, set the temporary tables manager for the Query object, then run a database query using the INTO keyword followed by the name of the created temporary table. The INTO keyword is located after the query selection list.

Example:

SELECT
Nomenclature.Code,
Nomenclature.Description
INTO  TemporaryTable
FROM
Catalog.Nomenclature AS Nomenclature

The query result will contain a single row with a Count column that will include the records placed in the created table.

If the temporary tables manager is not set or is closed or the specified temporary tables manager already has a table with the specified name, an error message will appear.

If you do not want to use an external source as a basis for temporary tables, you can use the FOR UPDATE clause; this is required when you need to place data into a temporary table and at the same time prevent other transactions from reading the data by locking it.

Example:

SELECT
Invoice.Ref,
Invoice.Number,
Invoice.Date,
INTO  TemporaryTable
FROM
Document.Invoice AS Invoice
WHERE
Invoice.Ref IN(&Documents)

FOR  UPDATE

If you need to create an index for a temporary table, specify the INDEX BY keyword in the query followed by the fields used to build an index.

Example:

SELECT
Nomenclature.Code AS Code,
Nomenclature.Description
INTO  TemporaryTable
FROM
Catalog.Nomenclature AS Nomenclature

INDEX  BY
Code

Fields for indexing must be included in the selection list.

If a value table is used as a source, it should have explicitly defined types of the values included in columns.

The FOR UPDATE clause must be used to create a temporary table and lock data in tables used as a basis for the temporary table.

Example:

SELECT
Invoice.Ref,
Invoice.Number,
Invoice.Date,
INTO  TemporaryTable
FROM
Document.Invoice AS Invoice
WHERE
Invoice.Ref IN(&Documents)

FOR  UPDATE

In order to create temporary tables based on an external source, specify the name of the parameter in which the external source will be placed, in the list of sources in the query text. The remaining syntax is identical to the usual creation of a temporary table.

The following entities can be external sources:

Value table

Tabular section

Query result

An example of the temporary table created on basis of external source is shown below:

SELECT
Source.Code,
Source.Description
INTO  TemporaryTable
FROM
&ExternalSource AS Source

In this example, the contents of the Code and Description columns are placed to TemporaryTable from an external source, for example, a value table passed as the ExternalSource parameter.

IMPORTANT!

You can't create a temporary table if it is created based on value tables with a UUID-type value in the columns.

IMPORTANT!

If a temporary table is on basis of external source, do not use unions and joins in the query or the fields that are field attributes of the tables used as a basis for the temporary table.

8.3.2.3. Use of Temporary Tables

To use existing temporary tables, set a temporary tables manager for the Query object; after this you can call the temporary tables included in this temporary tables manager by name, like standard query tables.

8.3.2.4. Deletion of Temporary Table

In order to delete a temporary table from the temporary tables manager, use the DROP keyword of the query language followed by the name of the deleted table.

Example:

DROP TemporaryTable

If the table to be dropped does not exist, an error message will be displayed.

8.3.3. Working with Batch Queries

The 1C:Enterprise platform supports operations with query batches. Query texts are separated by semicolons (";") within a batch query. Queries are executed sequentially; temporary tables created during query execution exist until the end of the entire query batch or until a query that drops a particular temporary table is executed in the batch.

Example:

Query  = New Query;
Query.Text=
"SELECT
|NomenclatureAccountingBalancesAndTurnovers.Nomenclature,
|NomenclatureAccountingBalancesAndTurnovers.QuantityReceipt,
|NomenclatureAccountingBalancesAndTurnovers.QuantityExpense,
|NomenclatureAccountingBalancesAndTurnovers.QuantityEndingBalance
|INTO NomenclatureAccounting
|FROM
|AccumulationRegister.NomenclatureAccounting.BalanceAndTurnovers
(, , Auto, , ) AS NomenclatureAccountingBalancesAndTurnovers
|;
|
|SELECT
|NomenclatureAccounting.Nomenclature,
|NomenclatureAccounting.QuantityExpense,
|NomenclatureAccounting.QuantityEndingBalance
|FROM
|NomenclatureAccounting AS NomenclatureAccounting
|;

Result  = Query.Execute();

The first query creates a temporary table; its data are used in the second query.

If the temporary tables manager is set for the Query object that executes the batch query, temporary tables that were not terminated within the batch query will be saved in this manager. Within the text of a batch query, you may use and destroy temporary tables that existed in the assigned temporary tables manager at the batch execution start.

In addition to the Execute() method that executes all batch queries sequentially and returns the result of the last batch query, the 1C:Enterprise platform provides another method, i.e. ExecuteBatch(). This method executes all queries sequentially and returns a result array for each query in the batch according to the succession of queries within the batch text. A query to terminate a temporary table results in the Undefined value which will also be included in the result array.

1C:Enterprise 8.3. Developer Guide. Contents

WORK WITH QUERIES

8.1. QUERY DATA SOURCES (TABLES)

8.2. QUERY LANGUAGE

8.2.1. Syntax Diagram for Query Language Constructs

8.2.2. Comments in Query Language

8.2.3. Use of Predefined Configuration Data

8.2.4. Query language keywords

8.2.5. Main Sections of Query Text

8.2.6. Query Description

8.2.6.1. Using DISTINCT Keyword

8.2.6.2. Using TOP Keyword

8.2.7. Selection Fields Description

NOTE 1

NOTE 2

8.2.7.1. Field Aliases in the Selection List

8.2.7.2. Nested Tables in the Selection Fields List

8.2.7.3. Empty Nested Tables in the Selection List

8.2.8. Query Sources Description

8.2.8.1. Join Specifications

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

8.2.8.2. Data Source Aliases

8.2.8.3. Nested Tables in the Source List

8.2.8.4. Nested Queries in the Source List

8.2.9. Query Result Filtration

8.2.10. Query Result Groups

8.2.11. Conditions for Aggregate Function Values

8.2.12. Query Union

8.2.13. Query Result Ordering

8.2.13.1. Ordering by Hierarchy

8.2.13.2. Ordering within Nested Tables

8.2.14. Autoorder Results

8.2.15. Calculation of Query Totals

8.2.15.2. Totals by Hierarchy

8.2.15.3. Date Addition

8.2.15.4. Calculation of Overalls

8.2.15.5. Using Totals and Groups Together

8.2.15.6. Totals Aliases

8.2.16. Expressions in the Query Language

8.2.16.1. Dereferencing Fields

8.2.16.2. Aggregate Functions of Query Language

8.2.16.3. Built-in Functions of Query Language

8.2.16.4. Case Operations in Query Language

8.2.16.5. Type Cast in Query Language

8.2.16.6. Constants and Parameters in Query Language

8.2.17. Conditions in Query Language

8.2.17.1. Logical Expressions in Query Language

8.2.17.2. Value Comparison Rules

8.2.17.3. Operator for Value Matching Check

8.2.17.4. Value-Within-Range Check Operator

8.2.17.5. NULL Check Operator

8.2.17.6. Reference Value Check Operator

8.2.17.7. String-Like-Template Check Operator

8.3. EXECUTION AND USE OF QUERIES IN THE 1C:ENTERPRISE SCRIPT

8.3.1. Major Techniques and Practices

8.3.1.1. Query Result Iteration Methods

Linear Result Iteration

Hierarchical Result Iteration

Result Iteration by Groups

8.3.1.2. Working with Selection

Use of NextByFieldValue() Method

Methods for Defining the Current Record Type

8.3.2. Work with Temporary Tables

8.3.2.1. Temporary Tables Manager

8.3.2.2. Creation of Temporary Tables

8.3.2.3. Use of Temporary Tables

8.3.2.4. Deletion of Temporary Table

8.3.3. Working with Batch Queries

Leave a Reply Cancel reply

1C:Enterprise Developer's Community