1C programming consists of more than just writing a program. 1C is an ingot of user actions and data with which he works.

The data is stored in a database. 1C queries are a way to retrieve data from a database in order to show it to the user in a form or to process it.

The fundamental part of the report is the 1C request. When SKD report is the bulk of the report.

Sit down. Take a breath. Calm down. Now I will tell you the news.

To program in 1C, it is not enough to know the 1C programming language. You also need to know the 1C query language.

The 1C query language is a completely separate language that allows us to specify what data we need to get from the database.

He is also bilingual - that is, you can write in Russian or English. It is exceptionally similar to the tongue SQL queries and those who know one can relax.

How 1C Requests are used

When the user starts 1C in Enterprise mode - in running client there is not an ounce of data. Therefore, when you need to open a directory, 1C requests data from the database, that is, it makes a 1C request.

1C queries are:

• Automatic queries 1C
  Generated automatically by the system. You have created a document list form. Added a column. This means that when you open this form in Enterprise mode, there will be a query and the data for this column will be requested.
• Semi-automatic queries 1C
  There are many methods (functions) in the 1C language, when accessed, a query is made to the database. For example.GetObject()
• Manual 1C queries (written by the programmer specifically as a query)
  You can write a 1C request yourself in code and execute it.

Creating and executing 1C queries

A 1C request is the actual text of the request in the 1C request language.
The text can be written with pens. That is, take it and write it (if you know this language).

Since 1C promotes the concept visual programming, where much or almost everything can be done without writing code by hand - there is a special Query Constructor object, which allows you to draw the query text without knowing the query language. However, miracles do not happen - for this you need to know how to work with the constructor.

Once the text of the 1C request is ready, it needs to be executed. For this purpose there is an object in the 1C code Request(). Here's an example:

Request = New Request();
Query.Text = "SELECT
| Nomenclature.Link
|FROM
| Directory.Nomenclature AS Nomenclature
|WHERE
| Nomenclature.Service";
Select = Query.Run().Select();

Report(Selection.Link);
EndCycle;

As you can see in the example, after executing the 1C request, the result comes to us and we must process it. The result is one or several rows of the table (in a special form).

The result can be uploaded to a regular table:
Fetch = Query.Run().Unload(); //Result – table of values

Or just go around line by line.
Select = Query.Run().Select();
While Select.Next() Loop
//Do something with the query results
EndCycle;

Working with 1C requests

Basic principles of 1C queries

Basic principles of constructing a 1C request –
SELECT List of Fields FROM Table Name WHERE Conditions

An example of constructing such a 1C request:

CHOOSE
//list of fields to select
Link,
Name,
Code
FROM
//name of the table from which we select data
//list of tables is a list of objects in the configurator window
Directory.Nomenclature
WHERE
//indicate selection
Product Type = &Service //selection by external value
Or Service // “Service” attribute of type Boolean, selection by value True
SORT BY
//Sorting
Name

List of 1C tables

You can see the table names in the configurator window. You just need to write “Directory” instead of “Directories”, for example “Directory.Nomenclature” or “Document.Sales of Goods and Services” or “Register of Accumulation.Sales”.

There are additional tables (virtual) for registers that allow you to get the final numbers.

Information Register.RegisterName.Last Slice(&Date) – 1C request from the information register, if it is periodic, for a specific date

Accumulation Register.Register Name.Balances(&Date) – 1C request from the register of balances for a specific date

Accumulation Register.Register Name.Turnover (&Start Date, &End Date) – 1C request from the turnover register for the period from the start date to the end date.

Additional principles

When we request a list of some data, the basic principles work. But we can also request numbers and the request can count them for us (add them, for example).

CHOOSE
//Quantity(FieldName) – counts the quantity
//Field AS OtherName – renames the field
Quantity (Link) AS Quantity of Documents Posted
FROM

WHERE
Conducted

