Merge query results. Combine query results How to combine queries into one 1s

The query language is one of the fundamental mechanisms of 1C 8.3 for developers. With the help of queries, you can quickly get any data stored in the database. Its syntax is very similar to SQL, but there are some differences.

The main advantages of the 1C 8.3 (8.2) query language over SQL:

dereferencing reference fields (turning one or more dots to object attributes);
work with the results is very convenient;
the ability to create virtual tables;
the request can be written both in English and in Russian;
the ability to block data to avoid deadlocks.

Disadvantages of the query language in 1C:

unlike SQL, in 1C queries do not allow you to change data;
lack of stored procedures;
the impossibility of converting a string to a number.

Consider our mini tutorial on the basic constructions of the 1C query language.

Due to the fact that requests in 1C only allow you to receive data, any request must begin with the word "SELECT". After this command, the fields from which you want to get data are indicated. If you specify "*", then all available fields will be selected. The place from where the data will be selected (documents, registers, directories, etc.) is indicated after the word "FROM".

In the example below, the names of the entire nomenclature are selected from the "Nomenclature" reference book. After the word “HOW”, aliases (names) for tables and fields are indicated.

CHOOSE
Nomenclature.Name AS NameNomenclature
FROM
Directory. Nomenclature AS Nomenclature

Next to the "SELECT" command, you can specify keywords:

VARIOUS. The query will select only rows that differ in at least one field (without duplicates).
FIRST n, Where n– the number of rows from the beginning of the result to be selected. Most often, this construction is used in conjunction with sorting (ORDER BY). For example, when you need to select a certain number of the latest documents by date.
ALLOWED. This design allows you to select from the database only those records that are available to the current user. If this keyword is used, the user will receive an error message if they try to query records they do not have access to.

These keywords can be used all together or separately.

FOR CHANGE

This clause locks data to avoid conflicts. Locked data will not be read from another connection until the end of the transaction. In this clause, you can specify specific tables that you want to lock. Otherwise, all will be blocked. The design is relevant only for the automatic blocking mode.

Most often, the "FOR CHANGE" clause is used when receiving balances. Indeed, when several users work in the program at the same time, while one receives the balances, the other can change them. In this case, the resulting balance will no longer be correct. If you block the data with this proposal, then until the first employee receives the correct balance and performs all the necessary manipulations with it, the second employee will have to wait.

CHOOSE
Mutual settlements. Employee,
Mutual settlements. Amount Mutual settlements Balance
FROM
Accumulation Register. Mutual Settlements WITH Employees. Balances AS Mutual Settlements
FOR CHANGE

WHERE

The construction is necessary for imposing any selection on the unloaded data. In some cases of obtaining data from registers, it is more reasonable to prescribe selection conditions in the parameters of virtual tables. When using "WHERE", all records are obtained first, and only then the selection is applied, which significantly slows down the query.

The following is an example of a request to get contact persons with a specific position. The selection parameter has the following format: &ParameterName (parameter name is arbitrary).

SELECTION (CASE)

The construct allows you to specify conditions directly in the request body.

In the example below, the "AdditionalField" will contain text depending on whether the document is posted or not:

CHOOSE
AdmissionT&U.Link,
CHOICE
WHEN
THEN "Document posted!"
ELSE "Document not posted..."
END AS AdditionalField
FROM
Document.Receipt of GoodsServices AS ReceiptT&C

JOIN

Joins link two tables by a certain link condition.

LEFT/RIGHT JOIN

The essence of the LEFT join is that the first specified table is taken completely and the second one is attached to it by the condition of the connection. If there are no records corresponding to the first table in the second, then NULL is substituted as their values. Simply put, the main table is the first specified table and the data of the second table (if any) is already substituted for its data.

For example, you need to get item items from the documents “Receipt of goods and services” and prices from the information register “Item prices”. In this case, if the price of any position is not found, substitute NULL instead. All items from the document will be selected regardless of whether they have a price or not.

CHOOSE
Receipt of T&U. Nomenclature,
Prices.Price
FROM
Document.Receipt of GoodsServices.Goods AS ReceiptT&C
INNER JOIN
ON Receipt of Q&A.Nomenclature = Prices.Nomenclature

