Programming in 1C 8.2 query language. Creating and working with queries (for beginners)

Query language 1C 8.3 for beginner programmers: installation of 1C, familiarization with the database and the first query

If you haven't read the introduction to the lessons, please read it: .

Installing 1C and getting to know the database

To complete the practical part of the lessons we will need three things:

Platform 1C 8.3 (not lower than 8.3.4.482).
Actually, the database itself from which we will extract data with our queries.
Special processing “Request Console” so that we do not have to go into the configurator every time to execute requests.

Platform installation

If you have 1C version 8.3 installed, use it. If not, download and install the educational version, which 1C produces specifically for educational purposes.

For this:

Unpack the archive downloaded from the link into a separate folder on your desktop:
- link to download from Dropbox (option No. 1)
- download link from Mail.ru (option No. 2)
Run the "setup.exe" file.
Click Next, Next, Install.
Wait for the installation to complete.

The following shortcut should appear on your desktop:

Installing and connecting the database

For all the lessons in this series we will use the Gastronom database I prepared. Download it from the following, unpack it and add it to the list of databases.

Processing "Query Console"

We will perform all requests in our lessons in 1C:Enterprise mode. To do this, we need the “Query Console” processing intended for this purpose. Download it here and extract it to your desktop.

Finally, the workplace is set up and now is the time to open our Gastronom database in 1C:Enterprise mode:

User: Administrator.
The password is empty.

Our database already contains certain data. You can verify this if you click on the base objects from the top panel menu (Food, Flavors, Colors, and so on):

Types of objects

Food, Flavors, Colors, Clients, Customer orders... - all these are application database objects. They are called applied because they serve to solve some applied problem.

For example, object Food serves to store information about what kind of food there is, and the object Customer orders stores information about orders that were made by our customers.

For the sake of lessons, for now it is enough for us to understand that application database objects come in two types: Directories And Documentation.

Directories

Directories allow you to store data that has the same structure and list character. This could be, for example, a list of employees, a list of goods, a list of suppliers or customers.

Documentation

Documents allow you to store information about completed business transactions in the application solution. operations or events that occurred in the “life” of the enterprise in general. These could be, for example, invoices, employment orders, invoices, payment orders, and so on. Each document, unlike directories, is characterized by a number, date and time.

Who's who in our database

Now, based on the information given above, try to divide the objects of our database into Directories And Documentation.

Take your time to look back. Form your thoughts first.

View answer

Real names of our objects

Those object names ( Food, Flavors, ... , Customer order and so on), which we see in 1C:Enterprise mode are not always real. Most often, they look so that they are pleasant for the user to read.

But to write queries, we need to know the real name of the object. How can you find out?

For example, if we set out to write a query to obtain data on customer orders, could we use the name “Customer Order”, which is displayed in 1C:Enterprise mode? Of course not, because it's not real. The real name of the object can be found in 1C: Configurator mode.

Go to the database configurator and execute from the main menu "Configuration" - "Open configuration":

These are our objects (and here, by the way, it’s very clear who Directory, who Document) and their real names.

So, what is the real name of the Customer Order object?

View answer

Customer order

Our first request

Let's finally write our "Hello, World" for requests. Of course, the query language, like any other language, has its own rules, but we will move on to their consideration in the next lesson. For now, just take the following request text as given:

SELECT * FROM Directory. Food

The request is very simple and, in principle, intuitive. Try to figure it out yourself and only then look at the explanation.

See explanation

I decided to make my contribution and describe those features of the language that were not discussed in the above articles. The article is aimed at beginner developers.

1. “IZ” design.

In order to obtain data from the database, it is not at all necessary to use the “FROM” construction.
Example: We need to select all information about banks from the banks directory.
Request:

SELECT Directory.Banks.*

Selects all fields from the Banks directory. And is similar to the request:

SELECT Banks.* FROM Directory.Banks AS Banks

2. Ordering data by reference field

When we need to organize query data by primitive types: "String", "Number", "Date", etc., then everything is solved by using the "ORDER BY" construct if you need to order the data by a reference field? The reference field is a link, a unique identifier, i.e. Roughly speaking, some arbitrary set of characters and ordinary ordering may produce a result that is not entirely expected. To order reference fields, the "AUTO ORDER" construction is used. To do this, you must first order the data directly by the reference type using the "ORDER BY" construct, and then the "AUTO ORDER" construct.

