1c choice in the where request. Getting the value of a field of a complex type using a dot

The 1C query language is one of the main differences between versions 7.7 and 8. One of the most important points in the study of 1C programming is a query language. In 1C 8.3 queries are the most powerful and effective tool receiving data. The query language allows you to obtain information from the database in a convenient way.

The syntax itself is very much reminiscent of classic T-SQL, except that in 1C, using the query language, you can only receive data using the Select construct. The language also supports more complex constructs, for example, (request within a request). Queries in 1C 8 can be written in both Cyrillic and Latin.

In this article I will try to talk about the main keywords in the 1C query language:

choose
allowed
various
express
first
to change
meaning
value type (and REFERENCE operator)
choice
group by
having
ISNULL
Yes NULL
connections - right, left, internal, full.

As well as some small tricks of the 1C language, using which you can optimally construct the request text.

To debug queries in the 1C 8.2 system, a special tool is provided - the query console. You can see the description and download it using the link -.

Let's look at the most important and interesting operators of the 1C query language.

SELECT

In the 1C Enterprise 8 query language, any query begins with a keyword CHOOSE. In the 1C language there are no UPDATE, DELETE, CREATE TABLE, INSERT constructs; these manipulations are performed in object technology. Its purpose is to read data only.

For example:

CHOOSE
Current Directory.Name
FROM
Directory.Nomenclature AS Current Directory

The query will return a table with item names.

Near the structure CHOOSE you can find keywords TO CHANGE, ALLOWED, VARIOUS, FIRST …

ALLOWED— selects only records from the table that the current user has rights to.

VARIOUS— means that the result will not contain duplicate lines.

SELECTION (CASE)

Very often this design is underestimated by programmers. An example of its use:

Current Directory.Name,

WHEN Current Directory.Service THEN

"Service"

END HOW TO VIEWNomenclature

Directory.Nomenclature AS Current Directory

The example will return in the “Type of Nomenclature” field text value- “Product” or “Service”.

WHERE

The design of the 1C query language, which allows you to impose selection on the received data. Please note that the system receives all data from the server, and only then it is selected based on this parameter.

CHOOSE
Directory.Name
FROM
Current Directory.Nomenclature AS Current Directory
WHERE CurrentDirectory.Service = TRUE

In the example, we select records for which the value of the “Service” attribute is set to “True”. IN in this example One could get by with the following condition:

"WHERE IS THE SERVICE"

Essentially, we are selecting rows where the expression after the keyword is equal to "True".

You can use direct conditions in expressions:

WHERE Code = "005215"

Using the “VALUE()” operator in the conditions, use access to predefined elements and enumerations in a 1C request:

WHERE Item Type = Value(Enumeration.Item Types.Product)

Time values can be specified as follows:

WHERE Receipt Date > DATETIME(2012,01,01):

Most often, conditions are specified as parameters passed to the request:

Get 267 video lessons on 1C for free:

WHERE NomenclatureGroup= &NomenclatureGroup

A condition can be imposed on the type of attribute if it composite type:

If you need to limit selection from a list of values or an array, you can do the following:

WHERE is the Accumulation Register. Registrar B (&List of Documents for Selection)

The condition can also be complex, consisting of several conditions:

WHERE Receipt Date > DATETIME(2012,01,01) AND NomenclatureGroup= &NomenclatureGroup AND NOT Service

GROUP BY

Design of the 1C 8.2 query language used to group the result.

For example:

CHOOSE
Receipt of Goods and Services Goods. Goods,
SUM(Receipt of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Receipt of GoodsServicesGoods.Amount) AS Amount
FROM
Document. Receipt of Goods and Services. Goods HOW Receipt of Goods and Services Goods

GROUP BY
Receipt of GoodsServicesGoods.Goods

This request will summarize all receipts by amount and quantity by item.

Besides the keyword SUM You can use other aggregate functions: QUANTITY, NUMBER OF DIFFERENT, MAXIMUM, MINIMUM, AVERAGE.

HAVING

A design that is often forgotten, but it is very important and useful. It allows you to specify selection in the form of an aggregate function, this cannot be done in the design WHERE.

Example of using HAVING in a 1C request:

CHOOSE
Receipt of Goods and Services Goods. Goods,
SUM(Receipt of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Receipt of GoodsServicesGoods.Amount) AS Amount
FROM
Document. Receipt of Goods and Services. Goods HOW Receipt of Goods and Services Goods

GROUP BY
Receipt of Goods and Services Goods. goods

SUM(Receipt of GoodsServicesGoods.Quantity) > 5

So we will select the number of products that arrived more than 5 pieces.

MEANING()

For example:

WHERE Bank = Value(Directory.Banks.EmptyLink)

WHERE Nomenclature Type = Value(Directory.Nomenclature Types.Product)

WHERE Item Type = Value(Enumeration.Item Types.Service)

TYPE in request

The data type can be checked by using the TYPE() and VALUETYPE() functions or using the logical REFERENCE operator.

EXPRESS()

The Express operator in 1C queries is used to convert data types.

Syntax: EXPRESS(<Выражение>HOW<Тип значения>)

Using it, you can convert string values to date or reference values to string data, and so on.

IN practical application The Express() operator is very often used to convert fields of unlimited length, because fields of unlimited length cannot be selected, grouped, etc. If such fields are not converted, you will receive an error You cannot compare fields of unlimited length and fields of incompatible types.

CHOOSE
ContactInformation.Object,
EXPRESS(ContactInfo.View AS ROW(150)) AS View
FROM
Register of Information. Contact Information HOW Contact Information

GROUP BY
EXPRESS(ContactInfo.Representation AS ROW(150)),
ContactInformation.Object

ISNULL (ISNULL)

Quite a useful function of the 1C query language that checks the value in the record, and if it is equal NULL, This allows you to replace it with your value. Most often used when obtaining virtual tables of balances and turnover in order to hide NULL and put a clear 0 (zero).

ISNULL(Pre-Month Taxes.AppliedFSS Benefit, 0)

Such a function of the 1C query language ISNULL will return zero if there is no value, which will avoid an error.

JOIN

There are 4 types of connections: LEFT, RIGHT, COMPLETE, INTERNAL.

LEFT and RIGHT CONNECTION

Joins are used to link two tables based on a specific condition. Feature when LEFT JOIN is that we take the first specified table in its entirety and conditionally bind the second table. The fields of the second table that could not be bound by condition are filled with the value NULL.

An example of a left join in a 1C request:

It will return the entire table and fill in the “Bank” field only in those places where the condition “Counterparties.Name = Banks.Name” is met. If the condition is not met, the Bank field will be set to NULL.

RIGHT JOIN in 1C 8.3 language absolutely similar LEFT connection, with the exception of one difference: in RIGHT OF CONNECTION The “main” table is the second, not the first.

