How to write expressions in 1C queries. Features of working with the View field and the View() function of the query language. Functions type and typevalue

The 1C query language is one of the main differences between versions 7.7 and 8. One of the most important points in learning 1C programming is the query language. In 1C 8.3, queries are the most powerful and effective tool for obtaining 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
for 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 FOR 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 a text value in the “Item Type” field - “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 this example, we 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 attribute type if it is of a 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 applications, 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 own 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 differs from the full one 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.

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!

The query language is one of the fundamental mechanisms of 1C 8.3 for developers. Using queries, you can quickly retrieve 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 (referring one or more points to object details);
working with results is very convenient;
the ability to create virtual tables;
the request can be written in both English and Russian;
ability to block data to avoid deadlocks.

Disadvantages of the query language in 1C:

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

Let's take a look at our mini tutorial on the basic constructs of the 1C query language.

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

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

CHOOSE
Nomenclature. Name AS Name of Nomenclature
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 that need 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 documents that are recent by date.
ALLOWED. This design allows you to select from the database only those records that are available to the current user. Based on the use of this keyword, the user will receive an error message when attempting to query records that they do not have access to.

These keywords can be used together or separately.

FOR CHANGE

This proposal blocks data to prevent mutual conflicts. Locked data will not be read from another connection until the transaction ends. In this clause, you can specify specific tables that need to be locked. Otherwise, everyone will be blocked. The design is relevant only for the automatic locking mode.

Most often, the “FOR CHANGE” clause is used when receiving balances. After all, when several users work in the program simultaneously, while one receives balances, another can change them. In this case, the resulting remainder 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 be forced to wait.

CHOOSE
Mutual settlements. Employee,
Mutual settlements. Amount of mutual settlements Balance
FROM
Register of Accumulations. Mutual settlements with employees. Balances AS Mutual settlements
FOR CHANGE

WHERE

The design is necessary to impose some kind of selection on the uploaded data. In some cases of obtaining data from registers, it is more reasonable to specify selection conditions in the parameters of virtual tables. When using "WHERE", all records are retrieved first, and only then selection is applied, which significantly slows down the query.

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

SELECTION (CASE)

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

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

CHOOSE
AdmissionT&U.Link,
CHOICE
WHEN AdmissionT&U.Performed
THEN “The document has been passed!”
ELSE “The document was not posted...”
END AS AdditionalField
FROM
Document. Receipt of Goods and Services HOW Receipt T&C

JOIN

Joins link two tables based on a specific relationship condition.

LEFT/RIGHT CONNECTION

The essence of the LEFT join is that the first specified table is taken in its entirety and the second one is linked to it according to the connection condition. 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, it is necessary to obtain item items from the “Receipt of goods and services” documents and prices from the information register “Item prices”. In this case, if the price for 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&U.Nomenclature,
Prices.Price
FROM
Document. Receipt of Goods and Services. Goods HOW Receipt and Specifications
INTERNAL JOIN RegisterInformation.PricesNomenclature.SliceLast AS Prices
Software Receipt&U.Nomenclature = Prices.Nomenclature

IN THE RIGHT everything is exactly the opposite.

FULL CONNECTION

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

When using a full connection in the previous example, all item items from the “Receipt of Goods and Services” document and all the latest prices from the “Item Prices” register will be selected. The values of not found records in both the first and second tables will be equal to 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, the query will not display it at all. As a result, only those item items from the document “Receipt of goods and services” will be selected for which there are records in the information register “Item prices”, 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 characteristic (grouping fields). Grouping fields can only be displayed using aggregate functions.

The result of the following query will be a list of product types with maximum prices for them.

CHOOSE
,
MAX(Price.Price) AS Price
FROM

GROUP BY
Prices.Nomenclature.Type of Nomenclature

RESULTS

Unlike grouping, when using totals, all records are displayed and total rows are 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 summarizing results, it is not necessary to use aggregate functions.

Let's look at 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
Register of Information. Prices of Nomenclature. Snapshot of the Latest AS Prices
RESULTS
MAXIMUM(Price)
BY
TypeNomenclature

HAVING

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

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

CHOOSE

MAX(Price.Price) AS Price
FROM
Register of Information. Prices of Nomenclature. Snapshot of the Latest AS Prices
GROUP BY
Prices.Nomenclature.Type of Nomenclature
HAVING
MAXIMUM(Prices.Price) > 1000

SORT BY