In this case, for documents the ordering will occur in the order "Date->Number", for reference books in the "Main View". If the ordering does not occur by reference fields, then using the "AUTO ORDER" construction is not recommended.

In some cases, the "AUTO ORDER" construct can slow down the selection process. In a similar way, you can rewrite without auto-ordering for documents:

3.Obtaining a text representation of a reference type. "PRESENTATION" design.

When you need to display a field of a reference type, for example, the "Bank" field, which is a link to an element of the "Banks" directory, you need to understand that when displaying this field, a subquery to the "Banks" directory will be automatically executed to obtain a view of the directory. This will slow down the data output. In order to avoid this, you need to use the “PREPRESENTATION” construction in the request in order to immediately obtain a representation of the object and then display it for viewing.

In the data composition system, this mechanism is used by default, but when creating layouts in cells, you should specify the representation of the reference field, and, for example, place the link itself in the transcript.

4. Condition for sampling data according to a template.

For example, you need to get mobile phones of employees of the form (8 -123- 456-78-912). To do this, you need to set the following condition in the request:

SELECT Employee.Name, Employee.Phone AS Phone FROM Directory.Employees AS Employees WHERE Phone LIKE "_-___-___-__-__"

The "_" character is a service character and replaces any character.

5. Simultaneous use of totals and groupings.

Totals are often used in conjunction with groupings; in this case, aggregate functions may not be specified in the totals.

SELECT Provision of Services.Organization AS Organization, Provision of Services.Nomenclature AS Nomenclature, SUM(Provision of Services.Amount of Document) AS Sum of Document FROM Document.Provision of Services AS Provision of Services GROUP BY Provision of Services.Organization, Provision of Services.Nomenclature RESULTS BY GENERAL, Organization, nomenklatura

In this case, the query will return almost the same as the following query:

SELECT Provision of Services.Organization AS Organization, Provision of Services.Nomenclature AS Nomenclature, Provision of Services.Amount of Document AS Amount of Document FROM Document.Provision of Services AS Provision of Services RESULTS AMOUNT (Amount of Document) BY GENERAL, Organization, Nomenclature

Only the first query will collapse records with the same nomenclature.

6. Dereferencing fields.

Referring to fields through a dot is called the reference field dereferencing operation. For example Payment.Organization.Administrative Unit. In this case, in the reference field "Organization" of the "Payment" document, it refers to another table "Organizations", in which the value of the "Administrative Unit" attribute will be obtained. It is important to understand that when accessing fields through a dot, the platform implicitly creates a subquery and joins these tables.

Request:

Can be represented as:

SELECT Payment.Link, Payment.Organization, Payment.Organization, Organizations. AdministrativeUnit FROM Document.Payment AS Payment LEFT JOIN Directory.Organizations AS Organizations Software Payment.Organization = Organizations.Link

When dereferencing reference fields of a composite type, the framework attempts to create implicit joins to all tables that are part of that field's type. In this case, the query will not be optimal. If it is clearly known what type of field it is, it is necessary to limit such fields by type with a construct EXPRESS().

For example, there is an accumulation register “Undistributed payments”, where several documents can act as a registrar. In this case, it is incorrect to obtain the values of the registrar details in this way:

SELECT UnallocatedPayments.Register.Date, ..... FROM RegisterAccumulation.UnallocatedPayments AS UnallocatedPayments

you should restrict the type of the composite field to logger:

SELECT EXPRESS(UnallocatedPayments.Register AS Document.Payment).Date, ..... FROM RegisterAccumulation.UnallocatedPayments AS UnallocatedPayments

7. Construction "WHERE"

With a left join of two tables, when you impose a “WHERE” condition on the right table, we will get a result similar to the result with an inner join of tables.

Example. It is necessary to select all Clients from the Clients Directory and for those clients who have a payment document with the value of the attribute "Organization" = &Organization, display the document "Payment", for those who do not, do not display it.

The result of the query will return records only for those clients who had payment by organization in the parameter, and will filter out other clients. Therefore, you must first receive all payments for “such and such” organization in a temporary table, and then connect it to the “Clients” directory using a left join.

SELECT Payment.Link AS Payment, Payment.Shareholder AS Client PLACE toPayments FROM Document.Payment AS Payment WHERE Payment.Branch = &Branch; //////////////////////////////////////////////// ///////////////////////////// SELECT Clients.Link AS Client, ISNULL(tPayment.Payment, "") AS Payment FROM Directory .Clients AS Clients LEFT CONNECTION topayments AS topayments SOFTWARE Clients.Link = topayments.Client

