Programmer's notebook. Programmer's notebook 1c select allowed

The 1C 8 query language is an indispensable tool for a 1C programmer; it allows you to write more concise, simple, understandable code, and use fewer system resources when working with data. This article opens a series of lessons dedicated to the 1C 8 query language. In the first lesson we will look at the structure of the main operator of this language - CHOOSE. Using this operator, you can create selections from database tables. Selected table data can be sorted, conditions placed on it, linked and combined with data from other tables, grouped by various fields, and much more.

Query language 1C enterprise 8 - Operator structure SELECT

Let's look at the structure of the SELECT operator (optional parts of the operator are indicated in square brackets). The 1C query language provides a wide range of tools for creating data samples.

SELECT [ALLOWED] [DIFFERENT] [FIRST A] [Field1] [AS Alias1], [Field2] [AS Alias2], ... [FieldM] [AS AliasB] [PUT TemporaryTableName] [FROM Table1 AS AliasTableTable1 [[INNER JOIN ][LEFT JOIN][FULL JOIN] Table2 AS Alias Table2 [[INNER JOIN][LEFT JOIN][FULL JOIN] TableC AS Alias TablesC BY Expression1 [AND Expression2]...[AND ExpressionD]] ... ... BY Expression1 [And Expression2]...[And ExpressionE]] ... [TableF AS TableF Alias] ... ] [GROUP BY GroupingField1[,] ... [GroupingFieldG]] [WHERE Expression1 [AND Expression2] ... [AND ExpressionH]] [UNITE ALL...] [; ...] [INDEX BY Alias1 ... AliasB] [TOTALS [AggregationFunction(Field1)][,] [AggregationFunction(Field2)][,] ... [AggregationFunction(FieldI)] BY [GENERAL][,] [ GroupingField1][,] ... [GroupingFieldj]]

Keywords and blocks for working with fields

CHOOSE— a keyword indicating the beginning of the operator;
ALLOWED — indicates that the selection should include table records to which read access is allowed for a given user;
VARIOUS — indicates that the sample should include only different (across all fields) flows. In other words, duplicate rows will be excluded from the sample;
FIRST A — if you specify this keyword, then only the first A of the rows selected by the query will be included in the selection, where A is a natural number;
Field block— this block indicates the fields that need to be included in the selection. These fields will be selected columns. In the simplest case, the field looks like this: Table Alias.TableFieldName AS Field Alias
This way we indicate which table we are taking this field from. The 1C query language allows you to specify any aliases, but they should not be repeated in the same SELECT statement. A field can be more complex, consisting of various combinations of table fields, query language functions, and aggregate functions, but we won't cover those cases in this tutorial;

Keywords and blocks for working with tables

PUT TemporaryTableName- keyword PLACE is intended to create a temporary table with a specific name, which will be stored in RAM in this 1C 8 session until it ends or until the temporary table is destroyed. It should be noted that the names of temporary tables in one 1C 8 session should not be repeated;
Block of tables and relationships— the block indicates all the tables used in this request, as well as connections between them. The block begins with keyword FROM, followed by the name and alias of the first table. If this table is linked to other tables, then the links are indicated. The 1C query language contains the following set of connection types:
- INNER JOIN— a record from the left table will be included in the selection only if the connection condition is met, a record from the right table will be included in the selection only if the connection condition is met;
- LEFT CONNECTION— a record from the left table will be included in the selection in any case, a record from the right table will be included in the selection only if the connection condition is met;
- FULL CONNECTION— a record from the left table will be included in the selection first in any case, then only if the connection condition is met, a record from the right table will be included in the selection first in any case, then only if the connection condition is met. In this case, the resulting duplicate rows are excluded from the sample.
After the connection type, the name and alias of the second table are indicated. Next comes the keyword BY, followed by communication conditions connected with each other by logical operators AND, OR. Each expression in the condition must return a Boolean value (True, False). If the first table is connected to some tables other than the second, then the connection type is again indicated, and so on. Each of the tables participating in the connection, in turn, can be connected to other tables, this is shown in the query structure diagram. If the table is not related to the first one, then it is indicated without a connection type, then its connections may follow, and so on;