FULL CONNECTION

FULL CONNECTION differs from left and right in that it displays all records from two tables and connects only those that it can connect by condition.

For example:

FULL CONNECTION
Directory.Banks HOW Banks

BY

The query language will return both tables completely only if the Join records condition is met. Unlike a left/right join, it is possible for NULL to appear in two fields.

INNER JOIN

INNER JOIN different from full of topics, which displays only those records that could be connected according to a given condition.

For example:

FROM
Directory. Counterparties AS Clients

INNER JOIN
Directory.Banks HOW Banks

BY
Clients.Name = Banks.Name

This query will return only rows in which the bank and counterparty have the same name.

Conclusion

This is only a small part of the syntax from the 1C 8 query language; in the future I will try to consider some points in more detail, show and much more!

NULL is nothing more than the absence of a value. Many people confuse it with the value “0” of type number, an empty reference to an object, or an empty string. Because of this misconception, many mistakes arise.

The NULL value will appear if the request refers to a non-existent field, property, or broken link.

Based on SQL, which does not allow normal equality testing for NULL. Below are two ways to check for NULL in 1C 8.3.

The 1C 8.3 query language function ISNULL() has two input parameters:

expression to be tested;
replacement expression.

If the value being tested is NULL, then this function will return the value of the replacement expression. If the value is other than NULL, the expression being tested will be returned.

Below is an example. It selects all item items of the tabular part of the product from the “Receipt of goods and services” document. Using the left join, each item is assigned the last price from the “Item Prices” information register.

In this case, a situation may arise that for some position there may simply not be a price in the register. In this case, the ISNULL function will return us the usual zero. If you do not use it, then when you try to perform arithmetic operations on the “Price” field with a NULL value, we will receive an error.

CHOOSE

ISNULL(Prices.Price, 0) AS CurrentPrice
FROM

WHERE

THERE IS NULL in the SELECT statement

The equivalent of ISNULL() is ISNULL, which is used in the SELECT statement and checks whether the value is NULL. “IS” in this case implies equality and the query in the previous example would look like this:

CHOOSE
Products.Nomenclature AS Product,
CHOICE
WHEN Prices. Price IS NULL
THEN 0
OTHERWISE Prices.Price
END AS CurrentPrice
FROM
Document. Receipt of Goods and Services. Goods AS Goods
LEFT CONNECTION RegisterInformation.PricesNomenclature.SliceLast AS Prices
Software Products.Nomenclature = Prices.Nomenclature
WHERE
Products.Link = &LinkToDocument

Differences between the function ISNULL() and IS NULL

As you can see from the previous examples, in both cases the request returns the same data. The ISNULL() function is a shorthand version of SELECTION WHEN... IS NULL... END, but it is still preferable for the following reasons:

The ISNULL() function optimizes the query. It is read once, so when checking a complex expression, the request will process faster.
The ISNULL() function shortens the construction, making the query more readable.
When executing the ISNULL() function, the replacement expression is reduced to the type of the expression being tested for string types (string length) and numeric types (bit depth).

The query language in 1C 8 is a simplified analogue of the well-known “structured programming language” (as it is more often called, SQL). But in 1C it is used only for reading data; an object data model is used to change data.

Another interesting difference is the Russian syntax. Although in fact you can use English-language constructions.

Example request:

CHOOSE
Banks.Name,
Banks.CorrespondentAccount
FROM
Directory.Banks HOW Banks

This request will allow us to see information about the name and correspondent account of all banks existing in the database.

The query language is the simplest and effective way obtaining information. As can be seen from the example above, in the query language you need to use metadata names (this is a list of system objects that make up the configuration, i.e. directories, documents, registers, etc.).

Description of query language constructs

Query structure

To obtain data, it is enough to use the “SELECT” and “FROM” constructions. The simplest request looks like this:

SELECT * FROM Directories.Nomenclature

Where “*” means selecting all fields of the table, and Directories.Nomenclature – the name of the table in the database.

Let's look at a more complex and general example:

CHOOSE
<ИмяПоля1>HOW<ПредставлениеПоля1>,
Sum(<ИмяПоля2>) HOW<ПредставлениеПоля2>
FROM
<ИмяТаблицы1>HOW<ПредставлениеТаблицы1>
<ТипСоединения>COMPOUND<ИмяТаблицы2>HOW<ПредставлениеТаблицы2>
BY<УсловиеСоединениеТаблиц>

WHERE
<УсловиеОтбораДанных>

GROUP BY
<ИмяПоля1>

ORDER BY
<ИмяПоля1>

RESULTS
<ИмяПоля2>
BY
<ИмяПоля1>

IN this request we select the data of the fields “FieldName1” and “FieldName1” from the tables “TableName1” and “TableName”, assign synonyms to the fields using the “HOW” operator, and connect them using a certain condition “TableConnectionCondition”.

From the received data, we select only data that meets the condition from “WHERE” “Data Selection Condition”. Next, we group the request by the field “Field Name1”, while summing “Field Name2”. We create totals for the field “Field Name1” and the final field “Field Name2”.

The last step is to sort the request using the ORDER BY construct.

General designs

Let's look at the general structures of the 1C 8.2 query language.

FIRSTn

Using this operator, you can get the n number of first records. The order of the records is determined by the order in the query.

SELECT FIRST 100
Banks.Name,
Banks. Code AS BIC
FROM
Directory.Banks HOW Banks
ORDER BY
Banks.Name

The request will receive the first 100 entries of the “Banks” directory, sorted alphabetically.

ALLOWED

This design is relevant for working with the mechanism. The essence of the mechanism is to restrict reading (and other actions) to users for specific records in a database table, and not the table as a whole.

If a user tries to use a query to read records that are not available to him, he will receive an error message. To avoid this, you should use the “ALLOWED” construction, i.e. the request will read only the records allowed to it.

SELECT ALLOWED
Repository of Additional Information. Link
FROM
Directory.Repository of Additional Information

VARIOUS

Using “DIFFERENT” will prevent duplicate lines from entering the 1C query result. Duplication means that all request fields match.

SELECT FIRST 100
Banks.Name,
Banks. Code AS BIC
FROM
Directory.Banks HOW Banks

EmptyTable

This construction is used very rarely to combine queries. When joining, you may need to specify an empty nested table in one of the tables. The “EmptyTable” operator is just right for this.

Example from 1C 8 help:

SELECT Link.Number, EMPTY TABLE.(No., Item, Quantity) AS Composition
FROM Document.Expense Invoice
COMBINE EVERYTHING
SELECT Link.Number, Contents.(LineNumber, Product, Quantity)
FROM Document.Invoice Document.Invoice.Composition.*

ISNULL