You can get around this condition in another way. It is necessary to impose a "WHERE" condition directly on the relationship between the two tables. Example:

SELECT Clients.Link, Payment.Link FROM Directory.US_Subscribers AS US_Subscribers LEFT CONNECTION Document.Payment AS Payment Software (Clients.Link = Payment.Client AND Payment.Client.Name LIKE "Sugar Packet") GROUP BY Clients.Link, Payment. Link

8. Joins with Nested and Virtual Tables

Nested Queries often necessary to retrieve data based on some condition. If you then use them in conjunction with other tables, this can critically slow down the execution of the query.

For example, we need to get the Balance Amount as of the current date for some clients.

SELECT UnallocatedPaymentsBalances.Customer, UnallocatedPaymentsBalances.AmountBalance FROM (SELECT Clients.Link AS Link FROM Directory.Clients AS Clients WHERE Clients.Link IN(&Clients)) AS NestedQuery LEFT JOIN RegisterAccumulations.UnallocatedPayments.Balances AS UnallocatedPayments BY Nested nyRequest.Link = UnallocatedPaymentsBalances.Customer

When executing such a query, the DBMS optimizer may make errors when choosing a plan, which will lead to suboptimal execution of the query. When joining two tables, the DBMS optimizer selects a table joining algorithm based on the number of records in both tables. If there is a nested query, it is extremely difficult to determine the number of records that the nested query will return. Therefore, you should always use temporary tables instead of nested queries. So let's rewrite the request.

SELECT Clients.Link AS Link PLACE tClients FROM Directory.Clients AS Clients WHERE
Clients.Link B (&Clients) ; //////////////////////////////////////////////// ///////////////////////////// SELECT tClients.Link, UnallocatedPaymentsRemains.AmountRemaining, FROM tClients AS tClients LEFT JOIN RegisterAccumulations.UnallocatedPayments.Balances (, Client IN (SELECT tClients.Link FROM tClients)) AS UnallocatedPaymentsBalances tClients.Link = UnallocatedPaymentsBalances.Clients

In this case, the optimizer will be able to determine how many records the temporary table tClients uses and will be able to select the optimal algorithm for joining tables.

Virtual tables , allow you to obtain practically ready-made data for most applied tasks. (Slice of the First, Slice of the Last, Remains, Turnovers, Remains and Turnovers) The key word here is virtual. These tables are not physical, but are compiled by the system on the fly, i.e. When receiving data from virtual tables, the system collects data from the final register tables, assembles, groups and issues it to the user.

Those. When connecting to a virtual table, a connection is made to a subquery. In this case, the DBMS optimizer may also choose a non-optimal connection plan. If the query is not generated quickly enough and the query uses joins in virtual tables, then it is recommended to move the access to the virtual tables to a temporary table, and then make a join between two temporary tables. Let's rewrite the previous request.

SELECT Clients.Link AS Link PLACE tClients FROM Directory.Clients AS Clients INDEX BY Link WHERE
Clients.Link B (&Clients) ; //////////////////////////////////////////////// ///////////////////////////// SELECT UnallocatedPayments.AmountBalance, UnallocatedPayments.Client AS Client PLACE balances FROM RegisterAccumulations.UnallocatedPayments.Balances(, Client B (SELECT tClients. Link FROM tClients)) AS UnallocatedPaymentsBalances; //////////////////////////////////////////////// ///////////////////////////// SELECT tClients.Link, toRemainders.AmountRemaining AS AmountRemaining FROM tClients AS tClients LEFT JOIN toRemainders AS Remainders BY tClients.Link = tRemainings.Client

9.Checking the result of the request.

The result of the query may be empty; to check for empty values, use the following construct:

ResRequest = Request.Execute(); If resQuery.Empty() Then Return; endIf;

Method Empty() should be used before methods Choose() or Unload(), since retrieving the collection takes time.

It is not a revelation to anyone that it is extremely undesirable to use queries in a loop. This can critically affect the operating time of a particular function. It is highly desirable to receive all the data in the request and then process the data in a loop. But sometimes there are cases when it becomes impossible to move the request outside the loop. In this case, for optimization, you can move the creation of the query outside the loop, and in the loop, substitute the necessary parameters and execute the query.

QueryResult = Query.Execute().Select(); EndCycle;