Keywords and data conversion blocks

Group block— this block is used to group table rows. Rows are combined into one if the values of the fields specified after the keyword GROUP BY turn out to be the same. In this case, all other fields are summed, averaged, maximized, or minimized using aggregate functions. Aggregate functions are used in a field block. Example: Maximum(TableAlias.TableFieldName) AS FieldAlias
Condition block- in this block after the keyword WHERE conditional expressions separated by logical operators are indicated AND, OR, in order for any of the selected rows to be included in the sample, it is necessary that all conditions in the aggregate have a value True.
COMBINE EVERYTHING— this keyword is used to combine queries (operators CHOOSE). The 1C query language allows you to combine several queries into one. In order for queries to be merged, they must have the same set of fields;
«;» - semicolons are used to separate statements that are independent of each other CHOOSE;
INDEX BY— the keyword is used to index the fields specified after it;
Summary block— used to build tree-like samples. For each of the grouping fields specified after the keyword BY, a separate row will be created in the selection. In this line, using aggregate functions, the total values of the fields specified after the keyword will be calculated RESULTS.

Do you want to continue learning the 1C 8 query language? Then read the next article.

17 rules for drawing up an optimal QUERY for 1C database data

To generate and execute queries to database tables in the 1C platform, a special programming language object is used Request. This object is created by calling the construct New Request. The query is convenient to use when you need to obtain a complex data sample, grouped and sorted as necessary. A classic example of using a query is to obtain a summary of the state of the accumulation register at a certain point in time. Also, the query mechanism makes it easy to obtain information in different time periods.

The request body is the instruction according to which the request must be executed. The request body describes:

infobase tables used as query data sources;
table fields that need to be processed in the query;
grouping rules;
sorting results;
etc.

The instructions are drawn up on special language– a query language and consists of separate parts – sections, sentences, keywords, functions, arithmetic and logical operators, comments, constants and parameters.

The query language of the 1C platform is very similar to the syntax of other SQL languages, but there are differences. The main advantages of the built-in query language are: dereferencing fields, the presence of virtual tables, convenient work with results, untyped fields in queries.

Recommendations for writing database queries in the 1C platform query language:

1) The request body may contain predefined configuration data, such as:

enum values;
predefined data:
reference books;
plans for types of characteristics;
charts of accounts;
plans for types of calculations;
empty links;
business process route point values.

Also, the request text can contain the values of system enumerations that can be assigned to fields in the database tables: Accumulation Movement Type, Account Type and Accounting Movement Type. In queries, predefined configuration data and system enumeration values are accessed using a literal of the VALUE function type. This literal improves the readability of the query and reduces the number of query parameters.

Example of using a literal MEANING:

WHERE City = VALUE(Directory.Cities.Moscow)
WHERE City = VALUE(Directory.Cities.EmptyLink)
WHEREProductType = VALUE(Enumeration.ProductTypes.Service)
WHEREMovementType = VALUE(MovementTypeAccumulation.Incoming)
WHERE RoutePoint = VALUE(BusinessProcess.BusinessProcess1.RoutePoint.Action1

2) Using the instructions AUTO ORDER the query can take a long time to complete, so if sorting is not required, then it is better not to use it at all. In most cases, it is best to use instruction sorting ORDER BY.

Auto-ordering works according to the following principles:

If the ORDER BY clause was specified in the request, then each link to the table found in this clause will be replaced by the fields by which the table is sorted by default (for reference books this is the code or name, for documents - the document date). If the field to be sorted refers to a hierarchical directory, then hierarchical sorting by this directory will be applied.
If the query does not have an ORDER BY clause, but does have a TOTAL clause, then the query result will be ordered by the fields present in the TOTAL clause after the key words software, in the same sequence and, if the totals were calculated by reference fields, then by the default sorting fields of the tables to which there were links.
If the query does not contain the ORDER BY and TOTAL clauses, but there is a GROUP BY clause, then the query result will be ordered by the fields present in the clause in the same sequence and, if the grouping was carried out by reference fields, then by default sorting fields tables that were referenced.
If the query does not contain ORDER BY, TOTAL, or GROUP BY clauses, the result will be ordered by the default sort fields for the tables from which the data is selected, in the order they appear in the query.
If the query contains a TOTAL clause, each total level is ordered separately.