A very useful feature that allows you to avoid many mistakes. YesNULL() allows you to replace the NULL value with the desired one. Very often used in checking for the presence of a value in joined tables, for example:

CHOOSE
Nomenclature Ref. Link,
IsNULL(Item Remaining.QuantityRemaining,0) AS QuantityRemaining
FROM

Can be used in other ways. For example, if for each row it is not known in which table the value exists:

ISNULL(InvoiceReceived.Date, InvoiceIssued.Date)

HOW is an operator that allows us to assign a name (synonym) to a table or field. We saw an example of use above.

These constructions are very similar - they allow you to get a string representation of the desired value. The only difference is that REPRESENTATION converts any values to a string type, while REPRESENTATIONREF converts only reference values. REFERENCE REPRESENTATION is recommended to be used in data composition system queries for optimization, unless, of course, the reference data field is planned to be used in selections.

CHOOSE
View(Link), //string, for example “Advance report No. 123 dated 10/10/2015
View(DeletionMark) AS DeleteMarkText, //string, “Yes” or “No”
ViewReferences(DeletionMark) AS DeleteMarkBoolean //boolean, True or False
FROM
Document.Advance Report

EXPRESS

Express allows you to convert field values to the desired data type. You can convert a value to either a primitive type or a reference type.

Express for a reference type is used to restrict the requested data types in fields of a complex type, often used to optimize system performance. Example:

EXPRESS(TableCost.Subconto1 AS Directory.Cost Items).Type of ActivityForTaxAccountingCosts

For primitive types, this function is often used to limit the number of characters in fields of unlimited length (such fields cannot be compared). To avoid the error " Invalid parameters in comparison operation. You can't compare fields
unlimited length and fields of incompatible types", you need to express such fields as follows:

EXPRESS(Comment AS Line(150))

DIFFERENCEDATE

Get 267 video lessons on 1C for free:

An example of using IS NULL in a 1C request:

SELECT * FROM
Ref
LEFT CONNECTION RegisterAccumulations.ProductsInWarehouses.Remaining AS Product Remaining
Software NomenclatureRef.Link = Sold GoodsCommitteesRemains.Nomenclature
WHERE NOT Remaining Products. QuantityRemaining IS NULL

The data type in a query can be determined by using the TYPE() and VALUETYPE() functions, or by using the logical REFERENCE operator. The two functions are similar.

Predefined values

In addition to using passed parameters in queries in the 1C query language, you can use predefined values or . For example, transfers, predefined directories, charts of accounts, and so on. For this, the “Value()” construct is used.

Usage example:

WHERE Nomenclature.Type of Nomenclature = Value(Directory.Types of Nomenclature.Product)

WHERE Counterparties.Type of Contact Information = Value(Enumeration.Types of Contact Information.Phone)

WHERE Account Balances.Accounting Account = Value(Chart of Accounts.Post-accounting.ProfitsLoss)

Connections

There are 4 types of connections: LEFT, RIGHT, COMPLETE, INTERNAL.

LEFT and RIGHT CONNECTION

For example:

It will return the entire table of Counterparties and fill in the “Bank” field only in those places where the condition “Counterparties.Name = Banks.Name” will be met. If the condition is not met, the Bank field will be set to NULL.

RIGHT JOIN in 1C language absolutely similar LEFT connection, with the exception of one difference - in RIGHT OF CONNECTION The “main” table is the second, not the first.

FULL CONNECTION

FULL CONNECTION differs from left and right in that it displays all records from two tables and connects only those that it can connect by condition.

For example:

FROM

FULL CONNECTION
Directory.Banks HOW Banks

BY

The query language will return both tables completely only if the condition to join the records is met. Unlike a left/right join, it is possible for NULL to appear in two fields.

INNER JOIN

INNER JOIN differs from full in that it displays only those records that could be connected according to a given condition.

For example:

FROM
Directory. Counterparties AS Clients

INNER JOIN
Directory.Banks HOW Banks

BY
Clients.Name = Banks.Name

This query will return only rows in which the bank and counterparty have the same name.

Associations

The JOIN and JOIN ALL constructs combine two results into one. Those. the result of performing two will be “merged” into one, common one.

That is, the system works exactly the same as regular ones, only for a temporary table.

How to use INDEX BY

However, one point should be taken into account. Building an index on a temporary table also takes time to complete. Therefore, it is advisable to use the “ ” construction only if it is known for sure that there will be more than 1-2 records in the temporary table. Otherwise, the effect may be the opposite - the performance of indexed fields does not compensate for the time it takes to build the index.

CHOOSE
Currency rates Latest cross-section. Currency AS Currency,
Currency rates Latest cross-section.
PUT Currency Rates
FROM
Information Register.Currency Rates.Last Slice(&Period,) AS Currency RatesLast Slice
INDEX BY
Currency
;
CHOOSE
PricesNomenclature.Nomenclature,
PricesNomenclatures.Price,
PricesNomenclatures.Currency,
Currency rates.Rate
FROM
Information Register.Nomenclature Prices.Last Slice(&Period,
Nomenclature B (&Nomenclature) AND PriceType = &PriceType) AS PriceNomenclature
LEFT JOIN Currency Rates AS Currency Rates
Software PricesNomenclatures.Currency = Currency Rates.Currency

Grouping

The 1C query language allows you to use special aggregate functions when grouping query results. Grouping can also be used without aggregate functions to “eliminate” duplicates.

The following functions exist:

Amount, Quantity, Number of different, Maximum, Minimum, Average.

Example #1:

CHOOSE
Sales of Goods and Services Goods. Nomenclature,
SUM(Sales of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Sales of GoodsServicesGoods.Amount) AS Amount
FROM

GROUP BY
Sales of Goods and Services Goods. Nomenclature

The request receives all lines with goods and summarizes them by quantity and amounts by item.

Example No. 2

CHOOSE
Banks.Code,
QUANTITY(DIFFERENT Banks.Link) AS Number Of Duplicates
FROM
Directory.Banks HOW Banks
GROUP BY
Banks.Code

This example will display a list of BICs in the “Banks” directory and show how many duplicates exist for each of them.

Results

Results are a way to obtain data from a system with a hierarchical structure. Aggregate functions can be used for summary fields, just as for groupings.

One of the most popular ways to use results in practice is batch write-off of goods.

CHOOSE

FROM
Document. Sales of Goods and Services. Goods HOW to Sale of Goods and Services Goods
ORDER BY

RESULTS
SUM(Quantity),
SUM(Sum)
BY
Nomenclature

The result of the query will be the following hierarchical:

General results

If you need to get totals for all “totals”, use the “GENERAL” operator.