This will save the system from syntax checking the request in a loop.

11. Construction "HAVING".

SELECT Payment.Customer, AMOUNT(Payment.Amount) AS Amount FROM Document.Payment AS Payment WHERE MONTH(Payment.Date) = 9 GROUP BY Payment.Customer HAVING AMOUNT(Payment.Amount) > 13000

In the constructor, to do this, just go to the “Conditions” tab, add a new condition and check the “Custom” checkbox. Then just write Amount(Payment.Amount) > 13000

12. NULL value

I will not describe here the principles of three-valued logic in the database; there are many articles on this topic. Just briefly about how NULL may affect the result of the query. The value NULL is not actually a value, and the fact that the value is undefined is unknown. Therefore, any operation with NULL returns NULL, be it addition, subtraction, division or comparison. A NULL value cannot be compared to a NULL value because we don't know what to compare. Those. both of these comparisons are: NULL = NULL, NULL<>NULL is not True or False, it is unknown.

Let's look at an example.

For those clients who do not have payments, we need to display the “Sign” field with the value “No payments”. Moreover, we know for sure that we have such clients. And in order to reflect the essence of what I wrote above, let’s do it this way.

SELECT "No payments" AS Attribute, NULL AS Document PLACE topayments; //////////////////////////////////////////////// //////////////////////////// SELECT Clients.Link AS Client, Payment.Link HOW Payment PUT tClientPayment FROM Directory.Clients AS Clients LEFT CONNECTION Document.Payment AS Payment Software Clients.Link = Payment.Shareholder; //////////////////////////////////////////////// ///////////////////////////// SELECT tClientPayment.Client FROM tClientPayment AS tClientPayment INTERNAL JOIN tPayment AS tTopay BY tClientPayment.Payment = tPayment. Document

Pay attention to the second temporary table tClientPayment. With the left join I select all clients and all payments for these clients. For those clients who do not have payments, the “Payment” field will be NULL. Following the logic, in the first temporary table “topayments” I designated 2 fields, one of them is NULL, the second is the line “Does not have payments”. In the third table, I connect the tables “tClientPayment” and “tPayment” using the fields “Payment” and “Document” with an internal join. We know that in the first table the “Document” field is NULL, and in the second table, those who do not have payments in the “Payment” field are also NULL. What will such a connection return to us? But it won't return anything. Because the comparison NULL = NULL does not evaluate to True.

In order for the request to return the expected result, let’s rewrite it:

SELECT "No payments" AS Attribute, VALUE(Document.Payment.EmptyLink) AS Document PLACE toPayments; //////////////////////////////////////////////// ///////////////////////////// SELECT Clients.Link AS Client, ISNULL(Payment.Link, VALUE(Document.Payment.EmptyLink )) HOW Payment PUT tClientPayment FROM Directory.Clients AS Clients LEFT CONNECTION Document.Payment AS Payment BY Clients.Link = Payment.Shareholder; //////////////////////////////////////////////// ///////////////////////////// SELECT tClientPayment.Client FROM tClientPayment AS tClientPayment INTERNAL JOIN tPayment AS tTopay BY tClientPayment.Payment = tPayment. Document

Now, in the second temporary table, we have indicated that if the “Payment” field is NULL, then this field = an empty link to the payment document. In the First table we also replaced NULL with an empty reference. Now the connection involves non-NULL fields and the request will return the expected result.

All requests contained in the article reflect the situations that I would like to consider and nothing more. ABOUT They may not be delusional or suboptimal, the main thing is that they reflect the essence of the example.

13. An undocumented feature of the "CHOICE WHEN...THEN...END" design.

In the case when it is necessary to describe the “Conditions” construction in the request, we use the standard syntax:

SELECT SELECTION WHEN Users.Name = "Vasya Pupkin" THEN "Our favorite employee" ELSE "We don't know this" END AS Field1 FROM Directory.Users AS Users

But what if, for example, we need to get the name of the month in a request? Writing a huge construction in a request is ugly and time-consuming, so this form of writing above can help us out:

SELECT MONTH(US_CalculationConsumption_TurnoverSchedule.CalculationPeriod) WHEN 1 THEN "January" WHEN 2 THEN "February" WHEN 3 THEN "March" WHEN 4 THEN "April" WHEN 5 THEN "May" WHEN 6 THEN "June" WHEN 7 THEN "July" WHEN 8 THEN "August" WHEN 9 THEN "September" WHEN 10 THEN "October" WHEN 11 THEN "November" WHEN 12 THEN "December" END AS A Month