3) To avoid repeating a query to the database when displaying the query result to the user (for example, building a query or displaying the query result using a spreadsheet document), it is useful to use the instruction INTRODUCTIONLINKS, which allows you to get a representation of a reference value. Example:

It is also possible to use instructions PERFORMANCE- designed to obtain a string representation of a value of an arbitrary type. The difference between these instructions is that in the first case, if the instructions pass a link, the result will be a string. In other cases, the result will be the value of the passed parameter. In the second case, the result of the instruction will always be a string!

4) If the request has a field with a composite type, then for such fields it becomes necessary to convert the field values to some a certain type using instructions EXPRESS, which will remove unnecessary tables from the left join to the field composite type data and speed up query execution. Example:

There is a register for the accumulation of Remaining Products, in which the Registrar field has a composite type. In the request, the Date and Document Number of Receipt of Goods are selected, while when accessing the document details through the Registrar field, many left connections of the accumulation register table with the tables of registrar documents do not occur.

Code 1C v 8.x SELECT
EXPRESS(Remaining Goods.Registrar AS Document.Receipt of Goods).Number AS Receipt Number,
EXPRESS(Remaining Goods.Registrar AS Document.Receipt of Goods).Date AS Receipt Date
FROM
Register of Accumulations. Remaining Goods AS Remaining Goods

If a type cast is considered not feasible, then the result of the type cast will be the value NULL.

5) Don’t forget about the instructions ALLOWED, which means the query will only select records that the current user has rights to. If given word If you do not specify, then if the request selects records for which the user does not have rights, the request will fail with an error.

6) If the query uses a join, and some parts of the join contain nested tables (a document with a tabular part), and some do not, it becomes necessary to supplement the selection list with fields - empty nested tables. This is done using a keyword EMPTYTABLE, after which the aliases of the fields that will make up the nested table are indicated in parentheses. Example:

Code 1C v 8.x // Select fields Number and Composition
// from the virtual table Document.Expenditure
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.*

7) To prevent duplicate lines from appearing in the query result, you should use the instruction VARIOUS, because it’s more visual and understandable, and the instructions GROUP BY used for grouping using aggregate functions. Ksati, when using aggregate functions, a suggestion GROUP BY may not be specified at all, but all query results will be grouped into one single line. Example:

Code 1C v 8.x // It is necessary to find out which counterparties
// goods were shipped for the period.
Select Various
Document.Invoice.Counterparty

8) Instructions GROUP BY allows you to access top-level fields, without grouping the results by these fields, if aggregate functions are applied to the fields of a nested table. Although the 1C help says that when grouping query results, aggregate functions must be specified in the list of selection fields, and in addition to aggregate functions in the list of selection fields, it is allowed to indicate only the fields by which grouping is carried out. Example:

Code 1C v 8.x SELECT
Receipt of Goods and Services. Goods. (SUM (Quantity), Nomenclature),
Receipt of Goods and Services. Link,
Receipt of Goods and Services. Counterparty
FROM
Document. Receipt of Goods and Services HOW Receipt of Goods and Services
GROUP BY
Receipt of Goods and Services. Goods. (Nomenclature)

9) Instructions ISNULL is intended to replace the value NULL to another value, but do not forget that the second parameter will be converted to the type of the first if the type of the first parameter is a string or a number.

10) When accessing the main table, you can conditionally access the data in the subordinate table. This feature is called dereferencing fields of a subordinate table.

Example (search for documents containing a specific product in the tabular section):

The advantage of this query over a query on the subordinate table Receipt.Goods is that if there are duplicates in documents, the query result will return only unique documents without using the DIFFERENT keyword.

11) Interesting option operator B is a check for the inclusion of an ordered set in the set of such sets (Field1, Field2, ..., FieldN) B (Field1, Field2, ..., FieldN).