CHOOSE
Sales of Goods and Services Goods. Nomenclature AS Nomenclature,
Sales of Goods and Services Goods. Link AS Document,
Sales of Goods and Services Goods. Quantity AS Quantity,
Sales of Goods and Services Goods. Amount AS Amount
FROM
Document. Sales of Goods and Services. Goods HOW to Sale of Goods and Services Goods
ORDER BY
Sales of Goods and Services Goods. Link. Date
RESULTS
SUM(Quantity),
SUM(Sum)
BY
GENERAL,
Nomenclature

As a result of executing the request, we get the following result:

In which 1 level of grouping is the aggregation of all necessary fields.

Arranging

The ORDER BY operator is used to sort the result of a query.

Sorting for primitive types (string, number, boolean) occurs by normal rules. For fields of reference types, sorting occurs according to the internal representation of the reference ( unique identifier), and not by code or by link presentation.

CHOOSE

FROM
Directory.Nomenclature AS Nomenclature
ORDER BY
Name

The request will display a list of names in the nomenclature directory, sorted alphabetically.

Auto-order

The result of a query without sorting is a chaotically presented set of rows. The developers of the 1C platform do not guarantee that rows will be output in the same sequence when executing the same queries.

If you need to display table records in a constant order, you must use the Auto-Order construct.

CHOOSE
Nomenclature.Name AS Name
FROM
Directory.Nomenclature AS Nomenclature
AUTO ORDER

Virtual tables

Virtual tables in 1C are unique feature 1C query language, which is not found in other similar syntaxes. Virtual table – quick way obtaining profile information from registers.

Each register type has its own set of virtual tables, which may differ depending on the register settings.

cut of the first;
cut of the latter.

leftovers;
revolutions;
balances and turnover.

movements from subconto;
revolutions;
speed Dt Kt;
leftovers;
balances and turnover
subconto.

base;
graph data;
actual period of validity.

For the solution developer, the data is taken from one (virtual) table, but in fact the 1C platform takes from many tables, transforming them into the required form.

CHOOSE
Products in Warehouses Remains and Turnover. Nomenclature,
ProductsInWarehousesRemainingAndTurnover.QuantityInitialRemaining,
ProductsInWarehousesRemainsAndTurnover.QuantityTurnover,
GoodsInWarehousesRemainsAndTurnover.QuantityIncoming,
GoodsInWarehousesRemainsAndTurnover.QuantityConsumption,
ProductsInWarehousesRemainingsAndTurnover.QuantityFinalRemaining
FROM
RegisterAccumulations.GoodsInWarehouses.RemainsAndTurnover AS GoodsInWarehousesRemainsAndTurnover

This request allows you to quickly get large number data.

Virtual Table Options

A very important aspect of working with virtual tables is the use of parameters. Virtual table parameters are specialized parameters for selection and configuration.

For such tables, it is considered incorrect to use selection in the “WHERE” construction. In addition to the fact that the query becomes suboptimal, it is possible to receive incorrect data.

An example of using these parameters:

Register of Accumulations. Goods in Warehouses. Balances and Turnovers (& Beginning of the Period, & End of the Period, Month, Movements and Borders of the Period, Nomenclature = & Required Nomenclature)

Algorithm for virtual tables

For example, the most used virtual table of the “Remains” type stores data from two physical tables – balances and movements.

When using a virtual table, the system performs the following manipulations:

We get the closest calculated value in terms of date and measurements in the totals table.
We “add” the amount from the movement table to the amount from the totals table.

Such simple actions can significantly improve the performance of the system as a whole.

Using the Query Builder

Query Builder– a tool built into the 1C Enterprise system that greatly facilitates the development of database queries.

The query builder has a fairly simple, intuitive interface. Nevertheless, let's look at using the query constructor in more detail.

The query text constructor is launched from the context menu (right mouse button) in the desired place in the program code.

Description of the 1C request constructor

Let's look at each tab of the designer in more detail. The exception is the Builder tab, which is a topic for another discussion.

Tables and Fields tab

This tab specifies the data source and fields that need to be displayed in the report. In essence, the constructions SELECT.. FROM are described here.

The source can be a physical database table, a virtual register table, temporary tables, nested queries, etc.

In the context menu of virtual tables, you can set virtual table parameters:

Connections tab

The tab is used to describe connections of several tables and creates constructions with the word CONNECTION.

Grouping tab

On this tab, the system allows you to group and summarize the required fields of the table result. Describes the use of the constructions GROUP BY, SUM, MINIMUM, AVERAGE, MAXIMUM, QUANTITY, NUMBER OF DIFFERENT.

Conditions tab

Responsible for everything that comes in the request text after the WHERE construction, i.e. for all the conditions imposed on the received data.

Advanced tab

Tab Additionally replete with all sorts of parameters that are very important. Let's look at each of the properties.

Grouping Selecting records:

First N– a parameter that returns only N records to the query (the FIRST operator)
No duplicates– ensures the uniqueness of the received records (DIFFERENT operator)
Allowed– allows you to select only those records that the system allows you to select taking into account (ALLOWED construction)

Grouping Request type determines what type of query will be: retrieving data, creating a temporary table, or destroying a temporary table.

Below there is a flag Lock received data for later modification. It allows you to enable the ability to set data locking, which ensures the safety of data from the moment it is read until it is changed (relevant only for Automatic mode interlocks, design TO CHANGE).

Joins/Aliases Tab

On this tab of the query designer, you can set the ability to join different tables and aliases (the HOW construct). The tables are indicated on the left side. If you set the flags opposite the table, the UNITE construction will be used, otherwise - UNITE ALL (differences between the two methods). On the right side, the correspondence of fields in different tables is indicated; if the correspondence is not specified, the query will return NULL.

Order tab

This specifies the order in which the values are sorted (ORDER BY) - descending (DESC) or ascending (ASC).

There is also an interesting flag - Auto-order(in the request - AUTO ORDERING). By default, the 1C system displays data in a “chaotic” order. If you set this flag, the system will sort data by internal data.

Query Batch tab

On the query designer tab, you can create new ones and also use it as a navigation. In the request text, packets are separated by the symbol “;” (comma).

“Query” button in the query designer

In the lower left corner of the request designer there is a Request button, with which you can view the request text at any time:

In this window, you can make adjustments to the request and execute it.

Using the Query Console

The Query Console is simple and convenient way for debugging complex queries and quickly obtaining information. In this article, I will try to describe how to use the Query Console and provide a link to download the Query Console.

Let's take a closer look at this tool.

Download 1C query console

First of all, to start working with the query console, you need to download it from somewhere. Treatments are usually divided into two types - controlled forms and conventional ones (or, sometimes, they are called 8.1 and 8.2/8.3).

I tried to combine these two types in one processing - in the desired operating mode it opens required form(in managed mode, the console only works in thick mode).

Description of the 1C query console

Let's start looking at the query console with a description of the main processing panel:

In the header of the query console, you can see the execution time of the last query with millisecond accuracy, this allows you to compare different designs in terms of performance.

The first group of buttons in the command bar is responsible for saving current queries to an external file. This is very convenient; you can always return to writing a complex request. Or, for example, store a list of typical examples of certain designs.

On the left, in the “Request” field, you can create new requests and save them in a tree structure. The second group of buttons is responsible for managing the list of requests. Using it you can create, copy, delete, move a request.

Executerequest– simple execution and results
Execute package– allows you to view all intermediate queries in a batch of queries
Viewing temporary tables– allows you to see the results that temporary queries return on a table

Request parameters:

Allows you to set the current parameters for the request.

In the query parameters window, the following is interesting:

Button Get from request automatically finds all parameters in the request for the convenience of the developer.
Flag Common parameters for all requests– when installed, its processing does not clear the parameters when moving from request to request in the general list of requests.

Set a parameter with a list of values It’s very simple, just when choosing a parameter value, click on the clear value button (cross), the system will prompt you to select the data type, where you need to select “Value List”:

Also in the top panel there is a button for calling up the query console settings:

Here you can specify parameters for autosaving queries and query execution parameters.

The request text is entered into the console request field. This can be done by simply typing a query test or calling special tool– query designer.

The 1C 8 request constructor is called from context menu(right mouse button) when clicking on the input field:

Also in this menu there are such useful functions as clearing or adding line breaks (“|”) to the request, or receiving the request code in this convenient form:

Request = New Request;
Request.Text = ”
|SELECT
| Currencies.Link
|FROM
| Directory.Currencies AS Currencies”;
RequestResult = Request.Execute();

The lower field of the query console displays the query result field, which is why this processing was created:

Also, the query console, in addition to the list, can display data in the form of a tree - for queries containing totals.

Query optimization

One of the most important points in increasing the productivity of 1C enterprise 8.3 is optimizationrequests. This point is also very important when passing the certification. Below we will talk about typical reasons for non-optimal query performance and optimization methods.

Selections in a virtual table using the WHERE construct

It is necessary to apply filters to the virtual table details only through the VT parameters. Under no circumstances should you use the WHERE construct for selection in a virtual table; this is a gross mistake from an optimization point of view. In the case of selection using WHERE, in fact, the system will receive ALL records and only then select the necessary ones.

RIGHT:

CHOOSE

FROM
Register of Accumulations. Mutual settlements with Participants of Organizations. Balances (
,
Organization = &Organization
And Individual = &Individual) HOW Mutual settlements with Participants of Organizations Balances

WRONG:

CHOOSE
Mutual settlements with Participants of Organizations Balances. Amount Balance
FROM
Register of Accumulations. Mutual settlements with Participants of Organizations. Balances (,) HOW Mutual settlements with Participants of Organizations Balances
WHERE
Mutual settlements with Participants of Organizations Balances. Organization = & Organization
AND Mutual settlements with Participants of Organizations Balances. Individual = &Individual

Getting the value of a field of a complex type using a dot

When receiving data of a complex type in a query through a dot, the system connects with a left join exactly as many tables as there are types possible in the field of the complex type.

For example, it is highly undesirable for optimization to access the register record field – registrar. The registrar has a composite data type, among which are all possible document types that can write data to the register.

WRONG:

CHOOSE
Record Set.Recorder.Date,
RecordSet.Quantity
FROM
RegisterAccumulations.ProductsOrganizations AS SetRecords

That is, in fact, such a query will access not one table, but 22 database tables (this register has 21 registrar types).

RIGHT:

CHOOSE
CHOICE
WHEN ProductsOrg.Registrar LINK Document.Sales of Products and Services
THEN EXPRESS(ProductsOrg.Registrar AS Document.Sales of GoodsServices).Date
WHEN GoodsOrg.Registrar LINK Document.Receipt of GoodsServices
THEN EXPRESS(GoodsOrg.Registrar AS Document.Receipt of GoodsServices).Date
END AS DATE,
ProductsOrg.Quantity
FROM
RegisterAccumulations.GoodsOrganizations AS GoodsOrganization

Or the second option is to add such information to the details, for example, in our case, adding a date.

RIGHT:

CHOOSE
ProductsOrganizations.Date,
ProductsOrganizations.Quantity
FROM
Register of Accumulations. Goods of Organizations AS Goods of Organizations

Subqueries in a join condition

For optimization, it is unacceptable to use subqueries in join conditions; this significantly slows down the query. It is advisable to use VT in such cases. To connect, you need to use only metadata and VT objects, having previously indexed them by connection fields.

WRONG:

CHOOSE …

LEFT JOIN (
SELECT FROM RegisterInformation.Limits
WHERE …
GROUP BY...
) BY …

RIGHT:

CHOOSE …
PUT Limits
FROM Information Register.Limits
WHERE …
GROUP BY...
INDEX BY...;

CHOOSE …
FROM Document. Sales of Goods and Services
LEFT JOIN Limits
BY …;

Joining Records with Virtual Tables

There are situations when, when connecting a virtual table to others, the system does not perform optimally. In this case, to optimize the performance of the query, you can try placing the virtual table in a temporary one, not forgetting to index the joined fields in the temporary table query. This is due to the fact that VTs are often contained in several physical tables DBMS, as a result, a subquery is compiled to select them, and the problem turns out to be similar to the previous point.

Using selections based on non-indexed fields

One of the most common mistakes when writing queries is using conditions on non-indexed fields, this contradicts query optimization rules. The DBMS cannot execute a query optimally if the query includes selection on non-indexable fields. If you take a temporary table, you also need to index the connection fields.

There must be a suitable index for each condition. A suitable index is one that satisfies the following requirements:

The index contains all the fields listed in the condition.
These fields are at the very beginning of the index.
These selections are consecutive, that is, values that are not involved in the query condition are not “wedged” between them.

If the DBMS does not select the correct indexes, the entire table will be scanned - this will have a very negative impact on performance and can lead to prolonged blocking of the entire set of records.

Using logical OR in conditions

That's all, this article covered the basic aspects of query optimization that every 1C expert should know.

A very useful free video course on query development and optimization, I strongly recommend for beginners and more!