In RIGHT, everything is exactly the opposite.

FULL CONNECTION

This type of join differs from the previous ones in that all records of both the first table and the second will be returned as a result. If no records are found in the first or second table for the specified link condition, NULL will be returned instead.

When using the full join in the previous example, all item items from the Goods and Services Receipt document and all the latest prices from the Item Prices register will be selected. The values of not found records, both in the first and in the second table, will be NULL.

INNER JOIN

The difference between an INNER join and a FULL join is that if a record is not found in at least one of the tables, then the query will not display it at all. As a result, only those item items from the Goods and Services Receipt document will be selected for which there are entries in the Item Prices information register, if in the previous example we replace FULL with INTERNAL.

GROUP BY

Grouping in 1C queries allows you to collapse table rows (grouping fields) according to a certain common feature (grouping fields). Grouping fields can only be displayed using aggregate functions.

The result of the next query will be a list of item types with their maximum prices.

CHOOSE
,
MAX(Price.Price) AS Price
FROM

GROUP BY
Prices.Nomenclature.TypeNomenclature

RESULTS

Unlike grouping, when using totals, all records are displayed and total rows are already added to them. Grouping displays only generalized records.

Results can be summarized for the entire table (using the keyword "GENERAL"), for several fields, for fields with a hierarchical structure (keywords "HIERARCHY", "ONLY HIERARCHY"). When summing up, it is not necessary to use aggregate functions.

Consider an example similar to the example above using grouping. In this case, the query result will return not only grouped fields, but also detailed records.

CHOOSE
Prices.Nomenclature.Type of Nomenclature AS Type of Nomenclature,
Prices.Price AS Price
FROM
RegisterInformation.PricesNomenclature.SliceLast AS Prices
RESULTS
MAXIMUM(Price)
BY
Type Nomenclature

HAVING

This operator is similar to the WHERE operator, but is only used for aggregate functions. Other fields than those used by this operator must be grouped. The "WHERE" operator is not applicable for aggregate functions.

In the example below, the maximum item prices are selected if they exceed 1000, grouped by item type.

CHOOSE

MAX(Price.Price) AS Price
FROM
RegisterInformation.PricesNomenclature.SliceLast AS Prices
GROUP BY
Prices.Nomenclature.TypeNomenclature
HAVING
MAX(Prices.Price) > 1000

SORT BY

The "ORDER BY" operator sorts the query result. To ensure that records are output in a consistent order, AUTO-ORDER is used. Primitive types are sorted according to the usual rules. Reference types are sorted by GUID.

An example of getting a list of employees sorted by name:

CHOOSE
Employees.Name AS Name
FROM
Directory. Employees AS Employees
SORT BY
Name
AUTO ORDER

Other constructions of the 1C query language

UNITE- the results of two queries in one.
UNITE ALL– similar to JOIN, but without grouping identical rows.
EMPTY TABLE- sometimes used when joining queries to specify an empty nested table.
PUT- creates a temporary table to optimize complex 1C queries. Such requests are called batch requests.

Query language features

SUBSTRING truncates a string from a specified position by the specified number of characters.
YEAR…SECOND allow you to get the selected value of the numeric type. The input parameter is a date.
BEGINNING OF THE PERIOD AND END OF THE PERIOD are used when working with dates. The period type (DAY, MONTH, YEAR, etc.) is specified as an additional parameter.
ADDDATE allows you to add or subtract from the date the specified time of a certain type (SECOND, MINUTE, DAY, etc.).
DATE DIFFERENCE determines the difference between two dates, specifying the type of output value (DAY, YEAR, MONTH, etc.).
IS NULL replaces the missing value with the specified expression.
PRESENTATION and PRESENTATIONLINKS get the string representation of the specified field. They are used for any values and only reference values, respectively.
TYPE, VALUE TYPE are used to determine the type of the input parameter.
LINK is a logical comparison operator for the attribute value type.
EXPRESS is used to convert the value to the desired type.
DATE TIME gets a value of type "Date" from numeric values (Year, Month, Day, Hour, Minute, Second).
MEANING in a 1C request, it is used to specify predefined values \u200b\u200b- directories, enumerations, plans for types of characteristics. Usage example: " Where LegalIndividual = Value(Enumeration.LegalIndividual.Individual)«.