The ORDER BY operator sorts the result of a query. To ensure that records are displayed 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 HOW Employees
SORT BY
Name
AUTO ORDER

Other 1C query language constructs

COMBINE– results of two queries into one.
COMBINE EVERYTHING– similar to COMBINE, but without grouping identical rows.
EMPTY TABLE– sometimes used when joining queries to specify an empty nested table.
PLACE– 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 to a specified number of characters.
YEAR...SECOND allow you to get the selected value of a numeric type. The input parameter is the date.
BEGINNING OF PERIOD and END OF PERIOD used when working with dates. The type of period (DAY, MONTH, YEAR, etc.) is indicated as an additional parameter.
ADDKDATE allows you to add or subtract a specified time of a certain type from a date (SECOND, MINUTE, DAY, etc.).
DIFFERENCEDATE determines the difference between two dates, indicating the type of output value (DAY, YEAR, MONTH, etc.).
ISNULL replaces the missing value with the specified expression.
REPRESENTATION and REPRESENTATIONLINKS get a string representation of the specified field. Apply to any values and only reference values, respectively.
TYPE, TYPE VALUES are used to determine the type of the input parameter.
LINK is a logical comparison operator for the attribute value type.
EXPRESS used to convert a 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 indicate predefined values - directories, enumerations, plans for types of characteristics. Usage example: " Where Legal Individual = Value(Enumeration. Legal Individual. 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 that need to be selected and their sources.
“Connections” - describes the conditions for the CONNECTION structure.
“Grouping”—contains a description of grouping structures and summed fields based on them.
“Conditions” - is responsible for selecting data in the request.
“Advanced”—additional query parameters, such as keywords for the “SELECT” command, etc.
“Joins/Aliases” - the possibilities of joining tables are indicated and aliases are specified (the “HOW” construct).
“Order” is responsible for sorting the result of queries.
“Totals” - similar to the “Grouping” tab, but used for the “TOTALS” construct.

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.

Request Console