Request . Text = "CHOOSE | StorageUnits.Link |FROM | Directory.usStorageUnits HOW touseStorageUnits // Example 1: comparison with an empty boolean value: |WHERE | StorageUnits.AllowSelectionFromReserveZone = False // Example 2. but if this Boolean is defined, then it’s better like this: // condition for a negative Boolean: |WHERE | NOT Storage Units. Allow Selection From Reserve Zone // Example 3. selection based on the condition of an empty field that has the type “directory of a specific type” |WHERE | StorageUnits.ActiveSelectionArea = VALUE(Directory.SelectionArea.EmptyLink) // Example 3a. selection based on the condition of an empty field having the type “document of a specific type” |WHERE | OurInformationRegister.Document = VALUE(Document.OurDocument.EmptyLink) // Example 3b. selection based on the condition of an empty field of type "documents" different types" (composite field) |WHERE | (OurInformationRegister.Document = VALUE(Document.OurDocument1.EmptyLink) | OR OurInformationRegister.Document = VALUE(Document.OurDocument2.EmptyLink) | OR... (etc. - we sequentially list the conditions for all possible types of this composite field) ) // Example 4. or vice versa, if you need to select a filled value of the "string" type, then the condition will help: |WHERE | Storage Unit.Name > """" // Example 5. if you need to select documents of a specific type, with a composite data type, for example, in the "RunningTasks" register, the "Task" resource has a composite type, among the values of which the document "Selection" is possible |WHERE | EXPRESS(Information RegisterExecutedTasks.Task AS Document.Selection) LINK Document.Selection // Example 5a. Another similar example when you need to select documents of a specific type | CHOICE | WHEN TO EXPRESS (ag Correspondence of Documents. DocumentBU AS Document. Receipt of Goods and Services) LINK Document. Receipt of Goods and Services | THEN ""Receipt of Goods and Services"" | WHEN TO EXPRESS (ag Correspondence of Documents. DocumentBU AS Document. Sales of Goods and Services) LINK Document. Sales of Goods and Services | THEN ""Sales of Goods and Services"" | ELSE """" | END AS Document View // Example 6. selection by condition of an undefined value: |WHERE | SavedSettings.User = UNDEFINED // Example 7. selection by type of movement "Incoming" of the accumulation register, "Expense" - similarly): |WHERE | RegProductsInRetail.MovementType = VALUE(MovementTypeAccumulation.Incoming) // Example 8. How to indicate in a request that it is not necessary to execute the request (for example, you need to programmatically, depending on some condition, return an empty request result - Request.Text = StrReplace(Request.Text, "WHERE Doc.Link = &DocumentLink" , "WHERE IS THE LIE");). To do this, just add the condition “Where is False”. By the way, regardless of the volume of data requested in the sample, such a request will be executed instantly. |WHERE IS THE LIE // Example 9. Checking that the query result contains data: If notRequest.Execute().Empty() Then // Example 10. selection based on an empty date: |WHERE | tbStrings.CancellationDate = DATETIME(1, 1, 1)

I decided to make my contribution and describe those features of the language that were not discussed in the above articles. The article is aimed at beginner developers.

1. “IZ” design.

In order to obtain data from the database, it is not at all necessary to use the “FROM” construction.
Example: We need to select all information about banks from the banks directory.
Request:

SELECT Directory.Banks.*

Selects all fields from the Banks directory. And is similar to the request:

SELECT Banks.* FROM Directory.Banks AS Banks

2. Ordering data by reference field

When we need to organize query data by primitive types: "String", "Number", "Date", etc., then everything is solved by using the "ORDER BY" construct if you need to order the data by a reference field? The reference field is a link, a unique identifier, i.e. Roughly speaking, some arbitrary set of characters and ordinary ordering may produce a result that is not entirely expected. To order reference fields, the "AUTO ORDER" construction is used. To do this, you must first order the data directly by the reference type using the "ORDER BY" construction, and then the "AUTO ORDER" construction.

In this case, for documents the ordering will occur in the order "Date->Number", for reference books in the "Main View". If the ordering does not occur by reference fields, then using the "AUTO ORDER" construction is not recommended.

In some cases, the "AUTO ORDER" construct can slow down the selection process. In a similar way, you can rewrite without auto-ordering for documents:

3.Obtaining a text representation of a reference type. "PRESENTATION" design.

When you need to display a field of a reference type, for example, the "Bank" field, which is a link to an element of the "Banks" directory, you need to understand that when this field is displayed, a subquery to the "Banks" directory will be automatically executed to obtain a view of the directory. This will slow down the data output. In order to avoid this, you need to use the "PREPRESENTATION" construction in the request in order to immediately obtain a representation of the object and then display it for viewing.

In the data composition system, this mechanism is used by default, but when creating layouts in cells, you should specify the representation of the reference field, and, for example, place the link itself in the transcript.

4. Condition for sampling data according to a template.

For example, you need to get mobile phones employees of the type (8 -123- 456-78-912). To do this, you need to set the following condition in the request:

SELECT Employee.Name, Employee.Phone AS Phone FROM Directory.Employees AS Employees WHERE Phone LIKE "_-___-___-__-__"

The "_" character is a service character and replaces any character.

5. Simultaneous use of totals and groupings.

Totals are often used in conjunction with groupings; in this case, aggregate functions may not be specified in the totals.

SELECT Provision of Services.Organization AS Organization, Provision of Services.Nomenclature AS Nomenclature, SUM(Provision of Services.Amount of Document) AS Sum of Document FROM Document.Provision of Services AS Provision of Services GROUP BY Provision of Services.Organization, Provision of Services.Nomenclature RESULTS BY GENERAL, Organization, nomenklatura

In this case, the query will return almost the same as the following query:

SELECT Provision of Services.Organization AS Organization, Provision of Services.Nomenclature AS Nomenclature, Provision of Services.Amount of Document AS Amount of Document FROM Document.Provision of Services AS Provision of Services RESULTS AMOUNT (Amount of Document) BY GENERAL, Organization, Nomenclature

Only the first query will collapse records with the same nomenclature.

6. Dereferencing fields.

Referring to fields through a dot is called the reference field dereferencing operation. For example Payment.Organization.Administrative Unit. In this case, in the reference field "Organization" of the "Payment" document, it refers to another table "Organizations", in which the value of the "Administrative Unit" attribute will be obtained. It is important to understand that when accessing fields through a dot, the platform implicitly creates a subquery and joins these tables.

Request:

Can be represented as:

SELECT Payment.Link, Payment.Organization, Payment.Organization, Organizations. AdministrativeUnit FROM Document.Payment AS Payment LEFT JOIN Directory.Organizations AS Organizations Software Payment.Organization = Organizations.Link

When dereferencing reference fields of a composite type, the framework attempts to create implicit joins to all tables that are part of that field's type. In this case, the query will not be optimal. If it is clearly known what type of field it is, it is necessary to limit such fields by type with a construct EXPRESS().

For example, there is an accumulation register “Undistributed payments”, where several documents can act as a registrar. In this case, it is incorrect to obtain the values of the registrar details in this way:

SELECT UnallocatedPayments.Registrar.Date, ..... FROM RegisterAccumulation.UnallocatedPayments AS UnallocatedPayments

you should restrict the type of the composite field to logger:

SELECT EXPRESS(UnallocatedPayments.Register AS Document.Payment).Date, ..... FROM RegisterAccumulation.UnallocatedPayments AS UnallocatedPayments

7. Construction "WHERE"

With a left join of two tables, when you impose a “WHERE” condition on the right table, we will get a result similar to the result with an inner join of tables.

Example. It is necessary to select all Clients from the Clients Directory and for those clients who have a payment document with the value of the attribute "Organization" = &Organization, display the document "Payment", for those who do not, do not display it.

The result of the query will return records only for those clients who had payment by organization in the parameter, and will filter out other clients. Therefore, you must first receive all payments for “such and such” organization in a temporary table, and then connect it to the “Clients” directory using a left join.

SELECT Payment.Link AS Payment, Payment.Shareholder AS Client PLACE toPayments FROM Document.Payment AS Payment WHERE Payment.Branch = &Branch; //////////////////////////////////////////////// ///////////////////////////// SELECT Clients.Link AS Client, ISNULL(tPayment.Payment, "") AS Payment FROM Directory .Clients AS Clients LEFT CONNECTION topayments AS topayments SOFTWARE Clients.Link = topayments.Client

You can get around this condition in another way. It is necessary to impose a "WHERE" condition directly on the relationship between the two tables. Example:

SELECT Clients.Link, Payment.Link FROM Directory.US_Subscribers AS US_Subscribers LEFT CONNECTION Document.Payment AS Payment Software (Clients.Link = Payment.Client AND Payment.Client.Name LIKE "Sugar Packet") GROUP BY Clients.Link, Payment. Link

8. Joins with Nested and Virtual Tables

Nested Queries often necessary to retrieve data based on some condition. If you then use them in conjunction with other tables, this can critically slow down the execution of the query.

For example, we need to get the Balance Amount as of the current date for some clients.

SELECT UnallocatedPaymentsBalances.Customer, UnallocatedPaymentsBalances.AmountBalance FROM (SELECT Clients.Link AS Link FROM Directory.Clients AS Clients WHERE Clients.Link IN(&Clients)) AS NestedQuery LEFT JOIN RegisterAccumulations.UnallocatedPayments.Balances AS UnallocatedPayments BY Nested nyRequest.Link = UnallocatedPaymentsBalances.Customer

When executing such a query, the DBMS optimizer may make errors when choosing a plan, which will lead to suboptimal execution of the query. When joining two tables, the DBMS optimizer selects a table joining algorithm based on the number of records in both tables. If there is a nested query, it is extremely difficult to determine the number of records that the nested query will return. Therefore, you should always use temporary tables instead of nested queries. So let's rewrite the request.

SELECT Clients.Link AS Link PLACE tClients FROM Directory.Clients AS Clients WHERE
Clients.Link B (&Clients) ; //////////////////////////////////////////////// ///////////////////////////// SELECT tClients.Link, UnallocatedPaymentsRemains.AmountRemaining, FROM tClients AS tClients LEFT JOIN RegisterAccumulations.UnallocatedPayments.Balances (, Client IN (SELECT tClients. Link FROM tClients)) AS UnallocatedPaymentsBalances tClients.Link = UnallocatedPaymentsBalances.Clients

In this case, the optimizer will be able to determine how many records the temporary table tClients uses and will be able to select the optimal algorithm for joining tables.

Virtual tables , allow you to obtain almost ready-made data for most applied tasks. (Slice of the First, Slice of the Last, Remains, Turnovers, Remains and Turnovers) Keyword here are virtual. These tables are not physical, but are compiled by the system on the fly, i.e. When receiving data from virtual tables, the system collects data from the final register tables, composes, groups and issues it to the user.

Those. When connecting to a virtual table, a connection is made to a subquery. In this case, the DBMS optimizer may also choose a non-optimal connection plan. If the query is not generated quickly enough and the query uses joins in virtual tables, then it is recommended to move the access to the virtual tables to a temporary table, and then make a join between two temporary tables. Let's rewrite the previous request.

SELECT Clients.Link AS Link PLACE tClients FROM Directory.Clients AS Clients INDEX BY Link WHERE
Clients.Link B (&Clients) ; //////////////////////////////////////////////// ///////////////////////////// SELECT UnallocatedPayments.AmountBalance, UnallocatedPayments.Client AS Client PLACE balances FROM RegisterAccumulations.UnallocatedPayments.Balances(, Client B (SELECT tClients. Link FROM tClients)) AS UnallocatedPaymentsBalances; //////////////////////////////////////////////// ///////////////////////////// SELECT tClients.Link, toRemainders.AmountRemaining AS AmountRemaining FROM tClients AS tClients LEFT JOIN toRemaining AS Remainder PO tClients.Link = tRemainings.Client

9.Checking the result of the request.

The result of the query may be empty; to check for empty values, use the following construct:

ResRequest = Request.Execute(); If resQuery.Empty() Then Return; endIf;

Method Empty() should be used before methods Choose() or Unload(), since retrieving the collection takes time.

It is not a revelation to anyone that it is extremely undesirable to use queries in a loop. This can critically affect the operating time of a particular function. It is highly desirable to receive all the data in the request and then process the data in a loop. But sometimes there are cases when it becomes impossible to move the request outside the loop. In this case, for optimization, you can move the creation of the query outside the loop, and in the loop, substitute the necessary parameters and execute the query.

This will save the system from syntax checking the request in a loop.

11. Construction "HAVING".

A design that is quite rare in requests. Allows you to impose conditions on the values of aggregate functions (SUM, MINIMUM, AVERAGE, etc.). For example, you need to select only those clients whose payment amount in September was more than 13,000 rubles. If you use the “WHERE” condition, you will first have to create a temporary table or a nested query, group records there by payment amount and then apply the condition. The “HAVING” construction will help avoid this.

SELECT Payment.Customer, AMOUNT(Payment.Amount) AS Amount FROM Document.Payment AS Payment WHERE MONTH(Payment.Date) = 9 GROUP BY Payment.Customer HAVING AMOUNT(Payment.Amount) > 13000

In the constructor, to do this, just go to the “Conditions” tab, add a new condition and check the “Custom” checkbox. Then just write Amount(Payment.Amount) > 13000

12. NULL value

I will not describe here the principles of three-valued logic in the database; there are many articles on this topic. Just briefly about how NULL may affect the result of the query. The value NULL is not actually a value, and the fact that the value is undefined is unknown. Therefore, any operation with NULL returns NULL, be it addition, subtraction, division or comparison. A NULL value cannot be compared to a NULL value because we don't know what to compare. Those. both of these comparisons are: NULL = NULL, NULL<>NULL is not True or False, it is unknown.

Let's look at an example.