Now the design looks less cumbersome and is easy to understand.

14. Batch execution of a request.

In order not to multiply requests, you can create one large request, split it into packages and work with it.
For example, I need to get the following fields from the "Users" directory: "Date of Birth" and the available roles for each user. upload this to different tabular parts on the form. Of course, you can do this in one request, then you will have to iterate through the records or collapse them, or you can do this:

SELECT Users.Link AS Full Name, Users.Date of Birth, Users.Role PUT vtUsers FROM Directory.Users AS Users; //////////////////////////////////////////////// ///////////////////////////// SELECT tueUsers.Full name, tueUsers.Date of Birth FROM tueUsers AS tueUsers GROUP BY tueUsers.full name, tueUsers. Date of Birth; //////////////////////////////////////////////// ///////////////////////////// SELECT wUsers.Full Name, wUsers.Role FROM wUsers AS wUsers GROUP BY wUsers.Full Name, wUsers. Date of Birth

tPackage = Request.ExecutePackage();

TP_BirthDate = tPackage.Upload();
TP_Roles = tPackage.Unload();

As we can see, the query can be executed in a batch and the result can be processed as an array. In some cases it is very convenient.

15. Conditions in a batch request

For example, we have a batch request, where first we get the fields: “Name, Date of Birth, Code” from the “Users” directory and want to get records with conditions for these fields from the “Individuals” directory.

SELECT Users.Individual.Name AS Name, Users.Individual.Date of Birth AS Date of Birth, Users.Individual.Code AS Code PLACE vtUsers FROM Directory.Users AS Users; //////////////////////////////////////////////// ///////////////////////////// SELECT Individuals. Link AS Individual FROM Directory. Individuals AS Individuals

You can impose conditions like this:

WHERE Individuals.Code IN (SELECT vtUsers.Code FROM vtUsers) AND Individuals.Name IN (SELECT vtUsers.Code FROM vtUsers) AND Individuals.BirthDate IN (SELECT vtUsers.DateBirth FROM tvUsers)

And you can do it like this:

WHERE (Individuals.Code, Individuals.Name, Individuals.Date of Birth) IN (SELECT tueUsers.Code, tueUsers.Name, tueUsers.Date of Birth FROM tueUsers)

Moreover, it is necessary to maintain order.

16. Calling the query builder for “condition” in a batch request

When it is necessary to impose a condition, as in the example above, you can forget how this or that field is called in the virtual table.
For example, you need to impose a condition on the "Date of Birth" field, and in the virtual table this field is called "Debtor's Date of Birth", and if you forget the name, you will have to exit editing the condition without saving and look at the name of the field. In order to avoid this, you can use the following technique.

It is necessary to put brackets after Construction “B” and leave an empty space (space) between the brackets, select this space and call the query constructor. The designer will have access to all tables of the batch query. The technique works both on virtual register tables and on the “Conditions” tab. In the latter case, you need to check the "P (arbitrary condition)" box and enter the editing mode "F4".

The queries were often made up on the fly and they simply serve to illustrate the “techniques” that I was considering.

I wanted to look at the use of indexes in queries, but this is a very broad topic. I’ll put it in a separate article, or I’ll add it here later.

upd1. Points 11,12
upd2. Points 13,14,15,16

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

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

1C Query Language Functions

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

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

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

Usage example:

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

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

Usage example:

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

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

Syntax: VALUE(<Имя>)

Usage example:

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

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

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

Usage example:

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

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

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

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

Usage example:

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

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

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

Usage example:

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

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

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

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

Usage example:

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

Constructs in the 1C query language

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

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

Usage example:

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

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

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

Usage example:

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

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

Usage example:

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

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

Row pattern options:

% - a sequence containing any number of arbitrary characters.

One arbitrary character.

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

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

Usage example:

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

Syntax: ALLOWED is written after the keyword SELECT

Usage example:

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

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

Syntax: VARIOUS is written after the keyword SELECT

Usage example:

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

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

Usage example:

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

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

Syntax: FIRST<число>

Usage example:

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

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

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

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

Usage example:

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

Requests are designed to extract and process information from the database to provide it to the user in the required form. Processing here means grouping fields, sorting rows, calculating totals, etc. You cannot change data using queries in 1C!

