1s query sorting by document type. Little tricks of big requests
To form and execute queries to database tables in the 1C platform, a special programming language object is used. Request. This object is created by calling the construct New request. It is convenient to use a query when you need to get a complex selection of data, grouped and sorted as necessary. A classic example of using a query is getting a summary of the state of an accumulation register at a specific point in time. Also, the query mechanism makes it easy to obtain information in various time sections.
The request text is the instruction according to which the request should be executed. The body of the request describes:
- infobase tables used as query data sources;
- table fields that need to be processed in the query;
- grouping rules;
- sorting results;
- etc.
The query language of the 1C platform is very similar to the syntax of other SQL languages, but there are differences. The main advantages of the built-in query language are: field dereferencing, virtual tables, convenient work with totals, untyped fields in queries.
- enum values;
- predefined data:
- directories;
- plans of types of characteristics;
- charts of accounts;
- plans for types of calculations;
- empty links;
- values of waypoints of business processes.
An example of using a literal MEANING:
WHERE City = VALUE(Directory.Cities.Moscow)
WHERE City = VALUE(Reference.Cities.EmptyReference)
WHEREItemType = VALUE(Enumeration.ProductTypes.Service)
WHEREMovementType = VALUE(MovementTypeAccumulation.Income)
WHERE RoutePoint = VALUE(BusinessProcess.BusinessProcess1.RoutePoint.Action1
2) Using instructions AUTO ORDER in a query, the query execution time can be very high, so if sorting is not required, then it is better not to use it at all. In most cases, the best way to apply sorting is with the statement SORT BY.
Auto-arranging works according to the following principles:
- If the ORDER BY clause was specified in the query, then each reference to the table in this clause will be replaced by the fields by which the table is sorted by default (for directories, this is the code or name, for documents, the date of the document). If the ordering field refers to a hierarchical directory, then hierarchical sorting by this directory will be applied.
- If there is no ORDER BY clause in the query, but there is a TOTALS clause, then the query result will be sorted by the fields present in the RESULTS clause after the keyword BY, in the same sequence and, if the totals were calculated by the fields - links, then by the sorting fields by default of the tables that were referenced.
- If there are no ORDER BY and TOTAL clauses in the query, but there is a GROUP BY clause, then the query result will be sorted by the fields present in the sentence in the same sequence and, if the grouping was carried out by fields - links, then by default sorting fields tables that were referenced.
- If the query does not contain the clauses and ORDER BY, TOTAL, and GROUP BY, the result will be ordered by the default sort fields for the tables from which the data is selected, in the order they appear in the query.
If the query contains the TOTAL clause, each level of totals is ordered separately.
3) To avoid re-querying the database when displaying the query result to the user (for example, building a query or displaying the query result using a spreadsheet document), it is useful to use the instruction PRESENTATIONLINKS A that allows you to get a representation of a reference value.
It is also possible to use the instruction PERFORMANCE- designed to get a string representation of a value of an arbitrary type. The difference between these instructions is that in the first case, if the instructions pass a link, the result will be a string. In other cases, the result will be the value of the passed parameter. In the second case, the result of the instruction will always be a string!
4) If the query contains a field with a composite type, then for such fields it becomes necessary to cast the field values to a specific type using the instruction EXPRESS, which will allow you to remove unnecessary tables from the left connection with a field of a composite data type and speed up the query.
Example:
There is a register for the accumulation of Remains of Goods, in which the Registrar field has a composite type. In the request, the Date and Number of the Goods Receipt documents are selected, while accessing the details of the document through the Registrar field does not result in many left connections of the accumulation register table with the tables of register documents.
CHOOSE
EXPRESS(Remains of Goods.Registrar AS Document.Receipt of Goods).Number AS Receipt Number,
EXPRESS(Remains of Goods.Registrar AS Document.Receipt of Goods).Date AS Date of Receipt
FROM
Accumulation Register.Remains of Goods AS Remains of Goods
If a type cast is not considered feasible, then the result of the type cast will be NULL.
5) Do not forget about the instructions ALLOWED, which means that the query will only select records for which the current user has permissions. If this word is not specified, then in the case when the query selects records for which the user does not have rights, the query will work with an error.
6) If the query uses a union, and in some parts of the union there are nested tables (a document with a tabular part), and in some there is no need to supplement the selection list with fields - empty nested tables. This is done using the keyword EMPTYTABLE, after which the aliases of the fields that the nested table will consist of are indicated in brackets.
Example:
// Select fields Number and Composition
// from virtual table Document.Invoice
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.*
7) In order to avoid duplicate lines in the query result, you should use the instruction VARIOUS, because it is clearer and clearer, and the instruction GROUP BY used for grouping using aggregate functions. By the way, when using aggregate functions, the sentence GROUP BY may not be specified at all, while all query results will be grouped into one single line.
Example:
// It is necessary to find out which counterparties
// the goods were shipped for the period.
Select Various
Document.Invoice.Contractor
8) Instruction GROUP BY allows you to access top-level fields, without grouping results by these fields, if aggregate functions are applied to the fields of a nested table. Although it is written in the 1C help, when grouping query results, aggregate functions must be indicated in the list of selection fields, and in addition to aggregate functions, only the fields by which grouping is carried out can be indicated in the list of selection 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 AS Receipt of Goods and Services
GROUP BY
Receipt of Goods and Services. Goods. (Nomenclature)
9) Instruction IS NULL is intended to replace the NULL value with another value, but do not forget that the second parameter will be converted to the type of the first if the type of the first parameter is a string or a number.
10) When referring to the main table, you can refer to the data of the subordinate table in the condition. This feature is called dereferencing the fields of a sub-table.
Example (search for documents containing a certain product in the tabular section):
CHOOSE
Entrance.Link
FROM
Document.Incoming Where Incoming.Goods.Nomenclature = &Nomenclature.
The advantage of this query over the query on the Incoming.Products subtable is that if there are duplicates in documents, the query result will return only unique documents without using the keyword VARIOUS.
11) An interesting operator option IN is a check of the occurrence of an ordered set in the set of such sets (Field1, Field2, ... , FieldN) In (Field1, Field2, ... , FieldN).
Example:
CHOOSE
Contractors.Link
WHERE
(Contractors.Link, Goods.Link)
(SELECT Sales.Customer, Sales.Product
FROM Accumulation Register. Sales AS Sales)
FROM
Directory. Counterparties,
Directory.Products
12) Whenever possible, use virtual tables requests. When creating a query, the system provides a number of virtual tables as data sources - these are tables that are also the result of a query that the system generates at the time of execution of the corresponding code section.
The developer can independently obtain the same data that the system provides to him as virtual tables, however, the algorithm for obtaining this data will not be optimized, because:
- All virtual tables are parameterized, i.e. the developer is given the opportunity to set some parameters that the system will use when generating a request to create a virtual table. Depending on what virtual table parameters are specified by the developer, the system can generate DIFFERENT queries to get the same virtual table, and they will be optimized in terms of the parameters passed.
- It is not always possible for a developer to gain access to the data that the system has access to.
Microsoft OLE DB Provider for SQL Server: Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.
HRESULT=80040E14, SQLSTATE=42000, native=8618
To avoid this error, it is not recommended to use the SUBSTRING() function to convert strings of unlimited length to strings of limited length. Instead, it is better to use the cast operator EXPRESS().
14) Use with care OR in the WHERE construct, since the use of a condition with OR can significantly "heavier" the query. You can solve the problem with the CONNECT ALL construction.
Example:
CHOOSE
FROM
WHERE
_DemoContractors.Link = &Link1
UNITE ALL
CHOOSE
_Demo Counterparties.NameFull
FROM
Directory._DemoContractors HOW _DemoContractors
WHERE
_DemoContractors.Link = &Link2
15) Condition NOT IN in the WHERE construct increases the execution time of the query, since it is a kind of NOT (OR1 OR2 ... ORn), so for large tables, try to use a LEFT JOIN with the IS NULL condition.
Example:
CHOOSE
_DemoContractors.Link
FROM
Directory._DemoContractors HOW _DemoContractors
LEFT JOIN Document._DemoBuyerOrder AS _DemoBuyerOrder
Software _DemoContractors.Link = _BuyerDemoOrder.Contractor
WHERE
_Buyer's DemoOrder.Counterparty IS NULL
15) When using Temporary tables you need to index the condition and join fields in these tables, BUT, when using indexes, the query can run even slower. Therefore, it is necessary to analyze each query with and without an index, measure the speed of query execution and make a final decision.
If you place data in a temporary table that is initially indexed on some fields, then there will no longer be an index on these fields in the temporary table.
16) If you don't use Temp table manager, then there is no need to explicitly delete the temporary table, it will be deleted after the completion of the batch query, otherwise the temporary table should be deleted in one of the following ways: using the DELETE command in the query, call the TemporaryTable Manager.Close() method.
The query language is one of the fundamental mechanisms of 1C 8.3 for developers. With the help of queries, you can quickly get any data stored in the database. Its syntax is very similar to SQL, but there are some differences.
The main advantages of the 1C 8.3 (8.2) query language over SQL:
- dereferencing reference fields (turning one or more dots to object attributes);
- work with the results is very convenient;
- the ability to create virtual tables;
- the request can be written both in English and in Russian;
- the ability to block data to avoid deadlocks.
Disadvantages of the query language in 1C:
- unlike SQL, in 1C queries do not allow you to change data;
- lack of stored procedures;
- the impossibility of converting a string to a number.
Consider our mini tutorial on the basic constructions of the 1C query language.
Due to the fact that requests in 1C only allow you to receive data, any request must begin with the word "SELECT". After this command, the fields from which you want to get data are indicated. If you specify "*", then all available fields will be selected. The place from where the data will be selected (documents, registers, directories, etc.) is indicated after the word "FROM".
In the example below, the names of the entire nomenclature are selected from the "Nomenclature" reference book. After the word “HOW”, aliases (names) for tables and fields are indicated.
CHOOSE
Nomenclature.Name AS NameNomenclature
FROM
Directory. Nomenclature AS Nomenclature
Next to the "SELECT" command, you can specify keywords:
- VARIOUS. The query will select only rows that differ in at least one field (without duplicates).
- FIRST n, Where n– the number of rows from the beginning of the result to be selected. Most often, this construction is used in conjunction with sorting (ORDER BY). For example, when you need to select a certain number of the latest documents by date.
- ALLOWED. This design allows you to select from the database only those records that are available to the current user. If this keyword is used, the user will receive an error message if they try to query records they do not have access to.
These keywords can be used all together or separately.
FOR CHANGE
This clause locks data to avoid conflicts. Locked data will not be read from another connection until the end of the transaction. In this clause, you can specify specific tables that you want to lock. Otherwise, all will be blocked. The design is relevant only for the automatic blocking mode.
Most often, the "FOR CHANGE" clause is used when receiving balances. Indeed, when several users work in the program at the same time, while one receives the balances, the other can change them. In this case, the resulting balance will no longer be correct. If you block the data with this proposal, then until the first employee receives the correct balance and performs all the necessary manipulations with it, the second employee will have to wait.
CHOOSE
Mutual settlements. Employee,
Mutual settlements. Amount Mutual settlements Balance
FROM
Accumulation Register. Mutual Settlements WITH Employees. Balances AS Mutual Settlements
FOR CHANGE
WHERE
The construction is necessary for imposing any selection on the unloaded data. In some cases of obtaining data from registers, it is more reasonable to prescribe selection conditions in the parameters of virtual tables. When using "WHERE", all records are obtained first, and only then the selection is applied, which significantly slows down the query.
The following is an example of a request to get contact persons with a specific position. The selection parameter has the following format: &ParameterName (parameter name is arbitrary).
SELECTION (CASE)
The construct allows you to specify conditions directly in the request body.
In the example below, the "AdditionalField" will contain text depending on whether the document is posted or not:
CHOOSE
AdmissionT&U.Link,
CHOICE
WHEN
THEN "Document posted!"
ELSE "Document not posted..."
END AS AdditionalField
FROM
Document.Receipt of GoodsServices AS ReceiptT&C
JOIN
Joins link two tables by a certain link condition.
LEFT/RIGHT JOIN
The essence of the LEFT join is that the first specified table is taken completely and the second one is attached to it by the condition of the connection. If there are no records corresponding to the first table in the second, then NULL is substituted as their values. Simply put, the main table is the first specified table and the data of the second table (if any) is already substituted for its data.
For example, you need to get item items from the documents “Receipt of goods and services” and prices from the information register “Item prices”. In this case, if the price of any position is not found, substitute NULL instead. All items from the document will be selected regardless of whether they have a price or not.
CHOOSE
Receipt of T&U. Nomenclature,
Prices.Price
FROM
Document.Receipt of GoodsServices.Goods AS ReceiptT&C
INNER JOIN
ON Receipt of Q&A.Nomenclature = Prices.Nomenclature
In RIGHT, everything is exactly the opposite.
FULL CONNECTION
This type of join differs from the previous ones in that all records of both the first table and the second will be returned as a result. If no records are found in the first or second table for the specified link condition, NULL will be returned instead.
When using the full join in the previous example, all item items from the Goods and Services Receipt document and all the latest prices from the Item Prices register will be selected. The values of not found records, both in the first and in the second table, will be NULL.
INNER JOIN
The difference between an INNER join and a FULL join is that if a record is not found in at least one of the tables, then the query will not display it at all. As a result, only those item items from the Goods and Services Receipt document will be selected for which there are entries in the Item Prices information register, if in the previous example we replace FULL with INTERNAL.
GROUP BY
Grouping in 1C queries allows you to collapse table rows (grouping fields) according to a certain common feature (grouping fields). Grouping fields can only be displayed using aggregate functions.
The result of the next query will be a list of item types with their maximum prices.
CHOOSE
,
MAX(Price.Price) AS Price
FROM
GROUP BY
Prices.Nomenclature.TypeNomenclature
RESULTS
Unlike grouping, when using totals, all records are displayed and total rows are already added to them. Grouping displays only generalized records.
Results can be summarized for the entire table (using the keyword "GENERAL"), for several fields, for fields with a hierarchical structure (keywords "HIERARCHY", "ONLY HIERARCHY"). When summing up, it is not necessary to use aggregate functions.
Consider an example similar to the example above using grouping. In this case, the query result will return not only grouped fields, but also detailed records.
CHOOSE
Prices.Nomenclature.Type of Nomenclature AS Type of Nomenclature,
Prices.Price AS Price
FROM
RegisterInformation.PricesNomenclature.SliceLast AS Prices
RESULTS
MAXIMUM(Price)
BY
Type Nomenclature
HAVING
This operator is similar to the WHERE operator, but is only used for aggregate functions. Other fields than those used by this operator must be grouped. The "WHERE" operator is not applicable for aggregate functions.
In the example below, the maximum item prices are selected if they exceed 1000, grouped by item type.
CHOOSE
MAX(Price.Price) AS Price
FROM
RegisterInformation.PricesNomenclature.SliceLast AS Prices
GROUP BY
Prices.Nomenclature.TypeNomenclature
HAVING
MAX(Prices.Price) > 1000
SORT BY
The "ORDER BY" operator sorts the query result. To ensure that records are output in a consistent order, AUTO-ORDER is used. Primitive types are sorted according to the usual rules. Reference types are sorted by GUID.
An example of getting a list of employees sorted by name:
CHOOSE
Employees.Name AS Name
FROM
Directory. Employees AS Employees
SORT BY
Name
AUTO ORDER
Other constructions of the 1C query language
- UNITE- the results of two queries in one.
- UNITE ALL– similar to JOIN, but without grouping identical rows.
- EMPTY TABLE- sometimes used when joining queries to specify an empty nested table.
- PUT- creates a temporary table to optimize complex 1C queries. Such requests are called batch requests.
Query language features
- SUBSTRING truncates a string from a specified position by the specified number of characters.
- YEAR…SECOND allow you to get the selected value of the numeric type. The input parameter is a date.
- BEGINNING OF THE PERIOD AND END OF THE PERIOD are used when working with dates. The period type (DAY, MONTH, YEAR, etc.) is specified as an additional parameter.
- ADDDATE allows you to add or subtract from the date the specified time of a certain type (SECOND, MINUTE, DAY, etc.).
- DATE DIFFERENCE determines the difference between two dates, specifying the type of output value (DAY, YEAR, MONTH, etc.).
- IS NULL replaces the missing value with the specified expression.
- PRESENTATION and PRESENTATIONLINKS get the string representation of the specified field. They are used for any values and only reference values, respectively.
- TYPE, VALUE TYPE are used to determine the type of the input parameter.
- LINK is a logical comparison operator for the attribute value type.
- EXPRESS is used to convert the value to the desired type.
- DATE TIME gets a value of type "Date" from numeric values (Year, Month, Day, Hour, Minute, Second).
- MEANING in a 1C request, it is used to specify predefined values \u200b\u200b- directories, enumerations, plans for types of characteristics. Usage example: " Where LegalIndividual = Value(Enumeration.LegalIndividual.Individual)«.
Query Builder
To create queries with 1C, there is a very convenient built-in mechanism - the query designer. It contains the following main tabs:
- "Tables and fields" - contains the fields to be selected and their sources.
- "Links" - describes the conditions for the CONNECTION construct.
- "Grouping" - contains a description of the constructions of groupings and summarized fields by them.
- "Conditions" - is responsible for the selection of data in the request.
- "Advanced" - additional query parameters, such as the keywords of the "SELECT" command, etc.
- “Joins / Aliases” - the possibilities of joining tables are indicated and aliases are set (the “HOW” construct).
- "Order" - is responsible for sorting the result of queries.
- "Totals" - similar to the "Grouping" tab, but is used for the "TOTALS" construction.
The text of the request itself can be viewed by clicking on the "Request" button in the lower left corner. In this form, it can be corrected manually or copied.
Query Console
To quickly view the result of a query in the "Enterprise" mode, or to debug complex queries, use . The query text is written in it, parameters are set, and its result is shown.
You can download the query console on the ITS disk, or by .
I decided to contribute and describe those features of the language that were not considered in the above articles. The article is aimed at beginner developers.
1. Construction "FROM".
In order to get data from the database, it is not necessary to use the "FROM" construct.
Example: We need to select all information about banks from the bank directory.
Request:
CHOOSE Directory.Banks.*
Selects all fields from the Banks directory. And is similar to the query:
SELECT Banks.* FROM Directory. Banks AS Banks
2. Order data by reference field
When we need to order query data by primitive types: "String", "Number", "Date", etc., then everything is solved by using the "ORDER BY" construct, if you need to order data by a reference field? The reference field is a link, a unique identifier, i.e. Roughly speaking, a certain arbitrary set of characters and the usual ordering may not produce the expected result. For ordering reference fields, the "AUTOORDER" construct is used. To do this, you must first order the data directly by the reference type using the "ORDER BY" construct, and then the "AUTOORDER" construct.
In this case, for documents, ordering will occur in the order "Date-> Number", for directories - by "Main view". If the ordering is not based on reference fields, then using the "AUTOORDER" construct is not recommended.
In some cases, the "AUTOORDER" construct can slow down the sampling process. Similarly, you can rewrite without auto-arranging for documents:
3. Obtaining a textual representation of the reference type. "PRESENTATION" construction.
When you need to display a reference type field for display, for example, the "Bank" field, which is a link to the element of the "Banks" directory, you need to understand that when this field is displayed, a subquery to the "Banks" directory will be automatically executed to get the lookup of the directory. This will slow down the data output. In order to avoid this, it is necessary to use the "REPRESENTATION" construct in the request in order to immediately get the representation of the object and already display it for viewing.
In the data composition system, this mechanism is used by default, but when generating layouts in cells, you should specify the representation of the reference field, and for example, put the link itself in the transcript.
4. Condition for data sampling according to the 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 put the following condition in the request:
SELECT Employee.Name, Employee.Phone AS Phone FROM Directory.Employees AS Employees WHERE Phone LIKE "_-___-___-__-__"
The character "_" is service and replaces any character.
5. Simultaneous use of totals and groupings.
Totals are often used in conjunction with groupings, in which case the aggregate functions in the totals can be omitted.
CHOOSE Services.Organization AS Organization, Services.Nomenclature AS Nomenclature, AMOUNT(Services.Document Amount) AS Document Amount FROM Document.Services AS Services GROUPS BY Services.Organization, Services.Nomenclature SHIE, Organization, Nomenclature
In this case, the request will return almost the same as this request:
CHOOSE Services. Organization AS Organization, Services. Nomenclature AS Nomenclature, Services. Document Amount AS Document Amount FROM Document.
Only the first query will collapse records with the same nomenclature.
6. Dereferencing fields.
Referencing 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 document "Payment", it refers to another table "Organizations", in which the value of the attribute "Administrative Unit" will be received. 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 the field type. In this case, the query will not be optimal. If it is clearly known what type of field is, it is necessary to restrict such fields by type with the construction EXPRESS().
For example, there is an accumulation register "Unallocated payments", where several documents can act as a registrar. In this case, it is incorrect to get the values of the registrar details in this way:
SELECT Unallocated Payments.Registrar.Date, ..... FROM Accumulation Register.Unallocated Payments AS Unallocated Payments
you should limit the type of the composite field logger:
SELECT EXPRESS(Unallocated Payments. Registrar AS Document. Payment). Date, ..... FROM Accumulation Register. Unallocated Payments AS Unallocated Payments
7. Construction "WHERE"
With a left join of two tables, when you impose the "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 Client Directory and for those clients who have a payment document with the attribute value "Organization" = &Organization, display the "Payment" document, for those who do not, do not display it.
The result of the query will return records only for those customers who had payment by organization in the parameter, and will filter out other customers. Therefore, you must first get all payments for "such and such" organization in a temporary table, and then connect with the "Clients" directory with a left join.
SELECT Payment.Reference AS Payment, Payment.Shareholder AS Client PUT topayments FROM Document.Payment AS Payment WHERE Payment.Department = &Department; ////////////////////////////////////////////////// ////////////////////////////// SELECT Clients.Reference AS Client, ISNULL(topayments.Payment, "") AS Payment FROM Directory .Clients AS Clients LEFT JOIN
You can get around this condition in another way. it is necessary to impose the condition "WHERE" directly in the relationship of the two tables. Example:
SELECT Clients.Reference, Payment.Reference FROM Directory.US_Subscribers AS ST_Subscribers LEFT JOIN Document.Payment AS Payment SOFTWARE (Clients.Reference = Payment.Client AND Payment.Client.Name LIKE "Sugar Bag") GROUP BY Clients.Reference, Payment. Link
8. Joins with Nested and Virtual Tables
Subqueries are often necessary to select data according to some condition. If you then use them in conjunction with other tables, then this can critically slow down the execution of the query.
For example, we need to get the Balance Amount for the current date for some clients.
SELECT UnallocatedPayBalances.Customer, UnallocatedPaymentsRemains.AmountBalance FROM (SELECT Clients.Reference AS Reference FROM Directory.Clients AS Clients WHERE Clients.Ref B(&Clients)) AS NestedQuery LEFT JOIN Accumulation register.UnallocatedPayments.Balance AS UnallocatedPayments s Software NestedRequest.Reference = UnallocatedPaymentsRemains.Client
When executing such a query, the DBMS optimizer is likely to make errors when choosing a plan, which will lead to suboptimal query execution. When joining two tables, the DBMS optimizer chooses an algorithm for joining tables based on the number of records in both tables. In the case of a nested query, it is extremely difficult to determine the number of records that the nested query will return. Therefore, instead of nested queries, you should always use temporary tables. So let's rewrite the query.
SELECT Clients.Link AS Link PUT Clients FROM Directory.Clients AS Clients WHERE
Clients.Link B (&Clients) ; ////////////////////////////////////////////////// ////////////////////// SELECT tClients.Reference, UnallocatedPaymentsBalances.SumBalance, FROM tClients AS tClients LEFT JOIN Accumulation Register.UnallocatedPayments.Balances (, Client IN (SELECT tClients.Reference FROM tClients)) AS UnallocatedPaymentsBalances ON tClients.Reference = 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 choose the optimal table join algorithm.
Virtual Tables , allow you to get almost ready-made data for most application tasks. (Slice of the First, Slice of the Last, Residuals, Turnovers, Residuals and Turnovers) The keyword here is virtual. These tables are not physical, but are assembled by the system on the fly, i.e. when receiving data from virtual tables, the system collects data from the final tables of registers, composes, groups and issues to the user.
Those. when you join with a virtual table, you join with a subquery. In this case, the DBMS optimizer may also choose a non-optimal join plan. If the query is not formed quickly enough and the query uses joins in virtual tables, then it is recommended to transfer the access to virtual tables to a temporary table, and then to make a join between two temporary tables. Let's rewrite the previous query.
SELECT Clients.Link AS Link PUT Clients FROM Directory.Clients AS Clients INDEX BY Link WHERE
Clients.Link B (&Clients) ; ////////////////////////////////////////////////// ////////////////////////////// SELECT UnallocatedPayments.AmountBalance, UnallocatedPayments.Customer AS Customer PUT the balances FROM Accumulation Register.UnallocatedPayments.Balances(, Client IN (SELECT tClients.Reference FROM tClients)) AS UnallocatedPaymentsBalances; ////////////////////////////////////////////////// ////////////////////////////// SELECT tClients.Reference, thenRemains.SumRemainder AS SumRemainder FROM tClients AS tClients tClients.Reference = tRemainders.Client
9.Checking the result of the query.
The result of the query execution may be empty; to check for empty values, use the construction:
RequestRes = Request.Execute(); If reQuery.Empty() Then Return; EndIf;
Method Empty() should be used before methods Choose() or Unload(), since it takes time to get the collection.
It is not a discovery for anyone that it is highly undesirable to use queries in a cycle. This can critically affect the operation time of a particular function. It is highly desirable to receive all the data in the request and only then process the data in a loop. But sometimes there are cases when it becomes impossible to take the request out of the loop. In this case, for optimization, you can move the creation of the query outside the loop, and substitute the necessary parameters in the loop and execute the query.
Request = New Request; Query.Text = "SELECT | Clients.Link, | Clients.Date of Birth |FROM | Directory.Clients AS Clients |WHERE | Clients.Link = &Client"; For Each Row FROM TableClients Loop Query.SetParameter("Client", Client); QueryResult = Query.Execute().Select(); EndCycle;
This will save the system from parsing the request in a loop.
11. Construction "HAVING".
A construction that is quite rare in queries. Allows you to impose conditions on the values of aggregate functions (SUM, MINIMUM, AVERAGE, etc.). For example, you need to select only those customers 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 the amount of payment and then impose a condition. The "HAVING" construction will help to avoid this.
SELECT Payment.Customer, SUM(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, all you need to do is 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 a glimpse of how NULL may affect the result of the query. The NULL value is not really a value, and the fact that the value is not defined is unknown. Therefore, any operation on NULL returns NULL, be it addition, subtraction, division, or comparison. The NULL value cannot be compared to the NULL value because we don't know what to compare. Those. both of these comparisons: NULL = NULL, NULL<>NULL is not True or False, that is unknown.
Let's look at an example.
For those customers who have no payments, we need to display the "Attribute" field with the value "No payments". And 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 PUT to payments; ////////////////////////////////////////////////// /////////////////////////////// SELECT Clients.Link AS Client, Payment.Link AS Payment PUT tClientPayment FROM Directory.Clients AS Clients LEFT JOIN Document.Payment AS Payment Software Clients.Link = Payment.Shareholder; ////////////////////////////////////////////////// /////////////////////////////// SELECT tClientPayment.Customer FROM tClientPay AS tClientPayment INTERNAL JOIN topayments AS topayments BY tClientPayment.Payment = topayments. Document
Pay attention to the second temporary table tCustomerPayment. With the left join, I select all clients and all payments for these clients. For those customers who do not have payments, the "Payment" field will be NULL . Following the logic, in the first temporary table "topayments" I designated 2 fields, one of them is NULL, the second is the line "Has no payments". In the third table, I join the "tClientPayment" and "tPayment" tables by the "Payment" and "Document" fields with an inner 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 return us such a connection? And it won't return anything. Because the comparison NULL = NULL does not evaluate to True.
In order for the query to return the expected result to us, we rewrite it:
SELECT "No payments" AS Sign, VALUE(Document. Payment. Empty Reference) AS Document PUT to Payments; ////////////////////////////////////////////////// /////////////////////////////// SELECT Clients.Reference AS Client, ISNULL(Payment.Reference, VALUE(Document.Payment.EmptyReference )) HOW TO PUT tClientPayment FROM Directory.Clients AS Clients LEFT JOIN Document.Payment AS Payment ON Clients.Reference = Payment.Shareholder; ////////////////////////////////////////////////// /////////////////////////////// SELECT tClientPayment.Customer FROM tClientPay AS tClientPayment INTERNAL JOIN topayments AS topayments BY tClientPayment.Payment = topayments. Document
Now, in the second temporary table, we have indicated that if the "Payment" field is NULL, then this field = an empty reference to the payment document. In the First Table, we also replaced NULL with a null reference. Now non-NULL fields are involved in the connection and the query will return the expected result.
All requests contained in the article reflect situations that I would like to consider and nothing more. ABOUT nor can they be crazy or not optimal, the main thing is to reflect the essence of the example.
13. Undocumented design feature "CHOICE WHEN...THEN....END".
In the case when it is necessary to describe the "Conditions" construct in the request, then we use the standard syntax:
SELECT CHOICE 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 the query? Writing a huge construct in a query is ugly and time consuming, so this form of notation above can help us out:
Choice of a month (settlement of_graphicborne assessment. Periodrass account) 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 is “July” then “July” 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 not so cumbersome and is easily perceived.
14. Batch query execution.
In order not to produce requests, you can create one large request, break it into packages and work with it already.
For example, I need to get fields from the "Users" directory: "Date of Birth" and available roles for each user. to unload it into different tabular parts on the form. Of course, you can do this in one query, then you have to iterate over the records or collapse, or you can do this:
SELECT Users.Link AS Name, Users.Date of Birth, Users.Role ENTER Users FROM Directory.Users AS Users; ////////////////////////////////////////////////// /////////////////////////////// SELECT tuUsers.Name, tuUsers.Date of Birth FROM tuUsers AS tuUsers GROUP BY tuUsers.Name, tuUsers. Date of Birth; ////////////////////////////////////////////////// /////////////////////////////// SELECT wUsers.Name, wUsers.Role FROM wUsers AS wUsers GROUP BY wUsers.Name, wUsers. Date of Birth
tPackage = Request.ExecutePackage();
TP_BirthDate = tPackage.Unload();
TP_Roles = tPackage.Unload();
As we can see, the query can be executed in a batch and work with the result as an array. In some cases, very convenient.
15. Conditions in a batch request
For example, we have a batch request, where we first get the fields: "Name, Date of Birth, Code" from the "Users" directory and want to get records with a condition on 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 PUT in Users FROM Directory.Users AS Users; ////////////////////////////////////////////////// ////////////////////////////// SELECT Individuals.Link AS Individual FROM Directory.Individuals AS Individuals
You can apply conditions like this:
WHERE Individuals.Code At (SELECT TueUsers.Code FROM TuUsers) AND Individuals.Name At (SELECT TueUsers.Code FROM TuUUsers) AND Individuals.Date of Birth At (SELECT TueUsers.Date of Birth FROM TuUsers)
And it's possible like this:
WHERE (Individuals.Code, Individuals.Name, Individuals.Date of Birth) AT (SELECT TueUsers.Code, TueUsers.Name, TueUsers.Date of Birth FROM TueUsers)
And be sure to follow the rules.
16. Call Query Builder for "Condition" in Batch Query
When you need 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 "Date of Birth of the Debtor", and if you forgot the name, you will have to exit editing the condition without saving and look at the field name. To avoid this, you can use the following trick.
It is necessary to put brackets after the Construction "B" and leave an empty space (space) between the brackets, select this place and call the query constructor. The constructor will have access to all batch query tables. Reception works both on virtual tables of registers and for the "Conditions" tab. In the latter case, it is necessary to check the "A (arbitrary condition)" checkbox and enter the "F4" editing mode.
The queries are often made up on the go and they just serve to display the "tricks" I've been considering.
I wanted to consider the use of indexes in queries, but it is a painfully extensive topic. I'll put it in a separate article, or later add it here.
upd1. Paragraphs 11,12
upd2. Items 13,14,15,16
Used Books:
Query language "1C:Enterprise 8" - E.Yu. Khrustalev
Professional development in the 1C:Enterprise 8 system.
/
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 structure 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.
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 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, 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 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
Rise of the Tomb Raider technical issues
Best Co-op Games Indie Games Multiplayer
Fallout 4 won't launch on windows xp