For those clients who do not have payments, we need to display the “Sign” field with the value “No payments”. Moreover, we know for sure that we have such clients. And in order to reflect the essence of what I wrote above, let’s do it this way.

SELECT "No payments" AS Attribute, NULL AS Document PLACE topayments; //////////////////////////////////////////////// //////////////////////////// SELECT Clients.Link AS Client, Payment.Link HOW Payment PUT tClientPayment FROM Directory.Clients AS Clients LEFT CONNECTION Document.Payment AS Payment Software Clients.Link = Payment.Shareholder; //////////////////////////////////////////////// ///////////////////////////// SELECT tClientPayment.Client FROM tClientPayment AS tClientPayment INTERNAL JOIN tPayment AS tTopay BY tClientPayment.Payment = tPayment. Document

Pay attention to the second temporary table tClientPayment. With the left join I select all clients and all payments for these clients. For those clients who do not have payments, the “Payment” field will be NULL. Following the logic, in the first temporary table “tPayments” I designated 2 fields, one of them NULL, the second line “Does not have payments”. In the third table I join inner join tables "tClientPayment" and "tPayment" for the fields "Payment" and "Document". We know that in the first table the “Document” field is NULL, and in the second table, those who do not have payments in the “Payment” field are also NULL. What will such a connection return to us? But it won't return anything. Because the comparison NULL = NULL does not evaluate to True.

In order for the request to return the expected result, let’s rewrite it:

SELECT "No payments" AS Attribute, VALUE(Document.Payment.EmptyLink) AS Document PLACE toPayments; //////////////////////////////////////////////// ///////////////////////////// SELECT Clients.Link AS Client, ISNULL(Payment.Link, VALUE(Document.Payment.EmptyLink )) HOW Payment PUT tClientPayment FROM Directory.Clients AS Clients LEFT CONNECTION Document.Payment AS Payment BY Clients.Link = Payment.Shareholder; //////////////////////////////////////////////// ///////////////////////////// SELECT tClientPayment.Client FROM tClientPayment AS tClientPayment INTERNAL JOIN tPayment AS tTopay BY tClientPayment.Payment = tPayment. Document

Now, in the second temporary table, we have indicated that if the “Payment” field is NULL, then this field = an empty link to the payment document. In the First table we also replaced NULL with an empty reference. Now the connection involves non-NULL fields and the request will return the expected result.

All requests contained in the article reflect the situations that I would like to consider and nothing more. ABOUT They may not be delusional or suboptimal, the main thing is that they reflect the essence of the example.

13. An undocumented feature of the "CHOICE WHEN...THEN...END" design.

In the case when it is necessary to describe the “Conditions” construction in the request, we use the standard syntax:

SELECT SELECTION WHEN Users.Name = "Vasya Pupkin" THEN "Our favorite employee" ELSE "We don't know this" END AS Field1 FROM Directory.Users AS Users

But what if, for example, we need to get the name of the month in a request? Writing a huge construction in a request is ugly and time-consuming, so this form of writing above can help us out:

SELECT MONTH(US_CalculationConsumption_TurnoverSchedule.CalculationPeriod) WHEN 1 THEN "January" WHEN 2 THEN "February" WHEN 3 THEN "March" WHEN 4 THEN "April" WHEN 5 THEN "May" WHEN 6 THEN "June" WHEN 7 THEN "July" WHEN 8 THEN "August" WHEN 9 THEN "September" WHEN 10 THEN "October" WHEN 11 THEN "November" WHEN 12 THEN "December" END AS A Month

Now the design looks less cumbersome and is easy to understand.

14. Batch query execution.

In order not to multiply requests, you can create one large request, split it into packages and work with it.
For example, I need to get the following fields from the "Users" directory: "Date of Birth" and the available roles for each user. upload this to different tabular parts on the form. Of course, you can do this in one request, then you will have to iterate through the records or collapse them, or you can do this:

SELECT Users.Link AS Full Name, Users.Date of Birth, Users.Role PUT vtUsers FROM Directory.Users AS Users; //////////////////////////////////////////////// ///////////////////////////// SELECT tueUsers.Full name, tueUsers.Date of Birth FROM tueUsers AS tueUsers GROUP BY tueUsers.full name, tueUsers. Date of Birth; //////////////////////////////////////////////// ///////////////////////////// SELECT wUsers.Full Name, wUsers.Role FROM wUsers AS wUsers GROUP BY wUsers.Full Name, wUsers. Date of Birth

tPackage = Request.ExecutePackage();

TP_BirthDate = tPackage.Upload();
TP_Roles = tPackage.Unload();

As we can see, the query can be executed in a batch and the result can be processed as an array. In some cases it is very convenient.

15. Conditions in a batch request

For example, we have a batch request, where first we get the fields: “Name, Date of Birth, Code” from the “Users” directory and want to get records with conditions for these fields from the “Individuals” directory.

SELECT Users.Individual.Name AS Name, Users.Individual.Date of Birth AS Date of Birth, Users.Individual.Code AS Code PLACE vtUsers FROM Directory.Users AS Users; //////////////////////////////////////////////// ///////////////////////////// SELECT Individuals. Link AS Individual FROM Directory. Individuals AS Individuals

You can impose conditions like this:

WHERE Individuals.Code IN (SELECT tueUsers.Code FROM tueUsers) AND Individuals.Name IN (SELECT tueUsers.Code FROM tueUsers) AND Individuals.BirthDate IN (SELECT tueUsers.DateBirth FROM tueUsers)

And you can do it like this:

WHERE (Individuals.Code, Individuals.Name, Individuals.Date of Birth) IN (SELECT tueUsers.Code, tueUsers.Name, tueUsers.Date of Birth FROM tueUsers)

Moreover, it is necessary to maintain order.

16. Calling the query builder for “condition” in a batch request

When it is necessary to impose a condition, as in the example above, you can forget what this or that field is called in the virtual table.
For example, you need to impose a condition on the "Date of Birth" field, and in the virtual table this field is called "Debtor's Date of Birth", and if you forget the name, you will have to exit editing the condition without saving and look at the name of the field. In order to avoid this, you can use the following technique.

It is necessary to put brackets after Construction “B” and leave an empty space (space) between the brackets, select this space and call the query constructor. The designer will have access to all tables of the batch query. The technique works both on virtual register tables and on the “Conditions” tab. In the latter case, you need to check the "P (arbitrary condition)" box and enter the editing mode "F4".

The queries were often made up on the fly and they simply serve to illustrate the “techniques” that I was considering.

I wanted to look at the use of indexes in queries, but this is a very broad topic. I’ll put it in a separate article, or add it here later.

upd1. Points 11,12
upd2. Points 13,14,15,16

Literature used:
Query language "1C:Enterprise 8" - E.Yu. Khrustaleva
Professional development in the 1C:Enterprise 8 system."