Query Builder

To create queries with 1C, there is a very convenient built-in mechanism - the query designer. It contains the following main tabs:

"Tables and fields" - contains the fields to be selected and their sources.
"Links" - describes the conditions for the CONNECTION construct.
"Grouping" - contains a description of the constructions of groupings and summarized fields by them.
"Conditions" - is responsible for the selection of data in the request.
"Advanced" - additional query parameters, such as the keywords of the "SELECT" command, etc.
“Joins / Aliases” - the possibilities of joining tables are indicated and aliases are set (the “HOW” construct).
"Order" - is responsible for sorting the result of queries.
"Totals" - similar to the "Grouping" tab, but is used for the "TOTALS" construction.

The text of the request itself can be viewed by clicking on the "Request" button in the lower left corner. In this form, it can be corrected manually or copied.

Query Console

To quickly view the result of a query in the "Enterprise" mode, or to debug complex queries, use . The query text is written in it, parameters are set, and its result is shown.

You can download the query console on the ITS disk, or by .

If you are working with a single table, then you do not need to use a join. It is a completely different matter when you work with several tables and, for example, you need to get data on both purchases and sales of goods.

The sentence is used to merge UNITE. In fact, the results of queries are combined, that is, each query collects data independently of each other, and then these results are combined into one. And only on the last query, other operations are performed, for example, ordering and calculation of totals.

When combining queries, the results are "vertically glued", that is, the result rows of the first query go first, then the second.

Let's move on to practice:
Code 1C v 8.x SELECT
Nomenclature,
Quantity,
Sum
FROM
Document.Receipt.Goods

UNITE

CHOOSE
Nomenclature,
Quantity,
Sum
FROM
Document.Receipt.Goods

The result of this query is the following:
Nomenclature Quantity Amount
Paper A4 25 14 500
Stickers 500 l 8 4 880
File transparent 5 4 100
Office set 8 8 840

As a result, we will get two tables that will be connected to each other. It is difficult to see where the Incoming document ends and the Outgoing document begins. But we will do something to understand it:
Code 1C v 8.x
CHOOSE
Nomenclature,
Quantity AS QtyIncoming,
Amount AS SumIncome,
0 HOW Qty Consumption,
0 AS SUMExpense
FROM
Document.Receipt.Goods

UNITE

CHOOSE
Nomenclature,
0 AS NumberIncoming,
0 AS SumIncome,
Quantity AS Qty Expense,
Amount AS SumExpense
FROM
Document.Consumable.Goods

As you can see from the request text, we have set different aliases for each document for quantity and amount. Also, in place of those fields where we will receive the values \u200b\u200bof the amount and quantity, we will put zeros. This is necessary so that the empty values \u200b\u200bof the amount and amount are replaced with zeros.

Paper A4 25 14 500
Stickers 500 l 8 4 880
File transparent 5 4 100
Office set 8 8 840
Office set 1 1 105
Transparent file 1 820
Stickers 500 l 1 610

Now we need to remove duplicate elements from the query result. We have two queries, and if we collapse each separately to exclude duplicate elements, then we will not succeed. Therefore, we will bring the query to the following form:
Code 1C v 8.x
CHOOSE
Nomenclature,
SUM(Number of Income) AS Number of Income,
SUM(SumIncome) AS SumIncome,
SUM(number of expenses) AS amount of expenses,
SUM(SumExpense) AS SumExpense
FROM

(CHOOSE
Nomenclature,
Quantity AS QtyIncoming,
Amount AS SumIncome,
0 HOW Qty Consumption,
0 AS SUMExpense
FROM
Document.Receipt.Goods

UNITE

CHOOSE
Nomenclature,
0 AS NumberIncoming,
0 AS SumIncome,
Quantity AS Qty Expense,
Amount AS SumExpense
FROM
Document.Consumable.Products) AS NestedRequest

GROUP BY Nomenclature