Code 1C v 8.x SELECT
Counterparties.Link
WHERE
(Counterparties.Link, Products.Link) B
(SELECT Sales.Customer, Sales.Product
FROM RegisterAccumulation.Sales AS Sales)
FROM
Directory. Counterparties,
Directory.Products

12) Use virtual query tables whenever possible. When creating a query, the system provides a number of virtual tables as data sources - these are tables that are also the result of a query that the system generates at the time the corresponding section of code is executed.

The developer can independently obtain the same data that the system provides to him as virtual tables, but the algorithm for obtaining this data will not be optimized because:

All virtual tables are parameterized, i.e. the developer is given the opportunity to set some parameters that the system will use when generating a request to create a virtual table. Depending on what parameters of the virtual table are specified by the developer, the system can generate VARIOUS queries to obtain the same virtual table, and they will be optimized in terms of the passed parameters.

It is not always possible for a developer to gain access to the data that the system has access to.

13) In the client-server mode of operation, the function SUBSTRING() is implemented using the SUBSTRING() function of the corresponding SQL statement, passed to the SQL Server database server, which calculates the result type of the SUBSTRING() function from complex rules depending on the type and values of its parameters, as well as depending on the context in which it is used. In most cases, these rules have no effect on query execution, but there are times when the maximum result row length calculated by SQL Server is important to query execution. It is important to keep in mind that in some contexts when using the SUBSTRING() function, the maximum length of its result may be maximum length Strings have a limited length, which in SQL Server is 4000 characters. This may cause the query to crash unexpectedly:

Microsoft OLE DB Provider for SQL Server: Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.

HRESULT=80040E14, SQLSTATE=42000, native=8618

14) Use with caution OR in design WHERE, since using an OR condition can make the query significantly heavier. The problem can be solved by design COMBINE EVERYTHING. Example:

Code 1C v 8.x SELECT

FROM

WHERE
_Demo Contractors.Link =Link1
COMBINE EVERYTHING
CHOOSE
_Demo Contractors.NameFull
FROM
Directory._Demo Counterparties HOW TO _Demo Counterparties
WHERE
_Demo Contractors.Link = Link2

15) Condition NOT IN in design WHERE increases the query execution time, since this is a kind of NOT (OR1 OR2 ... ORn), so for large tables try to use LEFT JOIN with condition IS NULL. Example:

Code 1C v 8.x SELECT
_Demo Contractors.Link
FROM
Directory._Demo Counterparties HOW TO _Demo Counterparties
LEFT CONNECTION Document._Buyer Demo Order HOW TO _Buyer Demo Order
Software _Demo Counterparties. Link = _Demo Order of the Buyer. Counterparty
WHERE
_Demo Order of the Buyer. Counterparty IS NULL

16) When using Temporary tables You need to index the condition and join fields in these tables, BUT, when using indexes, the query can be even slower. Therefore, it is necessary to analyze each query with and without the use of an index, measure the speed of query execution and make a final decision.

If you place data in a temporary table that is initially indexed by some fields, then the temporary table will no longer have an index on these fields.

17) If you don't use Temporary Table Manager, then there is no need to explicitly delete the temporary table, it will be deleted after the batch query is completed, otherwise you should delete the temporary table using one of the following methods: command DESTROY in the request, call the method TemporaryTableManager.Close().

And in addition to the video from Evgeny Gilev: Typical errors when writing queries in 1C:

In this article we want to discuss everything with you 1C query language functions, and also query language constructs. What is the difference between function and design? The function is called with parentheses and possible parameters in them, and the construct is written without parentheses. Undoubtedly all structures and functions of the 1C query language make the data acquisition process flexible and multifunctional. These functions and constructs apply to query fields, and some also apply to conditions.

1C Query Language Functions

Because a clear description 1c query language functions is much less common than descriptions of structures, we decided to start looking at functions. Now let's look at each one separately, describing its purpose, syntax and example of use, so:

1. Function DATETIME- this function creates a constant field with the "Date" type.

Syntax: DATETIME(<Год>,<Месяц>,<День>,<Час>,<Минута>,<Секунда>)