This 1C request will return us the total number of documents. However, every document has an Organization field. Let’s say we want to count the number of documents for each organization using a 1C query.

CHOOSE
//just a document field
Organization,
//count the quantity
Quantity(Link) AS QuantityBy Organizations
FROM
Document. Sales of Goods and Services
WHERE
Conducted
GROUP BY

Organization

This 1C request will return us the number of documents for each organization (also called “by organization”).

Let us additionally calculate the amount of these documents using a 1C request:

CHOOSE
//just a document field
Organization,
//count the quantity

//count the amount

FROM
Document. Sales of Goods and Services
WHERE
Conducted
GROUP BY
//must be used if the list of fields has a count() function and one or more fields at the same time - then you need to group by these fields
Organization

This 1C request will also return the amount of documents to us.

CHOOSE
//just a document field
Organization,
//count the quantity
Quantity(Link) AS QuantityBy Organizations,
//count the amount
Amount(DocumentAmount) AS Amount
FROM
Document. Sales of Goods and Services
WHERE
Conducted
GROUP BY
//must be used if the list of fields has a count() function and one or more fields at the same time - then you need to group by these fields
Organization
PO RESULTS General

The 1C query language is extensive and complex, and we will not consider all its capabilities in one lesson - read our next lessons.

Briefly about additional features 1C query language:

• Joining data from multiple tables
• Nested Queries
• Batch request
• Creating your own virtual tables
• Query from value table
• Using built-in functions for getting and manipulating values.

1C Query Builder

In order not to write the request text by hand, there is a 1C request designer. Just right-click anywhere in the module and select 1C Query Designer.

Select the desired table on the left in the 1C query designer and drag it to the right.

Select the required fields from the table in the 1C query designer and drag to the right. If you would like not only to select a field, but to apply some kind of summation function to it, after dragging, click on the field twice with the mouse. On the Grouping tab, you will then need to select (drag) the required fields for grouping.

On the Conditions tab in the 1C query designer, you can select the necessary selections in the same way (by dragging the fields by which you will make the selection). Be sure to select the correct condition.

On the Order tab, sorting is indicated. On the Results tab – summing up the results.

Using the 1C query designer, you can study any existing query. To do this, right-click on the text of an existing request and also select 1C query designer - and the request will be opened in the 1C query designer.

Query constructor is one of the development tools. It allows you to compose a query text in a query language using exclusively visual means.

Using buttons Further And Back you can navigate through the tabs of the designer and specify what data should be present as a result of the query, how they are related, grouped, what totals should be calculated, work with temporary tables, edit a package of queries:

The result of the constructor's work will be the syntactically correct request text. Thus, a developer can create a workable query even without knowing the syntax of the query language - the constructor will generate the necessary syntactic structures automatically. The finished request text can be immediately pasted into the module text or copied to the clipboard.

In addition, the query designer allows you to edit the query text that is already in the program. To do this, just place the cursor inside the existing request text and call the constructor. The existing request text will be analyzed and presented in the designer in the form of the corresponding selected database fields and a set of specified relationships, groupings, conditions, etc.

One of the most powerful tools of 1C Enterprise 8, without a doubt, is the Query Builder. The constructor is a visual tool for working with queries in the 1C language. But the constructor has one big drawback - it does not show the result of executing queries. This disadvantage is especially felt by novice developers of application solutions on the 1C:Enterprise 8.3 and 8.2 platform.

In addition, even experienced 1C programmers are often faced with a situation where it is necessary to do a “one-time” data analysis information base in various sections, but I don’t want to write a full-fledged report for the sake of such a “one-time conclusion”.

For all these and many other cases, we are pleased to offer you external processing Query console with the ability to process results for 1C 8.3 and 1C 8.2!

Main features of the Query Console for 1C 8.3 and 1C 8.2:

Universal processing, runs on any configuration of 1C platforms: Enterprise 8.1 – 8.3;
- runs in 1C:Enterprise mode;
- the query can be written manually, or you can call the Query Builder;
- shows the result of the request;
- you can specify report parameters directly in the Query Console;
- can save and load request files.

