/
Implementing data processing

Resolution of results for periodic information registers

Scope: Controlled application, mobile application, normal application.

1.1. For periodic information registers, it is recommended to resolve the results if all the following conditions are fulfilled:

• a large amount of data is expected in the register (for example, justified for the register with the price of the nomenclature; but it does not make sense to register with currency courses);
• the configuration provides frequency requests to sections of the last time and / or to the sections of the first to obtain topical data (i.e., when the period is not specified in the parameters of virtual tables Sleepand Slicing);
• at the same time, the remaining conditions for virtual tables Sleep and Slicing are specified only on the measurement values \u200b\u200b(and separators in mode Independently and together);
• in the limitations of access to data, only measurements are used (and dividers in mode Independently and together).

A complete list of all conditions when the results of the information register are involved in queries, seedocumentation for the 1C platform: Enterprise.

For example, if frequently running register requests are provided in the configuration PricesNactore To obtain current prices of the nomenclature:

Select nomenclature. Article as the article, price of interest. Price like price ,. . . From the directory. Nomenclature as a nomenclature Left connection of the register. PricesNet. Circling-lasting (, viditiones \u003d & Vidzena) as prices Price matter. Nomenclature \u003d nomenclature. Link. . .

that is subject to all other conditions listed above, setting the property Allow the results: cut last Significantly accelerates the execution of such requests, it is counted that the sample will be executed directly from additional tables in which only the last values \u200b\u200b(for the recent cut) are stored and the first values \u200b\u200b(for the sections of the first).

1.2. In addition, alternative options should be considered to revise register requests in such a way that these conditions are performed.

For example, if in some cases the data in the register PricesNactore Recorded by the future date, and when the goods selection, it is always a query to the current date (the date is clearly set in the virtual table parameter Slicing) The results will not speed up the execution of such requests. Since the results are built only for the first and last register entries.

However, if when you open the form of the selection of goods, analyze whether there are registrars with a future date, and if there are no - to perform another request to the last cutout of the latter without installing the date, then such a request will work faster.

2. In all other cases, one should not be resolved for periodic information registers. First of all, if

• most often (always) to the virtual tables of the sections of the first / last periodic information register, queries are executed for a specific period (for example, on the document date).
• in conditions for virtual tables Sleep and Slicing Most often (always) uses subqueries and connections (circulation "through the point" to the fields of associated tables). For example, in this case:

3. It is not necessary to provide in the configuration of a separate mechanism for recalculating the results, since the actualization of the outcome tables is performed automatically with each recording of records in the register.

: Sleep and Slicing Consider working with these virtual tables using 1C. Much more often applied Slicing, so with him and let's start.

The recesses of the latter allows you to get the latest record of the information register to the specified date in the context of measurements. For the cutting table of the last (first) there is an opportunity in brackets to specify two parameters through the comma. In the first parameter, the date is transmitted to which the cut is made (if the parameter does not specify the slice is made to the current date). The second parameter represents the condition in the 1C query language and allows you to install various selections. As a rule, measurements are used in these seborars. All this sounds foggy enough, so without an example you can not do.
So let us have a periodic register of information Price in which prices are stored in the context of goods and suppliers. The frequency of the register is the day.

The register has the following entries

To begin with, we get a cut of the latter without using the parameters, following this request.

Choose the price of a surrender. Period as a period, pricesRext. Sungwar as a product, priceRextrighted. The list is as a supplier, the price of the surrender. Summary as a sum of the registering. Price. Preventive

Since the parameters are not specified, the cut is performed on the current date - 01.02.2017. As a result, we get this table

Here we see that the combination of measurement goods + supplier is unique, i.e. For each combination of register measurements, a record was taken with a maximum date, and the record date is less or equal to the current date.

Suppose we need to do the same but the records we want to get with the date less or equal to January 15, 2017. To do this, you need to change the string in the query with the recent cut table as follows.

From registering. Price. Preventive (& dancer,) as the price of transvest

Before performing the request, it is certainly necessary to transfer the parameter. & Dataseca. Now the query result will look like

And finally imagine that we need to get a cut cut on the same date with the condition that we have the goods Pencil, and supplier Stationery. To do this, specify the second parameter in the query.

From registering. Price. Preventive (& dancer, product \u003d & goods and supplier \u003d & supplier) as the priceRext

As a result, we get only one record

In order not to get confused in all these brackets and commas, it is better to use the request designer. I'll show you on the example of the last query.

After selecting in the designer of the query table with a cut of the latter, you must click on the button Virtual Table Settings and in the window that opens to register

It is not difficult to guess that for cutting the first principle of action is the same except that the first record is selected after the cutting date.

Two virtual tables are available for periodic information registers: Sleep and Slicing Consider working with these virtual tables using 1C requests. Much more often applied Slicing, so with him and let's start.

The recesses of the latter allows you to get the latest record of the information register to the specified date in the context of measurements. For the cutting table of the last (first) there is an opportunity in brackets to specify two parameters through the comma. In the first parameter, the date is transmitted to which the cut is made (if the parameter does not specify the slice is made to the current date). The second parameter represents the condition in the 1C query language and allows you to install various selections. As a rule, measurements are used in these seborars. All this sounds foggy enough, so without an example you can not do.

So let us have a periodic register of information Price in which prices are stored in the context of goods and suppliers. The frequency of the register is a day.

The register has the following entries

To begin with, we get a cut of the latter without using the parameters, following this request.