The request is executed as per the given instructions − request text. The request text is compiled in accordance with the syntax and rules query language. The 1C:Enterprise 8 query language is based on the standard SQL, but has some differences and extensions.

Scheme of working with a request

The general scheme of working with a request consists of several successive stages:

Creating a Request object and setting the request text;
Setting request parameters;
Executing a request and getting the result;
Bypassing the request result and processing the received data.

1. Object Request has the property Text, to which you need to assign the request text.

// Option 1
Request = New Request;
Request . Text =
"CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM

|WHERE
;

// Option 2
Request = New Request("CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM
| Register of Information.Currency Rates AS Currency Rates
|WHERE
| Currency rates.Currency = &Currency");

2. Setting the parameter values is carried out using the method SetParameter(< Имя>, < Значение>) . Parameters in the request text are indicated by the symbol “ & " and are usually used in selection conditions (WHERE section) and in virtual table parameters.

Request);

3. After assigning the text and setting the parameters, the request must be executed and the execution result obtained. Execution is performed by the Execute() method, which returns an object Query Result. From the query result you can:

get a selection using the Select method (< ТипОбхода>, < Группировки>, < ГруппировкиДляЗначенийГруппировок>) ;
upload values to a value table or value tree using the Upload method (< ТипОбхода>) .

// Receive a sample

Sample = Query Result. Choose();

// Getting a table of values
RequestResult = Request. Run();
Table = Query Result. Unload();

4. You can bypass the query result selection using a loop:

Bye Sample.Next() Loop
Report(Selection.Course);
EndCycle;

A complete example of working with a request might look like this:

// Stage 1. Creating a request and setting the request text
Request = New Request;
Request . Text =
"CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM
| Register of Information.Currency Rates AS Currency Rates
|WHERE
| Currency rates.Currency = &Currency";

// Stage 2. Setting parameters
Request . SetParameter("Currency" , SelectedCurrency);

// Stage 3. Executing the query and getting the sample
RequestResult = Request. Run();
Sample = Query Result. Choose();

// Traversing the selection
Bye Sample.Next() Loop
Report(Selection.Course);
EndCycle;

Composition of the request text

The request text consists of several sections:

Request Description— list of selectable fields and data sources;
Merging queries— expressions “UNITE” and “UNITE ALL”;
Organizing results— the expression “ORDER BY...”;
Auto-order— the expression “AUTO ORDERING”;
Description of results- the expression “RESULTS ... BY …”.

Only the first section is mandatory.

Temporary tables and batch queries

1C query language supports the use temporary tables— tables obtained as a result of executing a query and stored on a temporary basis.

You can often encounter a situation where you need to use not database tables as the source of a query, but the result of executing another query. This problem can be solved using nested queries or temporary tables. The use of temporary tables allows you to simplify the text of a complex query by dividing it into its component parts, and also, in some cases, speed up query execution and reduce the number of locks. To work with temporary tables, use the object TimeTable Manager. A temporary table is created using the PLACE keyword followed by the name of the temporary table.

ManagerVT = New TemporaryTablesManager;
Request = New Request;
Request . ManagerTemporaryTables = ManagerVT;

Request . Text =
"CHOOSE
| Currencies.Code,
| Currencies.Name
|Place in Currency
|FROM
| Directory.Currencies AS Currencies";

RequestResult = Request. Execute();

To use the VTVcurrency temporary table in other queries, you need to assign a common temporary table manager to these queries—VT Manager.

Batch request is a request that contains several requests separated by the “;” character. When executing a batch query, all queries included in it are executed sequentially, and the results of all temporary tables are available to all subsequent queries. Explicitly assigning a temporary table manager to batch queries is not necessary. If a temporary table manager is not assigned, then all temporary tables will be deleted immediately after the query is executed.

For batch queries, the ExecuteBatch() method is available, which executes all queries and returns an array of results. Temporary tables in a batch query will be represented by a table with one row and one column “Count”, which stores the number of records. To debug batch requests, you can use the method Execute Batch WITH INTERMEDIATE DATA() : It returns the actual contents of temporary tables, not the number of records.

// Example of working with a batch request
Request = New Request;
Request . Text =
"CHOOSE
| Currencies.Name
|FROM
| Directory.Currencies AS Currencies
|;
|SELECT
| Nomenclature.Name
|FROM
| Directory. Nomenclature AS Nomenclature";

Batch Result = Request. ExecuteBatch();