To launch the Query Console, you need to launch your configuration in 1C:Enterprise mode and open external processing Query Console like any other file stored on disk. This processing will work exactly the same as if it were part of an application solution.

External processing The query console repeats the familiar interface and most functionality standard tools for drawing up queries of the 1C:Enterprise system 8.3 and 8.2. It also allows you to retrieve the results of queries, batch queries, and temporary tables.

The query console is executed in both client-server and file-server operating modes of the system, both under thick and thin clients! The Query Console integrates a standard Query Builder, which can be called via the context menu in the query text field.

IMPORTANT! Query constructor in thin client does not work, this menu item for opening the Designer is not available. In the thick client, the Query Builder is available!

In the thin client, we can only write requests by hand; all other functions work without changes.

The query designer in 1C 8.3 and 8.2 is a powerful development tool. It allows you to compose a request text using a special visual environment. Thus, in order to create a 1C request, it is not necessary to know the built-in query language; it is enough to navigate the simple and intuitive interface of the designer.

The query builder is a set of tabs, each of which is responsible for its own part of the query. So filling out the tab Tables and fields We select tables from which the 1C query will receive the data and fields of these tables necessary to solve a specific problem. Filling into the masonry Conditions we impose conditions on the selected tables in order to select from them only the data we need, and so on.

Description of the query designer on the official 1C 8 website: v8.1c.ru

Tables and fields; ; ; ; ; ; Nested queries (in development).

In order to call the 1s 8 query designer program code necessary:

• Create a new request

• Set an empty request text line

• Place the mouse cursor between the quotes and press the right mouse button. In the opened context menu select item Query constructor and answer Yes to the question about creating a new request. If the request text has already been written down, then you need to click anywhere inside it and call the constructor ;

Let's look at all the main tabs of the query builder using small examples of increasing complexity. This approach will allow a novice 1C programmer to more effectively study the constructor and all its capabilities. For examples we will use the configuration Accounting 3.0.

Lesson #1. The query builder is the simplest use case.

Task: write a request to the nomenclature directory, select the entire nomenclature of the directory.

New tabs: Tables and fields.

New mechanisms: viewing and editing the request text using the “Request” button.

To start creating a request, let's create a new request and call the constructor (as written a few paragraphs above). After this, the designer window will open on the tab Tables and fields.

Theoretical part of lesson No. 1

Tab Tables and fields consists of three sections:

Database. This section presents all the database tables that can be used to build a query;

Tables. This section contains the tables needed for of this request. To then move them from the section database need to:

• Or double-click on the table;
• Or use the “>” or “>>” buttons.

Above section Tables There are a number of buttons. Most of them will be discussed in more detail in the following lessons. For now I will give only brief explanations.

• Create a subquery(Red line). Designed to create a new subquery;
• Create a temporary table description(yellow line). Allows you to specify the name of a temporary table that is located outside of this query; it can also be used to pass a table of values ​​to the query;
• Change current element(green Line). Allows you to jump to the selected subquery, temporary table, or temporary table description;
• Remove current item(blue line). Removes the selected table from the selected tables;
• Replace table(blue line). Opens the dialog for replacing the selected table. Useful if you have selected the wrong register virtual table, as positioning occurs on the currently selected table in the list.
• Virtual Table Options(purple line). Opens the parameters of the virtual register table.

Fields. This section selects table fields from the previous section. These fields will be the columns of the table or selection obtained as a result of the query. They are needed primarily in order to obtain from the selected tables only the information necessary in a particular case. In order to move them from the section Tables needed:

• Or double-click on the field;
• Or use the “>” or “>>” buttons;
• You can also add a new field yourself, using an arbitrary expression from the fields of selected tables and query language functions.