To quickly view the result of a query in Enterprise mode, or debug complex queries, use . It contains the text of the request, sets the parameters, and displays the result.

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

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. Similarly, 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 displaying this field, 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 of employees of the form (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, Nomen klatura

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.Register.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 UnallocatedPaymentsRemains.Customer, UnallocatedPaymentsRemains.AmountRemaining 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 Request.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 practically ready-made data for most applied tasks. (Slice of the First, Slice of the Last, Remains, Turnovers, Remains and Turnovers) The key word here is 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, assembles, 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 toRemainders AS Remainders BY 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 connect the tables “tClientPayment” and “tPayment” using the fields “Payment” and “Document” with an internal join. 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_ScheduleTurnover.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" K 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 vtUsers.Code FROM vtUsers) AND Individuals.Name IN (SELECT vtUsers.Code FROM vtUsers) AND Individuals.BirthDate IN (SELECT vtUsers.DateBirth FROM tvUsers)

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 how 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

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

The article provides useful techniques when working with 1C v.8.2 queries, as well as information that is not so well known about the query language. I do not strive to give a complete description of the query language, but I want to dwell only on some points that may be useful for someone.

So, let's begin. A request is a special object in 1C 8.2, which is used to generate and execute queries against database tables in the system. To execute a query, you need to compose a query text that describes which tables will be used as query data sources, which fields need to be selected, which sortings and groupings to apply, etc. You can read more about queries in the book "1C 8.2 Developer's Guide". The 1C 8.2 query language is very similar in syntax to other SQL database query languages, but there are also differences. Among the main advantages of the built-in query language, it is worth noting the dereference of fields, the presence of virtual tables, convenient work with totals, and untyped fields in queries. The disadvantages are that you cannot use a query as an output field, you cannot use stored procedures, and you cannot convert a string to a number.

I will provide information and recommendations on the query language point by point:
1. To increase the readability of the request and reduce the number of request parameters, you can use a literal to access predefined configuration data in the request VALUE (VALUE REPRESENTATION). As a representation of values, the values of enumerations, predefined data of directories, plans of calculation types, plans of types of characteristics, charts of accounts, empty links, values of route points, values of system transfers (for example, Accumulation Movement Type, Account Type) can be used.
Examples:

WHERE City = VALUE(Directory.Cities.Moscow)
WHERE City = VALUE(Directory.Cities.EmptyLink)
WHEREProductType = VALUE(Enumeration.ProductTypes.Service)
WHEREMovementType = VALUE(MovementTypeAccumulation.Incoming)
WHERE is Route Point =
VALUE(BusinessProcess.Agreement.RoutePoint.Agreement)

The expression in parentheses always begins with a singular word (Directory, Enumeration, etc.) that matches the type of the predefined value.

2.Auto-ordering in a query can greatly slow down the process. If sorting is not needed, it is better not to use it at all. In many cases it is more efficient to write sorting using a keyword SORT BY.

3. You need to make sure that when using aliases, an ambiguous field does not appear. Otherwise, the system will not understand which object needs to be accessed.
Example of a request with an ambiguous field:
CHOOSE
Nomenclature.Link,
Remaining GoodsRemaining.QuantityRemaining
FROM
Directory.Nomenclature AS Nomenclature
LEFT CONNECTION Register Accumulations. Remaining Goods. Remaining AS Remaining Goods Remaining
Software Remaining ProductsRemaining.Nomenclature = Nomenclature.Link
It is necessary to correct the table alias, for example, like this: “Directory.Nomenclature AS Nomenclature1”, and “Nomenclature.Link” should be corrected accordingly to “Nomenclature1.Link”.

4.Sometimes it is useful to get a representation of reference fields using a keyword PERFORMANCE along with a link so that there is no repeated access to the database. This is useful when displaying the result of a query in a table.
Example:
CHOOSE
REPRESENTATION(Document.Counterparty) AS Recipient,
PRESENTATION(Document.Base)
FROM
Document.Invoice AS Document

5.Use in a request EXPRESS(Field AS Type) allows you to remove unnecessary tables from a connection with a field of a complex data type. Thereby speeding up the execution of the request.
Example (registrar is a field with a composite type for the physical table of the register of accumulation of Remaining Goods, in the request the Date and Number of documents Receipt of Goods are selected, while when accessing the details of the document Date and Number through the Registrar, there is no multiple connection of the register table with tables of documents that are registrars for the Remaining of Goods register ):
SELECT VARIOUS[b] EXPRESS(Remaining Goods.Registrar AS Document.Receipt of Goods).Number AS RECEIPT NUMBER,
[b] EXPRESS(Remaining Goods.Registrar AS Document.Receipt of Goods).Date AS RECEIPT DATE
[b]FROM Register of Accumulations. Remaining Goods AS Remaining Goods WHERE (EXPRESS(Remaining Goods.Registrar AS Document.Receipt of Goods) IS NOT NULL)

6.When in the 1C configuration there are users who have limited rights to certain configuration objects, the keyword must be used in the request to such objects ALLOWED so that the request is executed without error (Select Allowed...)

7.When merging tables containing nested tables (for example, a Document with a tabular part), the keyword can be useful EMPTYTABLE when, for example, one of the documents does not have a tabular part.
Example:
SELECT Link.Number, EMPTY TABLE.(No., Item, Quantity) AS Composition

COMBINE EVERYTHING
SELECT Link.Number, Composition.(LineNumber, Nomenclature, Quantity)
FROM Document.Invoice

8.When working with joins of tables containing one row each, it may be necessary to merge the rows of the tables (in both tables there is no field by which they could be joined). This can be achieved by using the construction " FULL CONNECTION Table By TRUE" If the tables have more than one row, the result will be a number of rows equal to the product of the number of rows of both tables. If there are O rows in one table, then the number of rows in the resulting table will be equal to the number of rows in the second table. Also, to connect such tables, you can use the Cartesian product of tables, in which all combinations of rows from both tables will appear in the resulting table. We must remember that if there are 0 rows in one of the tables, then the Cartesian product will be 0, so a full join will be better. In general, instead of a complete connection BY TRUE You can use any other type of join, but in this case it is also possible that the resulting table will have 0 rows, even if one of the tables has a non-zero number of rows. In the case of a full join, this situation will only occur in one case, if the number of rows in both tables is 0. If you know that there is exactly at least one row in the table, then you can use LEFT CONNECTION with another table with condition BY TRUE.
Example (admittedly contrived, for Full Join):
CHOOSE
First 1
Gender.Link,
K. Counterparty
FROM
Enumeration. Gender AS Gender
FULL CONNECTION (Select First 1 D. Counterparty FROM Document. Sales of Goods HOW D Arrange By D. Moment of Time) HOW TO
ON(TRUE)

9. In order to get unique records for a certain field, it is more correct to use a keyword instead of grouping VARIOUS in the request, because this construction is much clearer and the keyword GROUP BY has a wider application and is often used if it is additionally necessary to calculate aggregate functions by groupings. In some cases it is necessary to output a limited number of lines. To do this, you should specify the keyword in the request description FIRST and after it - the required number of lines.
Example for FIRST:
Select First 5
Directory.Nomenclature.Name,
Directory.Nomenclature.PurchasingPrice
Sort by
Directory.Nomenclature.PurchasePrice Descending
Example for VARIOUS:
Select Various
Document.Consumable.Counterparty

10.Aggregation functions in a query can be used without a keyword GROUP. In this case, all results will be grouped into one line.
Example:
Choose
Amount(Invoice.Amount) As Amount
From
Document.Invoice.Composition As Invoice

11.In queries in the selection fields, you can freely access the details of the selection fields. This feature is called select field dereferencing. If the data source is a nested table (tabular part of the document), then in the selection fields you can also access the fields of the main table (for example, through the Link field, access the field of the main table Account)
Example:
CHOOSE[b] Receipt of Goods and Services Goods. Quantity AS Quantity, Receipt of Goods and ServicesGoods.Link.Counterparty FROM WHERE
There is one peculiarity of using field dereferencing if there are groupings in the request. In any queries with groupings in the lists of query fields, you can freely access the details of the grouping fields.
Example:
CHOOSE
Receipt of Goods and Services Goods. Nomenclature,
Receipt of Goods and Services Goods. Nomenclature. Code,
SUM (Receipt of Goods and Services Goods. Quantity) AS Quantity,
Receipt of Goods and ServicesGoods.Link.Counterparty,
Receipt of Goods and ServicesGoods.Link.Date
FROM
Document. Receipt of Goods and Services. Goods HOW Receipt of Goods and Services Goods
WHERE
Receipt of Goods and ServicesGoods.Link = &Link
GROUP BY
Receipt of Goods and Services Goods. Nomenclature,
Receipt of Goods and ServicesGoods.Link
The 1C help says that if there is grouping, only grouping fields and aggregate functions for the selection fields can participate in the query selection fields. There is one exceptional case when aggregate functions are applied to fields of a nested table. In this case, in the list of selection fields, it is possible to access the fields of the top-level table, without grouping the results by these fields.
Example:
CHOOSE
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)