Usage example:

2. DATE DIFFERENCE function- returns the difference between two dates in one of the dimensions (year, month, day, hour, minute, second). The measurement is passed as a parameter.

Syntax: DIFFERENCEDATE(<Дата1>, <Дата2>, <Тип>)

Usage example:

Query.Text = "SELECT | DIFFERENCEDATE(DATETIME(2015, 4, 17), DATETIME(2015, 2, 1), DAY) | AS Number of Days";

3. Function VALUE- sets a constant field with a predefined record from the database; you can also get an empty link of any type.

Syntax: VALUE(<Имя>)

Usage example:

Request.Text = "SELECT //predefined element | VALUE(Directory.Currencies.Dollar) AS Dollar, //empty link | VALUE(Document.Receipt of Goods and Services.EmptyLink) AS Receipt, //transfer value | VALUE(Transfer. Legal Individual. Individual) AS Individual, //predefined account VALUE(Chart of Accounts. Self-Accounting.Materials) AS Account_10" ;

4. SELECT function- we have before us an analogue of the IF construction, which is used in the code, only this one is used in 1C queries.

Syntax: CHOICE WHEN<Выражение>THEN<Выражение>OTHERWISE<Выражение>END

Usage example:

Request.Text = //if the amount is more than 7500, then there should be a discount of 300 rubles, //so if the condition is triggered then the function //returns Amount - 300 //otherwise the request will simply return Amount "SELECT | SELECT | WHEN TCReceipts.Amount > 7500 | THEN TCReceipts.Amount - 300 | ELSE TCReceipts.Amount | END AS AmountWithDiscount | FROM |

5. EXPRESS function- allows you to express a constant field with a specific type.

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

Another option is to use the EXPRESS function in fields mixed types where are these found? The simplest example is the “Registrar” for any register. So why might we need to qualify the type in the registrar? Let's consider the situation when we select the "Number" field from the registrar, from which table will the number be selected? The correct answer of all! Therefore, for our query to work quickly, we should specify an explicit type using the EXPRESS function

Usage example:

Query.Text = "SELECT | EXPRESS(Nomenclature.Comment AS Line(300)) AS Comment, | EXPRESS(Nomenclature.Sum AS Number(15,2)) AS Sum |FROM | Directory.Nomenclature AS Nomenclature";

6. ISNULL function(alternative spelling ISNULL) - if the field is of type NULL, then it is replaced with the second parameter of the function.

Syntax: ISNULL(<Поле>, <ПодставляемоеЗначение>)

Usage example:

Also note that it is advisable to ALWAYS replace the NULL type with some value, because comparison with type NULL always returns FALSE even if you compare NULL with NULL. Most often, NULL values are formed as a result of joining tables (all types of joins except internal ones).

Request.Text = //Select the entire item and its balances //if there is no balance in some item, then there will be a field //NULL which will be replaced with the value 0 "SELECT | No. Link, | ISNULL(ProductsInStoreRemains.InStockRemaining, 0) AS Remaining | FROM | Directory.Nomenclature AS No. | LEFT CONNECTION RegisterAccumulations.GoodsInWarehouses.Remainings AS GoodsInWarehousesRemainings | PO (GoodsInWarehousesRemainings.Nomenclature = No.Link)";

7. REPRESENTATION function- allows you to get a representation of the request field.

Syntax: PERFORMANCE(<НаименованиеПоля>)

Usage example:

Query.Text = "SELECT | REPRESENTATION(FreeRemainingRemains.Nomenclature) AS Nomenclature, | REPRESENTATION(FreeRemainingRemaining.Warehouse) AS Warehouse, | FreeRemainingRemaining.InStockRemaining |FROM |Accumulation Register.FreeRemaining.Remaining AS FreeRemainingRemaining";

Constructs in the 1C query language

We discussed with you above 1C query language functions, now it's time to consider constructs in the 1C query language, they are no less important and useful, let’s get started.

1. Construction LINK- is a logical operator for checking a reference type. Most often encountered when checking a field of a complex type against a specific type. Syntax: LINK<Имя таблицы>

Usage example:

Request.Text = //if the value type is logger document Entrance Hall, //then the request will return "Receipt of Goods", otherwise "Sales of Goods" "SELECT | SELECT | WHEN Remainings. Registrar LINK Document. Receipt of Goods and Services | THEN ""Receipt"" | ELSE ""Consumption"" | END AS Movement Type |FROM | Accumulation Register. Remaining Goods in Warehouses AS Remains" ;

2. Design BETWEEN- this operator checks whether the value is within the specified range.

Syntax: BETWEEN<Выражение>AND<Выражение>

Usage example:

3. Construction B and B HIERARCHY- check whether the value is in the transferred list (arrays, tables of values, etc. can be transferred as a list). The IN HIERARCHY operator allows you to view the hierarchy (an example of using the Chart of Accounts).

Syntax: IN(<СписокЗначений>), IN HIERARCHY(<СписокЗначений>)

Usage example:

4. Design SIMILAR- This function allows us to compare a string with a string pattern.

Syntax: LIKE "<ТекстШаблона>"

Row pattern options:

% - a sequence containing any number of arbitrary characters.

One arbitrary character.

[...] - any single character or sequence of characters listed inside square brackets. The enumeration can specify ranges, for example a-z, meaning an arbitrary character included in the range, including the ends of the range.

[^...] - any single character or sequence of characters listed inside square brackets except those listed after the negation sign.

Usage example:

Query.Text = //find the entire nomenclature that contains the root TABUR and begins //either with a small letter or with capital letters t "SELECT | Nomenclature. Link | FROM | Directory. Nomenclature AS Nomenclature | WHERE | Products. Name LIKE "" [Tt]abur%""" ;

5. Design ALLOWED- this operator allows you to select only those records from the database for which the caller has read permission. These rights are configured at the record level (RLS).

Syntax: ALLOWED is written after the keyword SELECT

Usage example:

Request.Text = "SELECT ALLOWED | Counterparties. Link | FROM | Directory. Counterparties AS Counterparties";

6. Design VARIOUS- allows you to select records in which there are no duplicate records.

Syntax: VARIOUS is written after the keyword SELECT

Usage example:

Request.Text = //selects records to which the reader has rights "SELECT VARIOUS | Counterparties.Name |FROM | Directory. Counterparties AS Counterparties" ;

Also, the VARIOUS construction can be used with the ALLOWED operator and other operators.

Usage example:

Request.Text = //selects various records to which the reader has rights "SELECT ALLOWED VARIOUS | Counterparties.Name |FROM | Directory. Counterparties AS Counterparties";

7. Design FIRST- selects the number of records specified in the parameter from the query result.

Syntax: FIRST<число>

Usage example:

Request.Text = //select the first 4 CCD numbers from the directory "SELECT FIRST 4 | CCD Numbers. Link | FROM | Directory. CCD Numbers AS CCD Numbers";

8. Design FOR CHANGE- allows you to lock a table, works only in transactions (relevant only for automatic locks).

Syntax: TO CHANGE<НаименованиеТаблицы>

Usage example:

9. Design ORDER BY- organizes data by a specific field. If the field is a link, then when setting the flag AUTO ORDER Sorting will occur by link representation; if the flag is turned off, then links are sorted by the seniority of the link address in memory.

Syntax: ORDER BY<НаименованиеПоля>AUTO ORDER

Usage example:

10. Design GROUP BY- used to group query strings by specific fields. Numeric fields must be used with any aggregate function.

Syntax: GROUP BY<НаименованиеПоля1>, .... , <НаименованиеПоляN>

Usage example:

11. Design HAVING- allows you to apply an aggregate function to a data selection condition, similar to the WHERE construction.

Syntax: HAVING<агрегатная функция с условием>

Usage example:

12. Construction INDEX BY- used for indexing the query field. A query with indexing takes longer to complete, but speeds up searching through indexed fields. Can only be used in virtual tables.

Syntax: INDEX BY<Поле1, ... , ПолеN>

Usage example:

13. Design WHERE- allows you to impose a condition on any selection fields. The result will include only records that satisfy the condition.