TZCurrencies =PacketResult[ 0 ]. Unload();
TZNomenclature = Package Result[ 1 ]. Unload();

// An example of using temporary tables in a batch request
Request = New Request;
Request . Text =
"CHOOSE
| Products. Link HOW TO Product
|PLACE VTProducts
|FROM
| Directory.Nomenclature HOW Products
|WHERE
| Products.Manufacturer = &Manufacturer
|;
|SELECT
| VTTProducts.Product,
| Vocational school. Quantity,
| Vocational school.Price,
| Vocational school.Link AS DocumentReceipts
|FROM
| VT Products AS VT Products
| LEFT CONNECTION Document. Receipt of Goods and Services. Goods AS PTU
| Software VTProducts.Product = PTU.Nomenclature";

Request . SetParameter( "Manufacturer", Manufacturer);

RequestResult = Request. Run();
Sample = Query Result. Choose();

Bye Sample.Next() Loop

EndCycle;

Virtual tables

Virtual tables- these are tables that are not stored in the database, but are generated by the platform. At their core, these are nested queries against one or more physical tables executed by the platform. Virtual tables receive information only from registers and are mainly intended for solving highly specialized problems.

The following virtual tables exist (possible parameters are indicated in parentheses):

For information registers:
- SliceFirst(<Период>, <Условие>) — the earliest records for the specified date;
- SliceLast(<Период>, <Условие>) — the latest records for the specified date;
For accumulation registers:
- Leftovers(<Период>, <Условие>) — balances as of the specified date;
- Revolutions(<НачалоПериода>, <КонецПериода>, <Периодичность>, <Условие>) - Period transactions;
- RemainsAndTurnover(<НачалоПериода>, <КонецПериода>, <Периодичность>, <МетодДополненияПериодов>, <Условие>) — balances and turnover for the period;
For accounting registers:
- Leftovers(<Период>, <УсловиеСчета>, <Субконто>, <Условие>) — balances as of the specified date by account, dimensions and sub-accounts;
- Revolutions(<НачалоПериода>, <КонецПериода>, <Периодичность>, <УсловиеСчета>, <Субконто>, <Условие>, <УсловиеКорСчета>, <КорСубконто>) — turnover for the period in the context of accounts, measurements, corr. accounts, subconto, cor. subconto;
- RemainsAndTurnover(<НачалоПериода>, <КонецПериода>, <Периодичность>, <МетодДополненияПериодов>, <УсловиеСчета>, <Субконто>, <Условие>) — balances and turnover in the context of accounts, measurements and sub-accounts;
- TurnoverDtKt(<НачалоПериода>, <КонецПериода>, <Периодичность>, <УсловиеСчетаДт>, <СубконтоДт>, <УсловиеСчетаКт>, <СубконтоКт>, <Условие>) — turnover for the period by account Dt, account Kt, Subconto Dt, Subconto Kt;
- MovementsSubconto(<НачалоПериода>, <КонецПериода>, <Условие>, <Порядок>, <Первые>) — movements together with subconto values;
For calculation registers:
- Base(<ИзмеренияОсновногоРегистра>, <ИзмеренияБазовогоРегистра>, <Разрезы>, <Условие>) — basic data of the calculation register;
- DataGraphics(<Условие>)—graph data;
- ActualActionPeriod(<Условие>) is the actual period of validity.

When working with virtual tables, you should apply selections in the parameters of virtual tables, and not in the WHERE condition. The query execution time greatly depends on this.

Query constructor

To speed up the input of query texts, the platform has special tools: Query constructor And Query constructor with result processing. To call constructors, you need to right-click and select the required item:

Constructors can also be called from the main menu Text.

Using the query builder, the programmer can interactively construct the query text. To do this, select the necessary tables and fields with the mouse, establish relationships, groupings, totals, etc. This approach saves time and eliminates possible errors. As a result of its work, the query constructor generates the query text.

The query constructor with result processing, in addition to generating the query text, creates a ready-made code fragment for receiving and processing data.

RequestSchema object

The platform allows you to programmatically create and edit the request text using the object Request Schema. An object has a single property Batch of Requests, in which the object stores the properties of all queries currently being edited. The RequestSchema object supports the following methods:

SetQueryText(< Текст>) — fills the Query Packet property based on the submitted request text;
GetQueryText() - returns the request text generated based on the Request Package property;

FindParameters() - returns the request parameters.