12. Sometimes, instead of specifying any field in the grouping, it is useful to include the following parameter in the query selection fields:
CHOOSE DocProducts.Nomenclature, &Counterparty, &Period, SUM(DocProducts.Quantity * DocProducts.K) AS Quantity, SUM(DocProducts.Amount) AS Amount FROM Document.Admission.Products AS DocProducts WHERE DocProducts.Link = &Link
GROUP BY DocProducts.Nomenclature
And then set the parameter in the request body:
Request.SetParameter("&Account", SelectAccount);
Query.SetParameter("&Period", Date);

13. In universal queries, parameters can be used in the description of query data sources, in the conditions WHERE, in the conditions of joining tables and parameters of virtual tables. There are two techniques for creating generic queries:
A) using the string concatenation mechanism, adding variables to the request text;
Example1:

OrderingType = ?(SOME VARIABLE,"","DESC");
Query.Text = "Select... Arrange BY Field1 " + OrderType + "...";
Example2:
Query.Text = "Select Field1...";

If SOME VARIABLE = 1 Then
Request.Text = Request.Text + ",Field2 ...";
endIf;
B) use parameters in various parts of the request (for example, in the data sources section of the request), and then the built-in language method - STREPLACE(). When designing universal queries, it is useful to access the property of objects METADATA(), with which you can determine the name of the table for some link (for example, for a document it will be something like this - Link . METADATA().NAME), passed through a parameter to some universal procedure.
Example:
Choose
DocTch.Nomenclature,
...
FROM
&Some DocTC AS DocTC
And then set the parameter in the request body
Request.Text = StrReplace(Request.Text, "&SomeDocTCH", "Document."+Link.Metadata().Name+".Products");

Parameters can be used in query conditions to enable an optional condition &Parameter OR NOT SomeProperty:
Request.SetParameter(“&Parameter”, “Counterparty.Name=””Ivanov”””);
Using a literal TRUE you can remove certain filters in the request
Request.SetParameter("&Parameter", True);

14.Very useful in the query designer is the table context menu command - " Rename table...", with which you can come up with some generalized name for the data source. To create queries for tables of the same type, similar in structure, it can be useful for the second table to copy the query text of the first table, go to the query designer window and select the item in the context menu of the table - Replace table... and select the second table.