Choose the price of surrenders. The period is as the period, the price of the last. Product as goods, pricerere-lasting. Supplier as a supplier, price resurrection. The amount is as a sum of the registerings. Price. Circling as the price of translated

Since the parameters are not specified, the cut is performed on the current date - 01.02.2017. As a result, we get this table

Period | Product | Supplier | Sum

01/25/2017 | Pencil | Ltd. "Lesprot" | fifteen

02/01/2017 | Pencil | PJSC "Stationery" | 31.

02/01/2017 | Handle | PJSC "Stationery" | 145.

Here we see that the combination of measurement goods + supplier is unique, i.e. For each combination of register measurements, a record was taken with a maximum date, and the record date is less or equal to the current date.

Suppose we need to do the same but the records we want to get with the date less or equal to January 15, 2017. To do this, you need to change the string in the query with the recent cut table as follows.

From registerings. Price. Circus-lasting (& amp; dancer,) as the price of preserved

Before performing the request, it is certainly necessary to transfer the parameter. & Dataseca. Now the query result will look like

Period | Product | Supplier | Sum

01.01.2017 | Pencil | Ltd. "Lesprot" | 10

01.01.2017 | Pencil | PJSC "Stationery" | 27.

01/03/2017 | Handle | PJSC "Stationery" | 137.

And finally imagine that we need to get a cut cut on the same date with the condition that we have the goods Pencil, and supplier Stationery. To do this, specify the second parameter in the query.

From registerings. Price. Circling-lasting (& amp; dancer, product \u003d & amp; product and supplier \u003d & amp; supplier) as the price translated

As a result, we get only one record

Period | Product | Supplier | Sum

01.01.2017 | Pencil | PJSC "Stationery" | 27.

In order not to get confused in all these brackets and commas, it is better to use the request designer. I'll show you on the example of the last query.
After selecting in the designer of the query table with a cut of the latter, you must click on the button Virtual Table Settings and in the window that opens to register

It is not difficult to guess that for cutting the first principle of action is the same except that the first record is selected after the cutting date.

  43
  NULL - missing values. Do not confuse with zero value! NULL is not a number that is not equal to the space, an empty link, undefined. NULL - type-forming value, i.e. There is a NULL type and the only meaning of this type. NULL ...   26
  To form and execute queries to the database tables in the 1C platform, a special object programming language object is used. This object is created by calling a new request. Request is convenient ...   18
  The article presents useful techniques when working with requests 1C V.8.2, as well as information that are not so well known for the query language. I do not strive to give a full description of the language of requests, but I want to stop only on ...   13
  Like - the operator check the string on the like pattern. Analog Like in SQL. The operator like that allows you to compare the value of the expression specified to the left of it, with the string of the template specified on the right. The value of the expression ...

Sometimes you need a request to get from a periodic information information data for several dates immediately. A typical example is working with currency courses. Consider the algorithm to solve this task on the example.

Formulation of the problem

In the database, a document "Implementation of the service" was created, in the header of which there is a props "currency". The request is required for each document to receive an actual currency rate from the header to the document date. Storage of currency exchange rates are carried out in the periodic register of "Carsivalyut".
The decision "in the forehead" of this problem could be a request in the cycle: receiving all documents with their dates and currency and in the sample appeal to the virtual table of the release of the last register "Kursivalyut". But because The request in the cycle is "bad", try to implement the task with one request.

Decision

To solve the problem, we will use the fact that the tables are in the request can be connected not only to the equality of fields.

CHOOSE RealizatsiyaTovarovUslug.Ssylka, RealizatsiyaTovarovUslug.Valyuta, MAX (KursyValyut.Period) as a period VTPeriodyUstanovkiKursov PUT OUT HOW Dokument.RealizatsiyaTovarovUslug RealizatsiyaTovarovUslug left join RegistrSvedeniy.KursyValyut HOW TO KursyValyut RealizatsiyaTovarovUslug.Valyuta \u003d KursyValyut.Valyuta And RealizatsiyaTovarovUslug.Data\u003e \u003d KursyValyut.Period grouped According to the implementation of the service. Link, implementing the streets. Svaluta; ////////////////////////////////////////////// // ////////////////////////////////////////////////////////////////////////////////////////// /// // // Select TveriodoresOstorekurs. Link, WTPeriodoresOstrovikurstovs.Valuta, Kursivalyut.KURS from the WTPeriodorestorecars as a cluttering establishers of the left connection of the registerings. Kashivalyuta as coasting vaporiodine settings. Period \u003d coarsevalyut.period and clutterings MountingCurses.Valuta \u003d coarsevalyut.Valuta

Procedure in the query:

1. Obtaining a currency exchange rate for each document. Documents are connected to the Physical Table "Kursivalyut". Here you should pay attention to the connection conditions. Currencies must be equal, and the date of the document\u003e \u003d the period of the information register.
   As a result of such a connection, a set of strings satisfying the condition will be obtained for each document: all records of the currency exchange rate set not later than the date of the document.
   The final stage will be a grouping of strings to obtain a maximum course period. As a result, for each document, the required period of installation of the course for the desired currency will be obtained (the maximum date of installation of the currency exchange rate, but not more than the date of the document). The result is placed in a temporary table of cluttering systems.
2. Obtaining a course. The temporary table of the charges is connected to the "Krasvalyut" physical table. The connection occurs according to the currency of the document and the period of the course setting defined in the second temporary table.