Above section Fields There are a number of buttons. Creating fields using arbitrary expressions will be discussed in more detail in the following lessons. For now I will give only brief explanations.

• Add(green Line). Designed to add a new field using the free expression editor;
• Change current element(Red line). Allows you to change the selected field using the editor;
• Delete current(blue line). Removes the selected field from the list.

Practical part of lesson No. 1

We have dealt with the theory necessary to complete the task provided in this lesson. Let me remind you how it sounds: write a request to the nomenclature directory, select the entire nomenclature of the directory.

Let's start creating a request for items:

• Let's create a new request and open the constructor using the method specified at the beginning of the lesson;
• In chapter Database, let's open a thread Directories and we'll find a guide there Nomenclature;
• Select it and use the “>” button to move it to the section Tables;
• In chapter Tables open the nomenclature directory using the “+” icon;
• In the list of fields that opens, find the field Link and move it to the section Fields using the ">" button
• The item request is ready, click the “OK” button at the bottom of the designer window.

Requests in 1C – effective method receiving data in v8 famous software product. You can receive data not only in a convenient form, but also in short term. Optimization is one of the ways to improve the speed of query processing in the system. It's convenient that query language 1C supports both Latin and Cyrillic alphabet.

Small professional tricks of the query language

The request in the system is written to special language, and you can debug its parameters using the console in 1C. Keyword in query language is "SELECT".

In combination with the SELECT structure, the following structures can appear:

• “FOR CHANGE” - all table records are blocked for the duration of the transaction procedure;
• “DIFFERENT”—there should be no identical rows as a result of request processing;
• “ALLOWED” - selection according to which the current user will receive only that information for which he has user rights;
• "THE FIRST 3 (or any value of n) – on query language this means that the system will make a selection based on the first 3 records.

Competent request – effective optimization of work

Programmers know that in the 1C v8 program you can get up-to-date data using two effective methods:

• Object option for obtaining information;
• Tabular method of data extraction.

The object model is used when the goal is to obtain a simple set of data with a simple set of code. Query language in 1C it works for all cases, this is its versatility. But an illiterately created query impairs performance and does not always provide the opportunity to obtain correct data. But to be a good programmer in 1C it is not enough to study query language, you need to learn to correctly apply its rules.

The optimal request, where to start for beginners in 1C

A little more about the system of basic structures in the 1C language, for example, we have a data table:

Table "Nomenclature".

Task: obtain data for the Code and Variety columns in tabular form.

On query language it looks like this:

Request.Text = "
|SELECT
| Nomenclature.Code,
| Nomenclature.Variety
|FROM
| Directory. Nomenclature AS Nomenclature";

And we get the following:

For those with basic level designs query language do not cause difficulties, it makes sense to move on to grouping. The key here will be the “GROUP BY” construction, which will help group identical database fields. In effect, the grouping method combines fields with identical information into one. The resulting table has fewer rows, which is convenient both for current activities and for analysis. But it is important that all database fields first have a breakdown: grouping, not grouping.

Let's look at the “GROUP BY” construction as an example.

Task: group by quantity. To do this, you need to group fields with the same values ​​to obtain aggregated summary results. The grouping command collapses identical field values. As a result, the user receives up-to-date information with less significant lines, which is convenient for making management decisions and analyzing the current situation.

To find out how much product is available without taking into account the warehouse, you need to:

Request.Text = "
|SELECT
| Products.Product,
| SUM(Items.Quantity) AS Quantity
|FROM
| Directory.Products AS Products
|GROUP BY
| Products.Product";

Grouping is done by the value of the Item field; the type of field being grouped is Quantity. The sum in this particular example is some kind of aggregate function. It is what exactly needs to be done with the quantity of identical goods.

As a result, we get a table with the following information:

Special processing “Request Console” in v8.1/8.2/8.3 allows you to launch in Enterprise mode and formulate the requests themselves manually or by calling a function through the console. You can already estimate in advance what the outcome of the execution will be; the files can be saved.