So, we see that our entire query is enclosed in brackets and named as NestedQuery. This is done so that we can group records from both subqueries and eliminate duplicate items. Such a construct is called a nested query. The result of this query is the following:
Nomenclature QtyIncoming SumIncoming QtyExpense SumExpense
Paper A4 25 14 500
Stickers 500 l 8 4 880 1 610
Office set 5 4 100 1 1 105
File transparent 8 8 840 1 820

As a result, we get a grouped table in which the data from nested subqueries is reduced to a clear form. The number of nested queries should not exceed 255. We used four subqueries here.

If we want to leave only unique values of records in the query, without using grouping. It is necessary to write the sentence JOIN without the keyword ALL.

Information taken from the site

; Subqueries (in development).

Receipt of goods and services And Return of goods to the supplier for the specified period.

New tabs: Unions/Aliases.

Theoretical part of lesson number 4

Query Builder 1c allows you to create query aggregations. With their help, you can sequentially output data obtained from several queries into the result without using relationships. The only condition for joining is the same set of fields in every single query.

In the constructor, go to the tab Unions/Aliases. It is intended for creating query joins and for setting aliases for query fields. Field aliases are needed if you are not comfortable with the standard database field names. If a query field consists only of a database table field, then an alias is not required for it. If you used query language functions when creating a field, then an alias for such a field is required. For such fields, the query builder creates standard aliases Field1…FieldN, these aliases can be replaced with whatever is convenient for you.

Consider the sections of the tab Unions / Aliases:

Requests(red frame). This section contains a table that shows all combined queries, using the menu located above this section, you can add new ones, copy existing ones and delete selected ones, as well as swap them. When adding or copying a query, the designer goes to the tab Tables and fields, where you can set the database tables and required fields for the new query. You can switch between requests using the tabs that will appear on the right side of the constructor as new 1s requests are added;

Table Requests two columns:
- Name. Set automatically in the format Request 1 … Request N;
- No duplicates. Set if it is necessary to exclude duplicate rows when concatenating with a previous query. It is worth noting that this flag will only affect the union of the request in which it is set with the previous request.

Aliases(blue frame). In this section, you can set aliases for query fields, as well as match fields for combined queries so that in the query result they are in the same column and displayed under the same alias. If the fields in the combined queries are named the same, then the correspondence between them is adjusted automatically. To set up the correspondence of the fields, you need to find the line with the required alias in the table, find the required query in the columns and select the field in the drop-down list.

Practical part of lesson number 4

Let's analyze the solution of the problem given at the beginning of the lesson. Let me remind you the conditions:

Task: select all posted documents by query Receipt of goods and services And Return of goods to the supplier for the specified period.

Let's create a new request;
Let's launch the query builder;
Let's choose a table Incoming Goods Services from a branch Documentation;
From table Incoming Goods Services choose a field Link;
Let's go to the tab Conditions;
In chapter fields, open the branch Incoming Goods Services using the "+" button;
Let's find props date and drag it to the conditions section, select the comparison operator Between and specify the parameters for the beginning and end of the period, for example Beginning of period And EndPeriod;
Let's go to the tab Unions / Aliases.
Add a new request, flag No duplicates there is no need to raise, since we use different types of documents;
Query Builder 1s will automatically go to the tab Tables and fields. In the right part of the window, you will see that the second join request is active;
Let's repeat steps 1 - 7, for the table Return of Goods to Supplier;
Let's go back to the tab. Unions / Aliases. In the alias table, you can see that the fields Link both tables are on the same line, which means that links to both documents will be in the same column as a result of the query;
Change the column name from Link on Document(set the field alias). To do this, double-click on the field name with the left mouse button. Then you can set your name, enter there Document;
The request is ready, click the "OK" button at the bottom of the designer window.

As a result, we will get a request with the following text.

There are situations when it is necessary to combine several queries in one query, and table joins cannot help in any way. It's easiest to show with an example.

Let's assume that in our system the facts of purchase and sale of goods are registered by documents Income and Expenditure, respectively. A counterparty can be both a buyer and a supplier. Debt offset can be made by delivery of goods:

To calculate the total debt of a counterparty, it is necessary to add up the sum of all expenses for this counterparty and subtract the sum of all receipts from the same counterparty, the easiest way to do this is using the UNITE ALL operator:

In the first request, we calculate the amount of expenses for each counterparty, in the second - the amount for which each of the counterparties delivered the goods to us. The amount in the second query is taken with a minus sign, so that when the resulting table is collapsed, it would be subtracted from the amount of shipment to this counterparty. As a result, we get a table of the form:

It's not exactly what we wanted, but it's close. To achieve the desired result, it remains to group by counterparty. To do this, the query must be placed in a temporary table (working with temporary tables is discussed in closed part of the course ) and select and group fields from it:

Requirements for merging queries

When combining two queries, the number of fields in them must be the same, if any of the queries lacks fields, then they must be added as constants. Let's look at the example above, let's say that the expense document also has a discount field, which reduces the counterparty's debt, but there are no discounts in the income document. How to be in this case? So:

It remains to subtract the discount and group.

The order is also important. The fields will be combined exactly in the order in which they are specified in the SELECT sections of both queries. With regard to the previous example, let's swap the fields discount and amount in the selection of receipts:

Good day, dear readers of the blog site. Today we will discuss in detail combining requests in 1C. You can also download for a typical configuration Business Management for Kazakhstan, edition 1.0.

The sentence is used to merge UNION / UNION. What's really going on combining query results, that is, each query collects data independently of each other, and then these results are combined into one. And only on the last query, other operations are performed, for example, ordering and calculation of totals.

Unlike when combining queries, the results are “vertically glued”, that is, the result rows of the first query go first, then the second.

Let's move on to practice:

SELECT Nomenclature, Quantity, Amount UNITE SELECT Nomenclature, Quantity, Amount FROM Document.Consumable.Goods

The result of this query is the following:

As a result, we will get two tables that will be connected to each other. It is difficult to see where the Incoming document ends and the Outgoing document begins. But we'll do something to figure it out:

SELECT Nomenclature, Quantity AS NumberPrikhod, Sum AS SumPrikhod, 0 AS QtyExpense, 0 AS SumExpense FROM Document.Incoming.Goods UNITE NumberPrikhod, 0 AS SumPrikhod, Number AS QtyExpense, Sum AS SumExpense FROM Document.Consumable.Goods

Nomenclature	NumberPrikhod	SumPrikhod	QtyExpense	SumExpense
A4 paper	25	14 500
Stickers 500l	8	4 880
The file is transparent	5	4 100
Office set	8	8 840
Office set			1	1 105
The file is transparent			1	820
Stickers 500l			1	610

CHOOSE
Nomenclature,
SUM(Number of Income) AS Number of Income,
SUM(SumIncome) AS SumIncome,
SUM(number of expenses) AS amount of expenses,
SUM(SumExpense) AS SumExpense
FROM
( SELECT Nomenclature, Quantity AS NumberPrikhod, Sum AS SumPrikhod, 0 AS QtyExpense, 0 AS SumExpense FROM Document.Incoming.Goods UNITE SELECT Nomenclature, 0 AS NumberPrikhod, 0 AS SumPrikhod, Number AS QtyExpense, Sum AS SumExpense FROM Document.Consumable.Goods ) AS NestedQuery
GROUP BY Nomenclature

Nomenclature	NumberPrikhod	SumPrikhod	QtyExpense	SumExpense
A4 paper	25	14 500
Stickers 500l	8	4 880	1	610
Office set	5	4 100	1	1 105
The file is transparent	8	8 840	1	820

As a result, we get a grouped table in which the data from nested subqueries is reduced to a clear form.
The number of nested queries should not exceed 255. We have used four subqueries here.

Merge query results. Combine query results How to combine queries into one 1s

FOR CHANGE

WHERE

SELECTION (CASE)

JOIN

LEFT/RIGHT JOIN

FULL CONNECTION

INNER JOIN

GROUP BY

RESULTS

HAVING

SORT BY

Other constructions of the 1C query language

Query language features

Query Builder

Query Console

Theoretical part of lesson number 4

Practical part of lesson number 4

Requirements for merging queries

Latest

It needs to be known