15.When working with the creation of nested queries in the sections of conditions or parameters of virtual tables of the query designer, the technique of highlighting a space in brackets is used, then the “Query Designer” item appears in the context menu, and when editing a nested query, the entire query in brackets is highlighted in the condition.
Example of a nested query:
Product B (Select Product...)

16. When designing ACS reports in queries to balance registers, it is more convenient and correct to use the expression as the Period parameter AddToDate(EndPeriod(Period,DAY),SECOND,1), since virtual balances are obtained at the beginning of the period, not including the last second. The +1 second technique cannot be used with documents: according to the new method of posting documents, register balances must be received for the Period specified by the Boundary object with the moment in time of the document including (and not on the date of the document +1 second!), and according to the old method of posting - at the point in time of the document (and not at the date of the document!). When analyzing turnover or data for a period, it is convenient to add a parameter with the type StandardPeriod(in this case there is no need to provide the last date of the interval at the end of the day). For the standard field “Start of Period”, in the “Expression” field you must enter "&Period.Start Date" And for the standard field “End of Period” in the “Expression” field write “ &Period.End Date". A lot of useful information on the query language can be found not in the syntax assistant, but in the full help of the 1C 8.2 configurator (F1 button)

17.Query function IsNull(it’s more convenient to write the English version IsNull) is typically used to get rid of Null values for numeric query fields. In some cases, for example, a complete join of two tables, the function IsNull (Parameter1,Parameter2) can successfully replace the design CHOICE WHEN... THEN..ELSE....END, when for any field NULL values can be both in the first table and in the second (this construction allows you to obtain a non-Null value for the field). But we must remember that, unlike the conditional operator CHOICE function IsNull converts the type of the second argument to the type of the first argument, which must be taken into account if the argument types are different!
Example:
IsNull(Reg.Remaining,0)
IsNull(Doc.Product,Doc1.Item)

18. At the conditional construction CHOICE There is an alternative syntax for the simple case of testing equality to a certain value, but it is, however, undocumented:
Choice Expression When 1 Then “High” When 2 Then “Middle” Else “Low” End

19. NULL value checking operator Yes Null(We can recommend using the English version Is Null). This construction appeared because any operation comparing two values, at least one of which is Null, is always false. Write Where Name = Null wrong. The form of negation of this operator is also interesting No Null- wrong, but right Yes Not Null or shape Not (Field1 Is Null)- this is a significant difference from all operators used in conjunction with the He operator.

20. Sometimes the operator form is useful IN to check for a match with one of the listed values.
Example:
...Where is the Product.Name B ("Household Appliances","Computers")
For reference books, the operator form may be useful IN hierarchy membership checks.
Example:
...Where is the Nomenclature IN THE HIERARCHY (&Group)
Operator IN often used to check whether a value is included in the result of a subquery.
Example:
...Where Nomenclature.Link B (Select Nomenclature.Link...).
In a subquery, you can access the outer query fields in a condition.
Example:
// Select the names of products that were present
// in invoices
CHOOSE
Products.Name
FROM
Directory.Nomenclature HOW Products
WHERE
Products.Link B
(CHOOSE
InvoiceComposition.Nomenclature
FROM
Document.Invoice.Composition AS InvoiceComposition
WHERE
InvoiceContent.Nomenclature = Products.Link)
Operation IN can be used with arrays, lists of values, tables of values, nested queries. In this case, it is possible to reduce the conditions
Syntax for a subquery
(expression1, expression2,...,expressionN) In (Select expression1, expression2,...,expressionN...)
Syntax for value table
(expression1, expression2,...,expressionN) In (&TK), where the first N columns are used in the table of TK values

20. There is a joke on the Internet about how the query designer always does LEFT joining tables (and swapping them), no matter how we specify RIGHT:
1C: The enterprise loves “to the left”.

21. It is convenient to debug complex queries in the query console. There are many of them on the Internet. After debugging the query, you can copy it and in the query designer there is a wonderful button “ Request", where you can paste it in the same form and save it (previously it was only possible to copy it in the configurator and format the request using the line break character). In the window that opens when you click the “Query” button, you can edit the query and view the execution result, which is quite convenient.

22.When designing ACS reports, you need to remember that if you need to provide filtering by a certain field, it is not necessary to add a parameter to the request text. The Query Builder has a tab " Data composition", where you can add parameters to the conditions. In addition, at the ACS report level there is a conditions tab where you can add arbitrary conditions and save them in quick settings. In this case, the conditions will be universal (equality, inequality, belonging, inclusion in the list, etc.).