Let's look at an example of working with the RequestSchema object. To programmatically generate the request text

SORT BY
Currencies.Code

The embedded language code might look like this:

RequestScheme = New RequestScheme;
Package 1 = RequestScheme. RequestBatch[ 0 ];
Operator1 = Package1. Operators[ 0 ];
// adding source
RegisterTable = Operator1. Sources. Add( "Directory.Currencies", "Currencies" );
// adding fields
FieldLink = Operator1. SelectableFields. Add("Currencies.Link" , 0 );
FieldCode = Operator1. SelectableFields. Add("Currencies.Code", 1);
// specifying field aliases
Package 1 . Columns[ 0 ]. Alias = "Currency" ;
Package 1 . Columns[ 1 ]. Alias = "Code" ;
// adding a condition
Operator1 . Selection. Add( "NOT FlagDeletion");
// add ordering
Package 1 . Order. Add(FieldCode);
RequestText = RequestScheme. GetQueryText();

Queries are one of the basic mechanisms of 1C:Enterprise, along with a built-in language that allows you to read and process data stored in the database. To compose queries, 1C:Enterprise uses its own language based on SQL.

Book Query Language 1C:Enterprise 8 will help beginner developers who are not familiar with SQL master . The book will also be useful for those who have experience writing SQL queries in other development environments, since the 1C:Enterprise query language contains a significant number of extensions focused on the specifics of financial and economic tasks.

The book covers a significant number of practical examples. To create the examples, version 8.3.3.687 of the 1C:Enterprise platform was used.

The attached CD contains demo configurations illustrating these examples and a training version of the 1C:Enterprise 8 platform.

Additional materials:

The application contains a training version of the 1C:Enterprise 8.2 platform, with which you can independently reproduce the example described in the book. All steps are described in detail, so anyone can repeat them independently on their computer.

Download the materials and training version on the page, open the archive and follow the instructions for installing the Internet conference for beginner developers.

Chapter 1. Query mechanism

How data is stored in 1C:Enterprise
Source tables for queries
Query language "1C:Enterprise"

Real tables
Virtual tables

Query language "1C:Enterprise"

General query execution scheme
Request text syntax
Examples of using a query language to retrieve data from a single table
Examples of using a query language to retrieve data from multiple tables

Chapter 2. Working with queries in the built-in language

Query constructor
Creating a Simple Query
Query Source Relationships
Merging queries
Create a batch query that uses a temporary table
Executing queries from the built-in language

Create a request
Passing parameters to a request
Retrieving a sample from a query result
Traversing a selection from a query result
Processing Query Results Using the Query Builder
Uploading the query result into a table or tree of values
Using temporary tables using the built-in language
Examples of solving various problems using queries

Chapter 3. Solving applied problems

Data storage

Information registers
Characteristic type plans

Accounting for the movement of funds

Accumulation registers

Accounting

Charts of accounts
Plans for types of characteristics - types of subconto
Accounting registers

Complex periodic calculations

Calculation type plans
Calculation registers

Downloadable archives contain e-book files:

1C_QueryLanguage.epub* for reading on devices that support color images;

1C_QueryLanguageBWReaders.epub* for reading on devices with black and white screens;

1C_QueryLanguage.pdf(A5 format).

After payment you can download any of them or all at once.

Adobe Digital Editions

Electronic analogue of the printed publication “1C:Enterprise 8 Query Language” (ISBN 978-5-9677-1987-5, M.: 1C-Publishing LLC, 2013; article number of the printed book according to the price list of the 1C company: 4601546108029 ).

1C-Publishing LLC, 2013
© Design. 1C-Publishing LLC, 2013
All rights reserved.
The materials are intended for personal individual use by the purchaser.
It is prohibited to reproduce, distribute materials, or provide online access to materials without the written permission of the copyright holders.
It is permitted to copy fragments of program code for use in developed application solutions.

System requirements:

Program for reading files in .epub* format
*It is recommended that .epub files be read on a computer using the Adobe Digital Editions reader. When reading using other programs, it is not recommended to use a viewing mode that displays two pages at the same time - due to the large number of images, incorrect display may occur.

Adobe Reader for reading pdf files

After confirmation of payment for the book you will receive:

* It is recommended that you read .epub files on your computer using the Adobe Digital Editions reader. When reading using other programs, it is not recommended to use a viewing mode that displays two pages at the same time - due to the large number of images, incorrect display may occur.