Syntax: WHERE<Условие1 ОператорЛогСоединения УсловиеN>

Usage example:

Query.Text = //all records with CompensationRemaining are selected<>0 and //AmountForCalcCompRemaining > 100 "SELECT | CompensationRPORemains.Counterparty, |CompensationRPORemains.Child, | CompensationRPORemains.CompensationRemaining, | CompensationRPORemains.AmountForCalcCompRemains |Place DataTz |FROM |Accumulation Register.CompensationRP.Remains AS CompensationRPRemains |WHERE |CompensationRPORemaining.CompensationRemaining<>0 | And CompensationRPORemains.AmountForCalcCompRemaining> 100" ;

14. Design RESULTS... GENERAL- used to calculate totals; the design specifies the fields by which totals will be calculated and aggregate functions applied to the total fields. When using totals for each field following the TOTAL construction, data is grouped. There is an optional GENERAL construct; its use also provides additional grouping. You will see an example of the request result below.

Syntax: RESULTS<АгрегатнаяФункция1, ... , АгрегатнаяФункцияN>BY<ОБЩИЕ> <Поле1, ... , ПолеN>

Usage example:

The figure outlines the groupings that were formed during the execution of the request, the top one refers to the GENERAL section, and the second to the Counterparty AgreementAgreement Type field.

). Using this keyword allows you to avoid errors when retrieving records for which the user does not have rights.

Problem: In some cases, the result of data access restrictions in 1C 8.3 may depend on the DBMS query plan. This article examines possible situations and gives recommendations on how to avoid this.

The problem of possible dependence of the result of data access restrictions on the DBMS query plan can arise when executing a database query without a keyword ALLOWED, if the current user has data access restrictions and the request contains one or more comparisons of the form:

<Выражение над полями>(IN|NOT IN) (<Вложенный запрос>)
(<Выражение над полями 1>, …, <Выражение над полями N>) (IN|NOT IN) (<Вложенный запрос>)

If in this case < > (a query within a query) uses database tables on which access restrictions are imposed, it is possible that on some DBMSs the query will be executed successfully, while on others a message will be issued provided that the data in the information bases is completely identical.

Get 267 video lessons on 1C for free:

Reason for differences

The possible difference in behavior is due to the implementation of data access restrictions without a keyword ALLOWED in 1C Enterprise 8.3.

Query without keyword ALLOWED will be executed successfully only if during its execution no access to prohibited data occurs. To do this, a special signal field is added, which takes the value True for those records in the formation of which only permitted data participated, and the value Lie for all other entries. If at least one sample record contains the value Lie in the signal field, the request execution ends abnormally.

The same signal field is added to the results of queries nested in the comparison IN/NOT IN. Moreover, checking the value of the signal column in this case is performed using DBMS tools. Thus, if during the execution of a nested query, prohibited data was accessed, then the query should fail with an error The user does not have sufficient rights to perform an operation on the database.

However, when building a query plan, the DBMS may not receive the full sample <Вложенным запросом> , and receive only those records that are actually necessary to check the condition IN/NOT IN. In this case, the request may succeed even if the <Вложенного запроса> as an independent request, access to prohibited data could occur.

Let's look at a simple example. Let on the table Directory.Individuals restrictions on access to data are imposed. In this case the request:

Table.Individual AS Individual

will be executed with an error due to an attempt to access prohibited data. If this query is involved in comparison, for example:

Table.Individual AS Individual

Directory.Individuals AS Table)

then, depending on the selected DBMS query plan, the query can be executed either successfully or with an error. This request behavior is not an error because forbidden data may or may not be accessed during the execution of the request. To obtain a more predictable result, it is necessary to construct a query in such a way that the nested query is guaranteed not to access obviously unnecessary data. In particular, if the previous query is rewritten like this:

Agreement for Performance of Work with an Individual.Employee.Individual

Document. Agreement for Performing Work with a Physical Person AS an Agreement for Performing Work with a Physical Person

Agreement for Performance of Work with Individual.Employee.Individual B (

Table.Individual AS Individual

Directory.Individuals AS Table