23. When working with documents, you may need to add sorting by a virtual table field MOMENT OF TIME, but bad luck - in nested queries, sorting by this field does not work correctly. Dancing with tambourines helps: sorting by virtual field MOMENT OF TIME is replaced by two sortings: by date and by link. You can also solve the problem through a temporary table by moving the nested query into a separate query. For many releases this feature or bug has not been fixed.
An example of a malfunctioning request that receives the last posted document for the specified counterparty (or rather, the tabular part of the document):

CHOOSE
ConsumableProducts.Link,
Consumables.Line Number,
ConsumableProducts.Product,
Consumable Items.Quantity,
Consumable Products. Price,
ConsumableItems.Amount
FROM

WHERE
Consumable Products. Link B
(SELECT TOP 1
D. Link
FROM
Document.Consumable AS D
WHERE
D. Link. Conducted

ORDER BY D. Link. Moment of Time DESCENDING)

Possible solutions:
A) Replace with SORT BY on
ORDER BY D.Date DESC.
ORDER BY D.Link DESCENDING

B) You can move the nested query to a temporary table:
SELECT TOP 1
D. Link
PUT TZLink
FROM
Document.Consumable AS D
WHERE
D. Link. Conducted
And D.Counterparty = &Counterparty

SORT BY
D. Link. Moment of Time DESCENDING
;

////////////////////////////////////////////////////////////////////////////////
CHOOSE
ConsumableProducts.Link,
Consumables.Line Number,
ConsumableProducts.Product,
Consumable Items.Quantity,
Consumable Products. Price,
ConsumableItems.Amount
FROM
Document.Consumables.Goods AS ConsumablesGoods
WHERE
Consumable Products. Link B
(CHOOSE
T.Link
FROM
TZLink AS T)
C) You can refer to the main table of the document, and only then to the tabular part
SELECT TOP 1
Consumable.Link,
Consumables.Goods.(
Link,
LineNumber,
Product,
Quantity,
Price,
Sum
)
FROM
Document.Consumables AS Consumables
WHERE
Expense.Counterparty = &Counterparty
And Consumables.Conducted

SORT BY
Consumable.Moment of Time DECREASE

24. When accessing the main table of a document (directory), you can also access the data in the subordinate table (tabular part). This opportunity is called dereferencing table fields. An example of a task is the task of searching for documents containing a specific product in the tabular section.
Example:
Select Receipt.Link FROM Document.Receipt Where Receipt.Goods.Nomenclature = &Nomenclature.

The advantage of this query over a query on the nested table Receipt.Goods is that if there are duplicates in documents, the query result will return only unique documents without using the keyword VARIOUS.
Compare:
Select Various Products.Link FROM Document.Receipt.Products as Products Where Products.Nomenclature = &Nomenclature.
This is probably all there is to it. It is clear that there are still many questions in the query language that I have not covered. To write this article, I used the information I received after completing the basic course 1C 8.2 spec8.ru, as well as from the book “1C 8.2 Developer’s Guide” and the Internet.
Thanks to all!

In this article we want to discuss everything with you 1C query language functions, and 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 DATE TIME- this function creates a constant field with the "Date" type.

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

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-supporting. 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 return simply Amount "SELECT | SELECT | WHEN TCReceipts.Amount > 7500 | THEN TCReceipts.Amount - 300 | ELSE TCReceipts.Amount | END AS AmountWithDiscount |FROM | Document.Receipt of GoodsServices.Goods AS TCReceipts";

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

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

Is there another option for using the EXPRESS function in fields of mixed types, where do they occur? 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).

Query.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(ProductsInStockRemains.InStockRemaining, 0) AS Remainder | FROM | Directory.Nomenclature AS No. | LEFT CONNECTION Register Accumulations. GoodsInWarehouses. Remainings AS GoodsInWarehousesRemains | ON (GoodsInWarehousesRemains. 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 recorder value type is document Receipt, //then the query 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 Type of Movement | FROM | Accumulation Register. Remaining Products 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:

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: FOR 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: SORT 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.

Also on topic

What is earth gravity in simple words

RealProJoe - the whole truth about the history of human development What is the name of the 10th planet of the solar system

Zodiac signs have shifted by a month What does it mean that zodiac signs have shifted

General characteristics of the terrestrial planets

What to do at the cemetery on Parents' Day Visiting the cemetery on Parents' Saturday