Autoordering in a query 1s. Little tricks of big requests
Attention! Here is a trial version of the lesson, the materials of which may not be complete.
Login as a student
Sign in as a student to access school content
1C 8.3 query language for beginner programmers: ordering
Let's write a query that gets from a table Directory.Food food code and name:
SELECT Code, Name FROM Directory. Food |
As always, run this query on your computer.
With a high degree of probability, you will get the following result:
You may be surprised, but with this writing of the query, no one guarantees us exactly this order of issuing records in the table. In the case of using the client-server mode of operation on various DBMS, the result could be like this:
And ..., well, in general, you understand that if we do not specify the sorting (ordering) order of the query result, then this same order can be absolutely anything.
Therefore, it is good practice when writing queries to order query results even when we do not explicitly require this.
ORDER BY section
The fields by which you want to sort the query are listed in the section SORT BY separated by commas:
The ordering field name can be followed by one of two keywords:
- WHO - ascending ordering.
- DESC - descending ordering.
If you do not specify any of these words, it is considered that the sort is in ascending order.
Armed with knowledge, let's order the result of our query in descending field The code:
Now let's arrange the following table
so that sorting by field is done first Taste ascending, and then (among rows with the same field value Taste) was sorted by field Color descending:
CHOOSE Taste, Color FROM DIRECTORY. Food ORDER BY Taste. Name VOZR, Color. Name DESC |
Separately, I draw your attention to the fact that we specified sorting not by the fields themselves Taste and Color, and by their string props Name. You are reading a trial version of the lesson, full lessons are located.
This is due to the fact that sorting is possible only by fields that have one of the following types: Line, Number, the date.
And the fields Taste and Color are references to elements of directories Taste and Color, sorting by which does not make sense (in this case, sorting will be carried out by the internal identifier of the link). But you can sort by one of the attributes of these elements. The most suitable in our case would be a string attribute Name.
Ability to auto-order
The AUTOORDER keyword allows you to enable automatic generation of fields for ordering query results.
We will now get acquainted with this opportunity in detail, but I immediately want to make a reservation that the 1C company in its methodological recommendations does not recommend using it unnecessarily (we will talk about the reasons for this).
So let's go.
First of all, the AUTOORDER keyword can be placed in a query immediately after or instead of the ORDER BY section:
Auto-arranging works according to the following principles:
Case #1
If in a request:
- there is a section ORDER BY
For lookup tables, the default sorting fields are code and name, the choice from which is made in accordance with the lookup settings in the configurator:
For document tables, the default sort field is document date.
Consider an example:
Since the sort field Favorite color has type Reference.Colors Name
Case #2
If in a request:
- but there is a section RESULTS ON (we will go through it)
In this case, the query result will be sorted by total fields (in the same order).
Case #3
If in a request:
- missing section ORDER BY
- there is no section RESULTS ON
- but there is a GROUP BY section (we went through the grouping)
In this case, the query result will be sorted by grouping fields (in the same order).
Consider an example:
Since the grouping field City has type Directory.Cities, in the settings of which the field is selected as the main view Name, then this query is equivalent to:
Case #4
Finally, if in the request:
- missing section ORDER BY
- there is no section RESULTS ON
- GROUP BY section is missing
In this case, the query result will be ordered by the default sort fields for the tables from which the data is selected, in the order in which they appear in the query.
Consider an example:
Why auto-arranging is undesirable
Auto-arranging is good for:
- for universal queries, when the developer cannot foresee from which tables the data will be requested
- for cases where the resulting order of records is not important, but it must be the same regardless of the DBMS used
In all other cases, it is undesirable to use the auto-ordering feature, since fields that are sort fields today may no longer be sort fields tomorrow.
For example, today we can write code that is sensitive to query results from a lookup Food were sorted by field Name.
And tomorrow, 1C (or another developer) will change the database settings in the configurator so that the default sort field for the directory Food becomes, for example, a field The code. And, if we used auto-ordering in the query, then our report will break, because the sort order will already be different. You are reading a trial version of the lesson, full lessons are located.
Therefore, always try to specify specific fields and a specific sort order for them in the section SORT BY, such a request can no longer be broken just like this:
SELECT Code, Name FROM Directory. Food ORDER BY Name AGE |
Take the test
Start test
1. Query results are ordered by default
2. Query results can be sorted by
3. "ORDER BY FIELD_NAME" sorts by
4. To sort in ascending order, in the ORDER BY section, you must specify the field name and keyword
5. To sort in descending order, in the ORDER BY section, you must specify the field name and keyword
6. Auto-ordering in queries is possible
The query language is one of the fundamental mechanisms of 1C 8.3 for developers. With the help of queries, you can quickly get any data stored in the database. Its syntax is very similar to SQL, but there are some differences.
The main advantages of the 1C 8.3 (8.2) query language over SQL:
- dereferencing reference fields (turning one or more dots to object attributes);
- work with the results is very convenient;
- the ability to create virtual tables;
- the request can be written both in English and in Russian;
- the ability to block data to avoid deadlocks.
Disadvantages of the query language in 1C:
- unlike SQL, in 1C queries do not allow you to change data;
- lack of stored procedures;
- the impossibility of converting a string to a number.
Consider our mini tutorial on the basic constructions of the 1C query language.
Due to the fact that requests in 1C only allow you to receive data, any request must begin with the word "SELECT". After this command, the fields from which you want to get data are indicated. If you specify "*", then all available fields will be selected. The place from where the data will be selected (documents, registers, directories, etc.) is indicated after the word "FROM".
In the example below, the names of the entire nomenclature are selected from the "Nomenclature" reference book. After the word “HOW”, aliases (names) for tables and fields are indicated.
CHOOSE
Nomenclature.Name AS NameNomenclature
FROM
Directory. Nomenclature AS Nomenclature
Next to the "SELECT" command, you can specify keywords:
- VARIOUS. The query will select only rows that differ in at least one field (without duplicates).
- FIRST n, where n– the number of rows from the beginning of the result to be selected. Most often, this construction is used in conjunction with sorting (ORDER BY). For example, when you need to select a certain number of the latest documents by date.
- ALLOWED. This design allows you to select from the database only those records that are available to the current user. If this keyword is used, the user will receive an error message if they try to query records they do not have access to.
These keywords can be used all together or separately.
FOR CHANGE
This clause locks data to avoid conflicts. Locked data will not be read from another connection until the end of the transaction. In this clause, you can specify specific tables that you want to lock. Otherwise, all will be blocked. The design is relevant only for the automatic blocking mode.
Most often, the "FOR CHANGE" clause is used when receiving balances. Indeed, when several users work in the program at the same time, while one receives the balances, the other can change them. In this case, the resulting balance will no longer be correct. If you block the data with this proposal, then until the first employee receives the correct balance and performs all the necessary manipulations with it, the second employee will have to wait.
CHOOSE
Mutual settlements. Employee,
Mutual settlements. Amount Mutual settlements Balance
FROM
Accumulation Register. Mutual Settlements WITH Employees. Balances AS Mutual Settlements
FOR CHANGE
WHERE
The construction is necessary for imposing any selection on the unloaded data. In some cases of obtaining data from registers, it is more reasonable to prescribe selection conditions in the parameters of virtual tables. When using "WHERE", all records are obtained first, and only then the selection is applied, which significantly slows down the query.
The following is an example of a request to get contact persons with a specific position. The selection parameter has the following format: &ParameterName (parameter name is arbitrary).
SELECTION (CASE)
The construct allows you to specify conditions directly in the request body.
In the example below, the "AdditionalField" will contain text depending on whether the document is posted or not:
CHOOSE
AdmissionT&U.Link,
CHOICE
WHEN
THEN "Document posted!"
ELSE "Document not posted..."
END AS AdditionalField
FROM
Document.Receipt of GoodsServices AS ReceiptT&C
JOIN
Joins link two tables by certain condition connections.
LEFT/RIGHT JOIN
The essence of the LEFT join is that the first specified table is taken completely and the second one is attached to it by the condition of the connection. If there are no records corresponding to the first table in the second, then NULL is substituted as their values. Simply put, the main table is the first specified table and the data of the second table (if any) is already substituted for its data.
For example, you need to get item items from the documents “Receipt of goods and services” and prices from the information register “Item prices”. In this case, if the price of any position is not found, substitute NULL instead. All items from the document will be selected regardless of whether they have a price or not.
CHOOSE
Receipt of T&U. Nomenclature,
Prices.Price
FROM
Document.Receipt of GoodsServices.Goods AS ReceiptT&C
INNER JOIN
ON Receipt of Q&A.Nomenclature = Prices.Nomenclature
In RIGHT, everything is exactly the opposite.
FULL CONNECTION
This type of join differs from the previous ones in that all records of both the first table and the second will be returned as a result. If no records are found in the first or second table for the specified link condition, NULL will be returned instead.
When used in the previous example full connection all items of the item from the document "Receipt of goods and services" and all the latest prices from the register "Item prices" will be selected. The values of not found records, both in the first and in the second table, will be NULL.
INNER JOIN
The difference between an INNER join and a FULL join is that if a record is not found in at least one of the tables, then the query will not display it at all. As a result, only those item items from the Goods and Services Receipt document will be selected for which there are entries in the Item Prices information register, if in the previous example we replace FULL with INTERNAL.
GROUP BY
Grouping in 1C queries allows you to collapse table rows (grouping fields) according to a certain common ground(grouped fields). Grouping fields can only be displayed using aggregate functions.
The result of the next query will be a list of item types with their maximum prices.
CHOOSE
,
MAX(Price.Price) AS Price
FROM
GROUP BY
Prices.Nomenclature.TypeNomenclature
RESULTS
Unlike grouping, when using totals, all records are displayed and total rows are already added to them. Grouping displays only generalized records.
Results can be summarized for the entire table (using the keyword "GENERAL"), for several fields, for fields with a hierarchical structure (keywords "HIERARCHY", "ONLY HIERARCHY"). When summing up, it is not necessary to use aggregate functions.
Consider an example similar to the example above using grouping. In this case, the query result will return not only grouped fields, but also detailed records.
CHOOSE
Prices.Nomenclature.Type of Nomenclature AS Type of Nomenclature,
Prices.Price AS Price
FROM
RegisterInformation.PricesNomenclature.SliceLast AS Prices
RESULTS
MAXIMUM(Price)
ON
Type Nomenclature
HAVING
This operator is similar to the WHERE operator, but is only used for aggregate functions. Other fields than those used by this operator must be grouped. The "WHERE" operator is not applicable for aggregate functions.
In the example below, the maximum item prices are selected if they exceed 1000, grouped by item type.
CHOOSE
MAX(Price.Price) AS Price
FROM
RegisterInformation.PricesNomenclature.SliceLast AS Prices
GROUP BY
Prices.Nomenclature.TypeNomenclature
HAVING
MAX(Prices.Price) > 1000
SORT BY
The "ORDER BY" operator sorts the query result. To ensure that records are output in a consistent order, AUTO-ORDER is used. Primitive types are sorted according to the usual rules. Reference types are sorted by GUID.
An example of getting a list of employees sorted by name:
CHOOSE
Employees.Name AS Name
FROM
Directory. Employees AS Employees
SORT BY
Name
AUTO ORDER
Other constructions of the 1C query language
- UNITE- the results of two queries in one.
- UNITE ALL– similar to JOIN, but without grouping identical rows.
- EMPTY TABLE- sometimes used when joining queries to specify an empty nested table.
- PUT- creates a temporary table to optimize complex 1C queries. Such requests are called batch requests.
Query language features
- SUBSTRING truncates a string from a specified position by the specified number of characters.
- YEAR…SECOND allow you to get the selected value of the numeric type. The input parameter is a date.
- BEGINNING OF THE PERIOD AND END OF THE PERIOD are used when working with dates. The period type (DAY, MONTH, YEAR, etc.) is specified as an additional parameter.
- ADDDATE allows you to add or subtract from the date the specified time of a certain type (SECOND, MINUTE, DAY, etc.).
- DATE DIFFERENCE determines the difference between two dates, specifying the type of output value (DAY, YEAR, MONTH, etc.).
- ISNULL replaces the missing value with the specified expression.
- PRESENTATION and PRESENTATIONLINKS get the string representation of the specified field. They are used for any values and only reference values, respectively.
- TYPE, VALUE TYPE are used to determine the type of the input parameter.
- LINK is a logical comparison operator for the attribute value type.
- EXPRESS is used to convert the value to the desired type.
- DATE TIME gets a value of type "Date" from numeric values (Year, Month, Day, Hour, Minute, Second).
- MEANING in a 1C request, it is used to specify predefined values \u200b\u200b- directories, enumerations, plans for types of characteristics. Usage example: " Where LegalIndividual = Value(Enumeration.LegalIndividual.Individual)«.
Query Builder
To create queries with 1C, there is a very convenient built-in mechanism - the query designer. It contains the following main tabs:
- "Tables and fields" - contains the fields to be selected and their sources.
- "Links" - describes the conditions for the CONNECTION construct.
- "Grouping" - contains a description of the constructions of groupings and summarized fields by them.
- "Conditions" - is responsible for the selection of data in the request.
- "Advanced" - additional query parameters, such as the keywords of the "SELECT" command, etc.
- “Joins / Aliases” - the possibilities of joining tables are indicated and aliases are set (the “HOW” construct).
- "Order" - is responsible for sorting the result of queries.
- "Totals" - similar to the "Grouping" tab, but is used for the "TOTALS" construction.
The text of the request itself can be viewed by clicking on the "Request" button in the lower left corner. In this form, it can be corrected manually or copied.
Query Console
To quickly view the result of a query in the "Enterprise" mode, or to debug complex queries, use . The query text is written in it, parameters are set, and its result is shown.
You can download the query console on the ITS disk, or by .
The query language in 1C 8 is a simplified analogue of the well-known "structured programming language" (as it is often called, SQL). But in 1C it is used only for reading data, an object data model is used to change data.
Another interesting difference is the Russian syntax. Although in fact you can use English constructions.
Request example:
CHOOSE
Banks.Name,
Banks.CorrectAccount
FROM
Directory. Banks AS Banks
This request will allow us to see information about the name and correspondent account of all banks existing in the database.
The query language is the simplest and effective method obtaining information. As you can see from the example above, in the query language, you need to appeal with metadata names ( is a list of system objects that make up the configuration, i.e. directories, documents, registers, etc.).
Description of query language constructs
Request structure
To obtain data, it is enough to use the SELECT (select) and FROM (from) constructions. The simplest query looks like this:
SELECT * FROM Directories. Nomenclature
Where "*" means selection of all fields of the table, and References.Nomenclature - the name of the table in the database.
Consider a more complex and general example:
CHOOSE
<ИмяПоля1>HOW<ПредставлениеПоля1>,
Sum(<ИмяПоля2>) HOW<ПредставлениеПоля2>
FROM
<ИмяТаблицы1>HOW<ПредставлениеТаблицы1>
<ТипСоединения>COMPOUND<ИмяТаблицы2>HOW<ПредставлениеТаблицы2>
ON<УсловиеСоединениеТаблиц>WHERE
<УсловиеОтбораДанных>GROUP BY
<ИмяПоля1>SORT BY
<ИмяПоля1>RESULTS
<ИмяПоля2>
ON
<ИмяПоля1>
In this query, we select the data of the fields “FieldName1” and “FieldName1” from the tables “TableName1” and “TableName”, assign synonyms to the fields using the “HOW” operator, connect them according to a certain condition “TableConnection Condition”.
From the received data, we select only data that meets the condition from the “WHERE” “Data Selection Condition”. Next, we group the query by the “FieldName1” field, while summing up “FieldName2”. We create totals for the “FieldName1” field and the final field “FieldName2”.
The last step is to sort the query using the "ORDER BY" construct.
General designs
Consider general designs query language 1C 8.2.
FIRSTn
Using this operator, you can get n number of first records. The order of the records is determined by the order in the query.
SELECT FIRST 100
Banks.Name,
Banks.Code AS BIC
FROM
Directory. Banks AS Banks
SORT BY
Banks. Name
The request will receive the first 100 entries of the "Banks" directory, sorted alphabetically.
ALLOWED
This design is relevant for working with the mechanism. The essence of the mechanism is to restrict reading (and other actions) to users for specific records in the database table, and not the table as a whole.
If the user attempts to read records that are not available to him with a query, he will receive an error message. To avoid this, you should use the "ALLOWED" construction, i.e. the request will read only records allowed to it.
SELECT ALLOWED
RepositoryAdditionalInformation.Link
FROM
Directory.Storage of Additional Information
VARIOUS
The use of "DIFFERENT" will make it possible to exclude duplicate rows from entering the result of a 1C query. Duplication means that all fields of the request match.
SELECT FIRST 100
Banks.Name,
Banks.Code AS BIC
FROM
Directory. Banks AS Banks
EmptyTable
This construction is used very rarely to combine queries. When joining, it may be necessary to specify an empty nested table in one of the tables. The "EmptyTable" operator is just right for this.
Example from help 1C 8:
CHOOSE Reference.Number, EMPTYTABLE.(Nom, Tov, Qty) AS COMPOSITION
FROM Document.Invoice
UNITE ALL
SELECT Link.Number, Composition.(LineNumber, Product, Quantity)
FROM Document.Invoice Document.Invoice.Composition.*
ISNULL
A very useful feature that allows you to avoid many mistakes. IsNULL() allows you to replace the NULL value with the desired one. Very often used in checks for the presence of a value in joined tables, for example:
CHOOSE
NomenclatureRef.Reference,
IsNULL(Product Remaining.QuantityRemaining,0) AS QuantityRemaining
FROM
It can also be used in other ways. For example, if for each row it is not known in which table the value exists:
ISNULL(InvoiceInvoiceReceived.Date, InvoiceIssued.Date)
AS is an operator that allows us to assign a name (synonym) to a table or field. We saw an example of usage above.
These constructions are very similar - they allow you to get a string representation desired value. The only difference is that VIEW converts any values to a string type, while REF VIEW converts only reference values. REFERENCE REPRESENTATION is recommended to be used in data composition system queries for optimization, unless, of course, the reference data field is planned to be used in filters.
CHOOSE
View(Link), //string, for example "Advance report No. 123 dated 10/10/2015
Representation(DeletionMark) AS DeleteMarkText, //string, "Yes" or "No"
ReferenceRepresentation(DeletionMark) AS DeletionMarkBoolean //boolean, True or False
FROM
Document.AdvanceReport
EXPRESS
Express allows you to convert the field values to the desired data type. You can convert a value to either a primitive type or a reference type.
Express for reference type is used to restrict the requested data types in the fields of a composite type, often used to optimize system performance. Example:
EXPRESS(Table of Costs.Subconto1 AS Directory.Cost Items).Type of ActivityFor Tax Accounting of Costs
For primitive types, this function is often used to limit the number of characters in fields of unlimited length (cannot be compared against such fields). To avoid the error " Invalid parameters in compare operation. Can't compare fields
unlimited length and fields of incompatible types”, it is necessary to express such fields as follows:
EXPRESS(Comment AS String(150))
DATE DIFFERENCE
Get 267 1C video lessons for free:
An example of using IS NULL in a 1C query:
CHOOSE FROM
Ref
LEFT JOIN
Software
WHERE NOT Remains of Goods. Quantity Remains IS NULL
The data type in a query can be determined as follows: using the TYPE() and VALUETYPE() functions, or using the logical REFERENCE operator. These two functions are similar.
Predefined values
In addition to using passed parameters in queries in the 1C query language, you can use predefined values or . For example, enumerations, predefined directories, charts of accounts, and so on. For this, the “Value ()” construction is used.
Usage example:
WHERE
WHERE Counterparties.KindofContactInformation = Value(Enumeration.Types ofContactInformation.Phone)
WHERE Account Balances.Accounting Account = Value(Chart of Accounts.Self-supporting.Profit-Loss)
Connections
Connections are of 4 types: LEFT, RIGHT, COMPLETE, INTERNAL.
LEFT and RIGHT JOIN
Joins are used to link two tables by a certain condition. Feature at LEFT JOIN in that we take the first specified table completely and bind the second table by condition. The fields of the second table that could not be linked by condition are filled with the value NULL.
For example:
It will return the entire table of Counterparties and fill in the “Bank” field only in those places where the condition “Counterparties.Name = Banks.Name” will be met. If the condition is not met, the Bank field will be set to NULL.
RIGHT JOIN in 1C language absolutely similar LEFT join except for one difference - in RIGHT JOIN the “master” 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, joins only those that can be joined by condition.
For example:
FROM
FULL CONNECTION
Directory. Banks AS BanksON
The query language will return both tables in full only if the condition to join the records is met. Unlike a left/right join, it is possible for NULLs to occur 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 ClientsINNER JOIN
Directory. Banks AS BanksON
Clients.Name = Banks.Name
This query will return only rows where the bank and counterparty have the same name.
Associations
The UNION and UNION ALL construct combines two results into one. Those. the result of executing two will be "merged" into one, common.
That is, the system works exactly the same as regular ones, only for a temporary table.
How to use INDEX BY
However, one point should be taken into account. Building an index on a temporary table also takes time to complete. Therefore, it is advisable to use the ” ” construction only if it is known for sure that there will be more than 1-2 records in the temporary table. Otherwise, the effect may be the opposite - the performance of indexed fields does not compensate for the index building time.
CHOOSE
CurrenciesCurrencySliceLast.Currency AS Currency,
CurrenciesCurrencySliceLast.Course
PUT Currency Rates
FROM
DataRegister.Currency Rates.SliceLast(&Period,) AS Currency RatesSliceLast
INDEX BY
Currency
;
CHOOSE
PricesNomenclature.Nomenclature,
PricesNomenclature.Price,
PricesNomenclatures.Currency,
RatesCurrency.Course
FROM
RegisterInformation.PricesNomenclature.SliceLast(&Period,
Item B (&Nomenclature) AND PriceType = &PriceType) AS Item Prices
LEFT JOIN Currencies Rates AS Currencies Rates
Software PricesNomenclature.Currency = RatesCurrency.Currency
grouping
The 1C query language allows you to use special aggregate functions when grouping query results. Grouping can also be used without aggregate functions to "eliminate" duplicates.
There are the following functions:
Sum, Quantity, Number of different, Maximum, Minimum, Average.
Example #1:
CHOOSE
Realization of Goods, Services, Goods.Nomenclature,
SUM(Sale of Goods, Services, Goods. Quantity) AS Quantity,
SUM(Sale of Goods, Services, Goods. Amount) AS Sum
FROM
GROUP BY
Realization of Goods, Services, Goods. Nomenclature
The query receives all lines with goods and summarizes them by quantity and amounts in the context of the item.
Example #2
CHOOSE
Banks.Code,
NUMBER(DIFFERENT Banks.Reference) AS Number ofDuplicates
FROM
Directory. Banks AS Banks
GROUP BY
Banks.Code
This example will display a list of BICs in the "Banks" directory and show how many duplicates exist for each of them.
Results
Totals are a way to get data from a system with a hierarchical structure. Aggregate functions can be used for summary fields, as for groupings.
One of the most popular ways to use totals in practice is batch write-off of goods.
CHOOSE
FROM
Document.Sale of GoodsServices.Goods AS Realization of GoodsServicesGoods
SORT BY
RESULTS
SUM(Number),
SUM(Amount)
ON
Nomenclature
The query will result in the following hierarchical :
General results
If you need to get totals for all "totals", use the "TOTAL" operator.
CHOOSE
Realization of Goods, Services, Goods. Nomenclature AS Nomenclature,
Realization Goods Services Goods. Reference AS Document,
Sales of Goods, Services, Goods. Quantity AS Quantity,
Realization of Goods of Services Goods. Amount AS Amount
FROM
Document.Sale of GoodsServices.Goods AS Realization of GoodsServicesGoods
SORT BY
Realization of Goods and Services Goods. Reference. Date
RESULTS
SUM(Number),
SUM(Amount)
ON
GENERAL,
Nomenclature
As a result of executing the query, we get the following result:
In which 1 level of grouping is the aggregation of all the required fields.
ordering
The ORDER BY operator is used to sort the result of a query.
Sorting for primitive types (string, number, boolean) follows the usual rules. For fields of reference types, sorting occurs on the internal representation of the reference (unique identifier), and not on the code or on the representation of the reference.
CHOOSE
FROM
Directory. Nomenclature AS Nomenclature
SORT BY
Name
The query will display a list of names of the nomenclature reference book, sorted alphabetically.
Auto-arranging
The result of an unsorted query is a randomly represented set of rows. The developers of the 1C platform do not guarantee the output of lines in the same sequence when executing the same queries.
If you need to display table records in a constant order, you must use the "Auto-Ordering" construct.
CHOOSE
Nomenclature. Name AS Name
FROM
Directory. Nomenclature AS Nomenclature
AUTO ORDER
Virtual Tables
Virtual tables in 1C are a unique feature of the 1C query language, which is not found in other similar syntaxes. Virtual table - fast way obtaining profile information from registers.
Each register type has its own set of virtual tables, which may differ depending on the register settings.
- cut first;
- slice of the latter.
- leftovers;
- turnovers;
- balances and turnovers.
- movements from subconto;
- turnovers;
- revolutions Dt Kt;
- leftovers;
- balances and turnovers
- subconto.
- base;
- graph data;
- actual validity period.
For the solution developer, data is taken from one (virtual) table, but in fact, the 1C platform takes from many tables, converting them into the desired form.
CHOOSE
GoodsIn WarehousesRemainsAnd Turnovers.Nomenclature,
GoodsIn WarehousesRemainsAnd Turnovers.Quantity
GoodsIn WarehousesRemainsAndTurnovers.QuantityTurnover,
GoodsIn WarehousesRemainsAnd Turnovers.QuantityIncoming,
GoodsIn WarehousesRemainsAnd Turnovers.QuantityConsumption
GoodsIn WarehousesRemainders and Turnovers. Quantity
FROM
Accumulation Register. Goods In Warehouses. Remains And Turnovers AS Goods In Warehouses Remains And TurnoversSuch a query allows you to quickly get a large amount of data.
Virtual Table Options
A very important aspect of working with virtual tables is the use of parameters. Virtual table options are specialized options for selection and customization.
For such tables, it is considered incorrect to use selection in the WHERE clause. In addition to the fact that the query becomes suboptimal, it is possible to receive incorrect data.
An example of using such parameters:
Accumulation Register.GoodsInWarehouses.RemainsAndTurnovers(&StartPeriod, &EndPeriod, Month, Movements ANDPeriodBorders, Nomenclature = &NecessaryNomenclature)
Algorithm for virtual tables
For example, the most used virtual table of the "Remainders" type stores data from two physical tables - balances and movements.
When using a virtual table, the system performs the following manipulations:
- We get the calculated value nearest by date and dimensions in the totals table.
- “Add” the amount from the movement table to the amount from the totals table.
Such simple actions can significantly improve the performance of the system as a whole.
Using the Query Builder
Query Builder- a tool built into the 1C Enterprise system, which greatly facilitates the development of database queries.
The query builder has a fairly simple, intuitive interface. Nevertheless, let's consider the use of the query constructor in more detail.
The query text constructor is launched by the context menu (right-click) in the right place in the program code.
Description of the 1C query constructor
Let's consider each tab of the designer in more detail. The exception is the Builder tab, this is a topic for a separate discussion.
Tables and fields tab
This tab specifies the data source and fields to display in the report. As a matter of fact here constructions SELECT. FROM are described.
The source can be a physical database table, a virtual register table, temporary tables, nested queries, etc.
In the context menu of virtual tables, you can set the parameters of the virtual table:
Links tab
The tab is used to describe connections of several tables, creates constructions with the word JOIN.
Grouping tab
On this tab, the system allows you to group and summarize the desired fields of the table result. The use of the GROUP BY, SUM, MINIMUM, AVERAGE, MAXIMUM, NUMBER, NUMBER OF DIFFERENT structures is described.
Conditions tab
Responsible for everything that goes in the request text after the WHERE construct, i.e. for all the conditions imposed on the received data.
Advanced tab
tab Additionally replete with all sorts of parameters that are very important. Let's look at each of the properties.
grouping Selecting Records:
- First N– a parameter that returns only N records in the query (the FIRST operator)
- No recurring– ensures uniqueness of received records (DIFFERENT operator)
- Allowed– allows you to select only those records that the system allows you to select taking into account (the ALLOWED construction)
grouping Request type determines what type of query will be: fetching data, creating a temporary table, or destroying a temporary table.
Below there is a flag Lock received data for later modification. It allows you to enable the ability to set a data lock, which ensures the safety of data from the moment they are read to modified (relevant only for the Automatic lock mode, construction FOR CHANGE).
Joins/Aliases tab
On this tab of the query designer, you can set the ability to join different tables and aliases (the AS construct). Tables are listed on the left side. If you set the flags in front of the table, the JOIN construction will be used, otherwise - JOIN ALL (differences between the two methods). On the right side, field correspondences in different tables are indicated; if a correspondence is not specified, the query will return NULL.
Order tab
Here you specify the sort order of values (ORDER BY) - descending (DESC) or ascending (ASC).
There is also an interesting flag - Auto-arranging(in the query - AUTOORDER). By default, the 1C system displays data in a “chaotic” order. If you set this flag, the system will sort the data by internal data.
Query Batch tab
You can create new ones on the Query Design tab and also use it as a navigation. In the text of the request, the packets are separated by the symbol “;” (semicolon).
Query button in query builder
There is a Request button in the lower left corner of the query builder, with which you can view the text of the query at any time:
In this window, you can make adjustments to the request and execute it.
Using the Query Console
Query Console - simple and convenient way for debugging complex queries and getting information quickly. In this article, I will try to describe how to use the Query Console and provide a link to download the Query Console.
Let's take a closer look at this tool.
Download request console 1C
First of all, to get started with the query console, you need to download it from somewhere. Processings are usually divided into two types - managed forms and conventional ones (or, sometimes, they are called 8.1 and 8.2 / 8.3).
I tried to combine these two views in one processing - in the desired mode of operation, the desired form opens (in managed mode, the console only works in thick mode).
Description of the 1C query console
Let's start our consideration of the query console with a description of the main processing panel:
In the header of the query console, you can see the execution time of the last query with an accuracy of milliseconds, this allows you to compare different designs in terms of performance.
The first group of buttons in the command bar is responsible for saving the current queries in an external file. This is very convenient, you can always return to writing a complex query. Or, for example, store a list of typical examples of certain constructions.
On the left, in the "Request" field, you can create new requests and save them in a tree structure. The second group of buttons is just responsible for managing the list of requests. With it, you can create, copy, delete, move a request.
- Runrequest– simple execution and getting results
- Execute package- allows you to view all intermediate requests in a batch of requests
- Viewing temporary tables- allows you to see the results that temporary queries return in a table
Request parameters:
Allows you to set the current parameters for the request.
In the query parameters window, the following is interesting:
- Button Get from request automatically finds all parameters in the request for the convenience of the developer.
- Flag Single parameters for all requests– when set, its processing does not clear the parameters when moving from request to request in the general list of requests.
Set a parameter as a list of values very simple, it is enough to click on the value clear button (cross) when choosing a parameter value, the system will prompt you to select the data type, where you need to select “Value list”:
Also in the top panel there is a button for calling the query console settings:
Here you can specify query autosave options and query execution options.
The request text is entered in the console request field. This can be done with a simple query test set or by calling a special tool - the query builder.
The 1C 8 query constructor is called from the context menu (right mouse button) when you click on the input field:
Also in this menu there are such useful functions as cleaning or adding line break characters (“|”) to the request or getting the request code in this convenient form:
Request = New Request;
Query.Text = ”
|CHOOSE
| Currencies.Link
| FROM
| Handbook. Currencies AS Currencies”;
QueryResult = Query.Execute();
The lower field of the query console displays the query result field, for which this processing was created:
Also, the query console, in addition to the list, can display data in the form of a tree - for queries containing totals.
Query Optimization
One of the most important points in improving the productivity of 1C enterprise 8.3 is optimizationrequests. This point is also very important for passing certification. Below we will talk about the typical causes of non-optimal query performance and optimization methods.
Selections in a virtual table using the WHERE construct
It is necessary to apply filters on the details of a virtual table only through the VT parameters. In no case should you use the WHERE construction for selection in a virtual table, this is a gross mistake from the point of view of optimization. In the case of selection using WHERE, in fact, the system will receive ALL records and only then select the necessary ones.
RIGHT:
CHOOSE
FROM
Accumulation register.Mutual settlements withDepositors ofOrganizations.Balances(
,
Organization = &Organization
AND Individual = &Individual) HOW Mutual settlements withDepositorsOrganizationsBalances
NOT PROPERLY:
CHOOSE
Mutual settlements withDepositorsOrganizationsBalances.AmountBalance
FROM
Accumulation Register. Mutual Settlements with Depositors of Organizations. Balances(,)
WHERE
Mutual settlements withDepositorsOrganizationsBalances.Organization = &Organization
AND Mutual settlements withDepositorsOrganizationsBalances.Individual = &Individual
Getting the value of a field of a composite type through a dot
When retrieving data of a composite type in a dotted query, the system left-joins exactly as many tables as there are types possible in the field of the composite type.
For example, it is highly undesirable for optimization to refer to the field of the register entry - registrar. The registrar has a composite data type, among which are all possible types of documents that can write data to the register.
NOT PROPERLY:
CHOOSE
RecordSet.Registrar.Date,
RecordSet.Number
FROM
Accumulation Register.GoodsOrganization AS A Set Of Records
That is, in fact, such a query will refer not to one table, but to 22 database tables (this register has 21 registrar types).
RIGHT:
CHOOSE
CHOICE
WHEN GoodsOrg.Registrar LINK Document.Sale of GoodsServices
THEN EXPRESS(GoodsOrg.Registrar AS Document.Sale of GoodsServices).Date
WHEN GoodsOrg.Registrar LINK Document.Receipt of GoodsServices
THEN EXPRESS(Goods Org. Registrar AS Document. Receipt of Goods/Services). Date
END AS Date,
GoodsOrg.Quantity
FROM
RegisterAccumulation.GoodsOrganizations AS GoodsOrg
Or the second option - adding such information to the props, for example, in our case - adding a date.
RIGHT:
CHOOSE
GoodsOrganizations.Date,
GoodsOrganizations.Quantity
FROM
RegisterAccumulation.GoodsOrganizations AS GoodsOrganizations
Subqueries in a join condition
For optimization, it is unacceptable to use subqueries in join conditions, this significantly slows down the query. It is advisable to use VT in such cases. To connect, you need to use only metadata and BT objects, having previously indexed them by the connection fields.
NOT PROPERLY:
CHOOSE …
LEFT JOIN (
SELECT FROM Register of information.Limits
WHERE …
GROUP BY…
) ON …
RIGHT:
CHOOSE …
PUT Limits
FROM Register of information.Limits
WHERE …
GROUP BY…
INDEX BY…;CHOOSE …
FROM Document.Realization of GoodsServices
LEFT JOIN Limits
ON …;
Linking Records to Virtual Tables
There are situations when, when connecting a virtual table to others, the system does not work optimally. In this case, to optimize the performance of the query, you can try to place the virtual table in a temporary table, remembering to index the joined fields in the query of the temporary table. This is due to the fact that VTs are often contained in several physical tables of the DBMS, as a result, a subquery is compiled for their selection, and the problem is similar to the previous paragraph.
Using filters on non-indexed fields
One of the most common mistakes when compiling queries is the use of conditions on non-indexed fields, this contradicts query optimization rules. The DBMS cannot perform the query optimally if the query is filtered by non-indexed fields. If a temporary table is taken, it is also necessary to index the connection fields.
There must be a suitable index for each condition. A suitable index is one that meets the following requirements:
- The index contains all the fields listed in the condition.
- These fields are at the very beginning of the index.
- These selections go in a row, that is, values that are not participating in the query condition do not “wedged” between them.
If the DBMS does not pick up the correct indexes, then the entire table will be scanned - this will have a very negative impact on performance and can lead to a long lock on the entire recordset.
Using logical OR in conditions
That's all, this article covered the basic aspects of query optimization that every 1C expert should know.
A very useful free video course on developing and optimizing queries, strongly recommend beginners and beyond!
/
Implementation of data processing
Ordering query results
1.1. If the algorithm for processing the query results depends on the order of the records in the query, or if the query processing result is presented to the user in one form or another, then the query text should use the sentence SORT BY. In the absence of expression SORT BY no assumptions can be made about the order in which records will be presented in the query results.
Typical examples of problems that may occur are:
- different sequence of rows in the tabular part when filling in according to the results of the query;
- different order of data output (rows, columns) in reports;
- different filling of document movements based on the results of the query (*).
The likelihood of having different results when performing the same actions increases
- when migrating an infobase to another DBMS
- when changing the DBMS version
- when changing DBMS parameters
* Note: ordering the results of queries that form movements is justified only if the ordering is part of the algorithm for generating movements (for example, writing off the balance of consignments of goods according to FIFO). In other cases, records should not be ordered, as additional ordering will create an excessive load on the DBMS.
1.2. If the query results must be displayed to the user in one way or another, then
- it is necessary to order the results of such queries by the fields of primitive types;
- ordering by fields of reference types should be replaced by ordering by string representations of these fields.
Otherwise, the order of the lines will look random (inexplicable) to the user.
See also: Sorting rows of value tables
1.3. No offer SORT BY justified only when
- the algorithm for processing query results does not rely on a specific order of records
- the result of processing the completed request is not shown to the user
- query result - obviously one record
Sharing with design VARIOUS
2. If the query uses the construction VARIOUS, ordering should be performed only by the fields included in the selection (in the section CHOOSE).
This requirement is related to the following feature of query execution: ordering fields are implicitly included in the selection fields, which in turn can lead to the appearance of several rows with the same values of the selection fields as a result of the query.
Restrictions on using the AUTOORDER construct
3. Use of construction FIRST together with the design AUTO ORDER forbidden.
In other cases, the design AUTO ORDER it is also not recommended to use, since the developer does not control which fields will be used for ordering. The use of such a construction is justified only in cases where the resulting order of records is not important, but it must be the same regardless of the DBMS used.