Left and right connection 1s

The 1C query language is one of the main differences between versions 7.7 and 8. One of the most important points in learning 1C programming is the query language. In 1C 8.3, queries are the most powerful and effective tool for obtaining data. The query language allows you to obtain information from the database in a convenient way.

The syntax itself is very much reminiscent of classic T-SQL, except that in 1C, using the query language, you can only receive data using the Select construct. The language also supports more complex constructs, for example, (request within a request). Queries in 1C 8 can be written in both Cyrillic and Latin.

In this article I will try to talk about the main keywords in the 1C query language:

• choose
• allowed
• various
• express
• first
• for change
• meaning
• value type (and REFERENCE operator)
• choice
• group by
• having
• ISNULL
• Yes NULL
• connections - right, left, internal, full.

As well as some small tricks of the 1C language, using which you can optimally construct the request text.

To debug queries in the 1C 8.2 system, a special tool is provided - the query console. You can see the description and download it using the link -.

Let's look at the most important and interesting operators of the 1C query language.

SELECT

In the 1C Enterprise 8 query language, any query begins with a keyword CHOOSE. In the 1C language there are no UPDATE, DELETE, CREATE TABLE, INSERT constructs; these manipulations are performed in object technology. Its purpose is to read data only.

For example:

CHOOSE
Current Directory.Name
FROM
Directory.Nomenclature AS Current Directory

The query will return a table with item names.

Near the structure CHOOSE you can find keywords FOR CHANGE, ALLOWED, VARIOUS, FIRST …

ALLOWED— selects only records from the table that the current user has rights to.

VARIOUS— means that the result will not contain duplicate lines.

SELECTION (CASE)

Very often this design is underestimated by programmers. An example of its use:

Current Directory.Name,

WHEN Current Directory.Service THEN

"Service"

END HOW TO VIEWNomenclature

Directory.Nomenclature AS Current Directory

The example will return a text value in the “Item Type” field - “Product” or “Service”.

WHERE

The design of the 1C query language, which allows you to impose selection on the received data. Please note that the system receives all data from the server, and only then it is selected based on this parameter.

CHOOSE
Directory.Name
FROM
Current Directory.Nomenclature AS Current Directory
WHERE CurrentDirectory.Service = TRUE

In the example, we select records for which the value of the “Service” attribute is set to “True”. In this example, we could get by with the following condition:

"WHERE IS THE SERVICE"

Essentially, we are selecting rows where the expression after the keyword is equal to "True".

You can use direct conditions in expressions:

WHERE Code = "005215"

Using the “VALUE()” operator in the conditions, use access to predefined elements and enumerations in a 1C request:

WHERE Item Type = Value(Enumeration.Item Types.Product)

Time values ​​can be specified as follows:

WHERE Receipt Date > DATETIME(2012,01,01):

Most often, conditions are specified as parameters passed to the request:

Get 267 video lessons on 1C for free:

WHERE NomenclatureGroup= &NomenclatureGroup

A condition can be imposed on the attribute type if it is of a composite type:

If you need to limit selection from a list of values ​​or an array, you can do the following:

WHERE is the Accumulation Register. Registrar B (&List of Documents for Selection)

The condition can also be complex, consisting of several conditions:

WHERE Receipt Date > DATETIME(2012,01,01) AND NomenclatureGroup= &NomenclatureGroup AND NOT Service

GROUP BY

Design of the 1C 8.2 query language used to group the result.

For example:

CHOOSE
Receipt of Goods and Services Goods. Goods,
SUM(Receipt of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Receipt of GoodsServicesGoods.Amount) AS Amount
FROM
Document. Receipt of Goods and Services. Goods HOW Receipt of Goods and Services Goods

GROUP BY
Receipt of GoodsServicesGoods.Goods

This request will summarize all receipts by amount and quantity by item.

Besides the keyword SUM You can use other aggregate functions: QUANTITY, NUMBER OF DIFFERENT, MAXIMUM, MINIMUM, AVERAGE.

HAVING

A design that is often forgotten, but it is very important and useful. It allows you to specify selection in the form of an aggregate function, this cannot be done in the design WHERE.

Example of using HAVING in a 1C request:

CHOOSE
Receipt of Goods and Services Goods. Goods,
SUM(Receipt of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Receipt of GoodsServicesGoods.Amount) AS Amount
FROM
Document. Receipt of Goods and Services. Goods HOW Receipt of Goods and Services Goods

GROUP BY
Receipt of Goods and Services Goods. goods

SUM(Receipt of GoodsServicesGoods.Quantity) > 5

So we will select the number of products that arrived more than 5 pieces.

MEANING()

For example:

WHERE Bank = Value(Directory.Banks.EmptyLink)

WHERE Nomenclature Type = Value(Directory.Nomenclature Types.Product)

WHERE Item Type = Value(Enumeration.Item Types.Service)

TYPE in request

The data type can be checked by using the TYPE() and VALUETYPE() functions or using the logical REFERENCE operator.

EXPRESS()

The Express operator in 1C queries is used to convert data types.

Syntax: EXPRESS(<Выражение>HOW<Тип значения>)

Using it, you can convert string values ​​to date or reference values ​​to string data, and so on.

In practical applications, the Express() operator is very often used to convert fields of unlimited length, because fields of unlimited length cannot be selected, grouped, etc. If such fields are not converted, you will receive an error You cannot compare fields of unlimited length and fields of incompatible types.

CHOOSE
ContactInformation.Object,
EXPRESS(ContactInfo.View AS ROW(150)) AS View
FROM
Register of Information. Contact Information HOW Contact Information

GROUP BY
EXPRESS(ContactInfo.Representation AS ROW(150)),
ContactInformation.Object

ISNULL (ISNULL)

Quite a useful function of the 1C query language that checks the value in the record, and if it is equal NULL, This allows you to replace it with your own value. Most often used when obtaining virtual tables of balances and turnover in order to hide NULL and put a clear 0 (zero).

ISNULL(Pre-Month Taxes.AppliedFSS Benefit, 0)

Such a function of the 1C query language ISNULL will return zero if there is no value, which will avoid an error.

JOIN

There are 4 types of connections: LEFT, RIGHT, COMPLETE, INTERNAL.

LEFT and RIGHT CONNECTION

Joins are used to link two tables based on a specific condition. Feature when LEFT JOIN is that we take the first specified table in its entirety and conditionally bind the second table. The fields of the second table that could not be bound by condition are filled with the value NULL.

An example of a left join in a 1C request:

It will return the entire table and fill in the “Bank” field only in those places where the condition “Counterparties.Name = Banks.Name” is met. If the condition is not met, the Bank field will be set to NULL.

RIGHT JOIN in 1C 8.3 language absolutely similar LEFT connection, with the exception of one difference: in RIGHT OF CONNECTION The "main" table is the second, not the first.

FULL CONNECTION

FULL CONNECTION differs from left and right in that it displays all records from two tables and connects only those that it can connect by condition.

For example:

FULL CONNECTION
Directory.Banks HOW Banks

BY

The query language will return both tables completely only if the Join records condition is met. Unlike a left/right join, it is possible for NULL to appear in two fields.

INNER JOIN

INNER JOIN differs from full in that it displays only those records that could be connected according to a given condition.

For example:

FROM
Directory. Counterparties AS Clients

INNER JOIN
Directory.Banks HOW Banks

BY
Clients.Name = Banks.Name

This query will return only rows in which the bank and counterparty have the same name.

Conclusion

This is only a small part of the syntax from the 1C 8 query language; in the future I will try to consider some points in more detail, show and much more!

; Nested queries (in development).

Task No. 1: use a query to select all posted documents Sales of goods and services,

Explanation: configurations Accounting 2.0 / 3.0 The implementation does not store a link to the issued invoice. The connection between these documents is reverse: in the invoice (in the details A document base) a reference to the implementation to which it relates is stored. Thus, to understand which implementations do not have invoices, you will need to use two tables in the query: Sales of Goods and Services And InvoiceIssued.

Task No. 2: let’s say in the database of the directory Contracts of counterparties additional information created Postponement(storage location register additional information). Use a query to select all agreements of the specified counterparty with a delay from 0 to 10 days.

Explanation: in this task it would be possible to do without using two tables, if not for one condition: the choice of contracts with zero deferment. The point is that in the register additional information Empty values ​​for additional information are not stored, so there will be no entries for zero deferment there. Therefore, you will have to attach this register to the directory Contracts of counterparties, if there is no record with a deferment for any of the contracts, then it is zero.

New tabs: Connections

New mechanisms: checking for null in 1C request.

Theoretical part of lesson No. 3

If a 1C query uses only one table, then data is taken only from it. But if you need to select data from multiple tables, then you should set up relationships between these tables. Relationships are necessary in order to compare the rows of one database table with the rows of another table and thus obtain, when forming a query, a final table that will contain the necessary data from all query tables.

Each specific relationship is configured between two tables, one of them is called Left, other Right. There are three types of connections:

• INNER JOIN- a record from the left table will appear in the query result only if the connection condition is met, a record from the right table will appear in the query result only if the connection condition is met;
• LEFT CONNECTION- a record from the left table will be included in the query result in any case, a record from the right table will be included in the selection only if the connection condition is met. The analogue of the left is RIGHT JOIN, The data from the right table is included in the result in any case, the data from the left table only if the condition is met. Usually when writing a query it is used Left connection, if you use the right one, the next time you open the query, the query designer will convert it to the left one, swapping the tables;
• FULL CONNECTION- a record from the left table will appear in the query result first in any case, then only if the connection condition is met, a record from the right table will appear in the query result first in any case, then only if the connection condition is met. In this case, the resulting duplicate rows are excluded from the sample.

In theory, joining query tables is quite difficult to understand for an untrained 1C programmer, but when studying the examples, all unclear points will become clearer.

Relationships tab in Query Designer

In the query designer, go to the tab Connections. It is a table in which the required number of connections is created for all different pairs of tables. You can add, delete, copy and change connections using the buttons located above the table (we will not analyze each in detail).

After adding a new connection, you must fill in all columns of the table. Let's look at each column of the connection table:

• Table 1. The left connection table is selected in this column. You can only use tables selected on the tab Tables and fields;
• All. The flag in this column is placed if you need to select all data for the left table (for Left or Full connections);
• Table 2. The right connection table is selected in this column;
• All. The flag in this column is placed if you need to select all data for the right table (for Right or Full connections);
• free. Flag with which you can switch to manual editing mode of the connection condition;
• Condition of connection. A condition in a query language, similar to the conditions discussed in . It is an expression that must always return one of two values: True or Lie. Typically, a link condition consists of three parts:
  • Left side. Usually a field in the left table, but it can also be a field in the right table or a parameter (only in custom condition editing mode);
  • Comparison operator. By default, "=", "<>», «<«, «>», «>=», «<=». Но в произвольном режиме редактирования можно использовать и некоторые другие, например «Между»;
  • Right part. Usually a field in the right table, but it can also be a field in the left table or a parameter (only in custom condition editing mode);

Filling and editing Terms of communication, similar to editing a regular condition discussed in the last lesson, with the exception that by default, not a parameter is set in the right part of the condition, but a field in the right table. In free mode, a free expression editor is also available.

Practical part of lesson No. 3

Let's look at the solution to the problems given at the beginning of the lesson.

Task No. 1

Select all posted documents with a query Sales of goods and services, for the specified period, for which an invoice has not been created.

• Let's create a new request;
• Let's select tables Sales of Goods and Services And InvoiceIssued from the thread Documentation;
• From the table Sales of Goods and Services select a field Link;
• Let's go to the tab Conditions;
• In chapter Fields, let's open the thread Sales of Goods and Services using the “+” button;
• Let's find the props date and drag it to the conditions section, select the comparison operator Between and indicate the parameters for the beginning and end of the period, for example Beginning of period And End of the Period;
• From section Fields let's drag the props Conducted, put a flag in the line with the condition free and erase the extra part of the condition “= &Passed”;
• Let's go to the tab Communications;
• Let’s create a new connection using the “Add” button;
• In field Table 1 select a table Sales of Goods and Services(this will be the left table) ;
• To correctly solve the problem, we need to get all the implementations, attach invoices to them, and if the invoice is not found, display the implementation as the result of the request. Let's split this task into two subtasks:
  • Let’s select all sales and, using the connection, try to find their invoice;
  • Using the condition, we will select only those lines where there is no invoice;
• From the previous paragraph it follows that we need to use Left connection, in order to select all implementations. Therefore, let's put a flag in the column All
• In field table 2 select a table InvoiceIssued(this will be the right table);
• Since the link to the implementation is stored in the invoice details D DocumentBase, in the connection condition we choose:
  • On the left side of the field Sales of Goods and Services. Link;
  • On the right side of the field: InvoiceInvoiceIssued.DocumentBase.

• The connection setup is complete;

With a left join, if for a row of the left table there is not a single row of the right table that satisfies the connection conditions, all fields of the right right table for a given row of the left table have the value NULL. Let's use this to select only those rows of the left table for which there is no invoice;

The 1C query language has a special logical operator that allows you to check whether a field contains a value NULL, its syntax <Поле>IS NULL. It returns a value True if the field has a value NULL And Lie- in the opposite case.

Note that check the field value by NULL is possible only using the specified function, construction <Поле>= NULL This check will not be possible.

• Let's go to the tab Conditions;
• Drag one of the table fields to the conditions section InvoiceIssued, for example field Link;
• Set a flag in the line free and add the operator after the field IS NULL, having previously erased the unnecessary part of the condition;

Request = New Request; Request.SetParameter("StartPerid" , StartPerid); Request.SetParameter("End of Period", End of Period); Request.Text = "SELECT | Sales of Goods and Services. Link | FROM | Document. Sales of Goods and Services AS Sales of Goods and Services | LEFT CONNECTION Document. Invoice Issued AS Invoice Invoice Issued | Software Sales of Goods and Services. Link = Invoice Issued. Document Basis | WHERE | Sales of Goods and Services. INTERNATIONAL At & Beginning of Period And & End of Period | And Sale of Goods and Services. Conducted | AND InvoiceIssued.Reference IS NULL";

Task No. 2

• Let's create a new request;
• Let's launch the query builder;
• On the tab Tables and fields Let's select two tables: Contracts of Counterparties from the thread Directories And Additional information from the thread Information Registers;
• From the table Contracts of Counterparties select a field Link;

According to the condition, the request result should include contracts of only one counterparty, the value of which will be passed to the request using a parameter. The counterparty to which the contract belongs is stored in the field Owner directory Contracts of counterparties.

• Let's go to the tab Conditions. From the table Contracts of Counterparties move the field to the conditions section Owner. On the right side of the condition, we’ll set the name of the parameter to which we’ll pass the counterparty we need, let’s call it Counterparty;
• We will also divide the task of selecting contracts with delays from 0 to 10 into two subtasks:
  • Let’s select all the contracts and, using connections, try to find their deferments;
  • using the condition, we will select only contracts with the deferments we need;
• Let's set up connections between contracts and additional information. Let's go to the Connections tab and add a new connection;
• In field Table 1 select a table Contracts of Counterparties(this will be the left table);
• Since we need all contracts, we will use Left connection. Let's put a flag in the All column, which belongs to the left table;
• In field table 2 select a table Additional information(this will be the right table);
• Since the reference to the contract is stored in the dimension An object information register Additional information, in the connection condition we choose:
  • On the left side of the field Contracts of Contractors. Link;
  • Let's leave the standard comparison operator "=";
  • On the right side of the field: AdditionalInfo.Object.

We have added a link with which we will select all additional. information about each contract. But since we only need one extra. mixing - Postponement, we need to add one more connection. In this case Postponement is an additional property. Additional properties are of type Plan of types of characteristics Additional Details and Information. In the register Additional information additional value properties are stored in a dimension Property. Thus, we need to add a relationship that limits additional information only to the Deferral property.

• Let's add a new connection;
• In field Table 1 select a table Contracts of Counterparties;
• It is important to know that if you have multiple relationships on the same pair of tables, then you must use the same join type for them. In our case, this is Left connection. Therefore, let's put a flag in the column All, relating to the left table;
• In field table 2 select a table Additional information;
• In the relation condition we will not need to use the left table field, instead we will impose a condition on the dimension Property, equating it to additional. property Postponement, which will be passed to the request using the parameter;
• Let's set the flag free in meaning True and manually write the following condition text: “Additional Information.Property = &PropertyDelay”;

• The connection setup is complete;

Now all that remains is to impose a condition on the amount of deferment. Since we use the interval from 0 to 10, it will be necessary to use the comparison operator Between. Since the zero backoff is not stored in additional information, when connected in this case, all register fields will return the value NULL. In order to replace the value in a condition NULL to 0, we will use the query language function ISNULL(<Выражение1>, <Выражение2>) . The function returns Expression1, if it is not equal NULL And Expression2 otherwise.

• Let's go to the tab Conditions query designer and add a new condition;
• Let's set a flag in it free and go to the free expression editor;
• In the query language functions section we will expand the branch Functions -> Other functions;
• Let's drag a function into the expressions section ISNULL;
• After the resulting construction, we will enter the comparison operator Between and set the interval: 0 and 10;
• Click OK, the condition is ready;

The last thing you need to do is display the deferment value in the request fields. In order to instead NULL the value 0 was displayed, we also use the free expression editor and the function ISNULL.

• Let's go to the tab Tables and fields and add a new field;
• In the free expression editor that opens, select the function ISNULL;
• Instead of Expression 1, insert the AdditionalInformation.Value field, and instead of Expression 2, insert 0;
• Click OK, the field is ready;

As a result, we will get a request with the following text:

Request = New Request; Query.SetParameter("PropertyDelay", PropertyProperty); Request.SetParameter("Account", Account); Query.Text = "SELECT | Counterparty Agreements. Link, | ISNULL(Additional Information. Value, 0) AS Field1 | FROM | Directory. Counterparty Agreements AS Counterparty Agreements | LEFT JOIN Information Register. Additional Information | AS Additional Information | ON Counterparty Agreements. Link = Additional Information. Object | AND (Additional Details.Property = &PropertyDelay) |WHERE | Contracts of Contractors.Owner = &Counterparty | AND ISNULL(Additional Details.Value, 0) BETWEEN 0 AND 10";

Also read articles about the 1C 8 query language.

When we want to see data from several tables at the same time, i.e. to collect several tables into one, the concept of connecting tables and relationships between them arises. There are four types of connections:

• left;
• right,
• internal;
• complete.

We will look at each type using an abstract example. There are 2 tables, in the first we store descriptive information about the item, in the second about its balances:

In order to get one from these tables, we need to explicitly indicate which fields we will connect, by what condition and type. Now it will become more clear.

Left connection

Using a left join, we tell the system that as a result we want to see all the records from the left table and the records from the right that satisfy the connection condition. Suppose we connect tables by the product field with the equal condition, then we will get a table like:

Request.Text =
"CHOOSE
| Nomenclature.Product,
| Nomenclature.Color AS ColorNomenclature,
| Remains.Color AS ColorRemains,
| Balances.Quantity
|FROM

";

There were no matches for the chair from the table of remainders, so the fields were filled with NULL values, which must be processed by the ISNULL function, see 1C 8 Query Language Functions.

The left join works approximately like a loop within a loop - it takes the first record from the left table and runs through all the records from the right table to ensure that the connection condition is satisfied. Then the second record is taken from the left table, and so on. If suddenly several records from the right table satisfy the connection condition, then several rows will be added to the resulting table (according to the number of successful connections). As you can see, the resulting table is not informative, the data does not reflect the real essence, so it is better to connect these tables by two fields: Product and Color, only this time we’ll handle NULLs:

Request.Text =
"CHOOSE
| Nomenclature.Product,
| Nomenclature.Color,
| ISNULL(Remaining.Quantity, 0) AS Quantity
|FROM
| Nomenclature AS Nomenclature
| LEFT JOIN Remainders AS Remainders
| Software Nomenclature.Product = Remaining.Product

Right connection

The right connection is essentially no different from the left. If you swap the tables, the right join will turn into a left join; moreover, when using the constructor, the system itself converts all right joins to left joins.

Inner join

Using an inner join, we tell the system that as a result we want to see only those records that satisfy the connection condition from both the right table and the left one. Thus, the number of resulting records will be less than or equal to the number of records of the shortest table participating in the join. Let's apply an inner join to the Product and Color fields of our tables:

Request.Text =
"CHOOSE
| Nomenclature.Product,
| Nomenclature.Color,
| Remainings.Quantity AS Quantity
|FROM
| Nomenclature AS Nomenclature
| INNER JOIN Remainders AS Remainders
| Software Nomenclature.Product = Remaining.Product
| And Nomenclature.Color = Remaining.Color";

Full connection

A full join will result in all records from both tables, those records that satisfy the connection condition will be connected, those records that do not satisfy the connection condition will still end up in the query result, but with some NULL fields. Complete is like left and right connections in one.

There can be many problems on this topic, let's try to solve one of them. Our organization is a dealer of 2 furniture factories: “Zarya” and “Rassvet”. The assortment with the cost of each of the factories is stored in different tables. It is necessary to draw up a single price list, and include products in it at the minimum price:

Let's apply a full join with a selection of all fields, we will connect by product:

Request.Text =
"CHOOSE
| NomenclatureZarya.Product AS ProductZarya,
| NomenclatureZarya.Price AS PriceZarya,
| Nomenclature Rassvet. Product AS Product Rassvet,
| NomenclatureRassvet.Price AS PriceRassvet
|FROM

This is not exactly what we need, let's combine the product field into one and process NULLs:

Request.Text =
"CHOOSE
//the ISNULL construction was discussed in the query language functions section
//if the price is not defined, then initialize it
//why 1000000 see explanation below
| ISNULL(NomenclatureZarya.Price, 1000000) AS PriceZarya,
| ISNULL(NomenclatureRassvet.Price, 1000000) AS PriceRassvet
|FROM
| NomenclatureZarya AS NomenclatureZarya
| FULL CONNECTION NomenclatureDawn AS NomenclatureDawn
| Software NomenclatureZarya.Product = NomenclatureDawn.Product";

All that remains is to choose the minimum price. The final request text will look like this:

Request.Text =
"CHOOSE
| ISNULL(NomenclatureZarya.Product, NomenclatureDawn.Product) AS Product,
| CHOICE
| WHEN THERE ISNULL(NomenclatureZarya.Price, 1000000) > ISNULL(NomenclatureRassvet.Price, 1000000)
| THEN ISNULL(NomenclatureRassvet.Price, 1000000)
| ELSE ISNULL(NomenclatureZarya.Price, 1000000)
| END AS PRICE
|FROM
| NomenclatureZarya AS NomenclatureZarya
| FULL CONNECTION NomenclatureDawn AS NomenclatureDawn
| Software NomenclatureZarya.Product = NomenclatureDawn.Product";

If the price is not defined (NULL), then it must be initialized with some value, otherwise the comparison operation for more/less will fail with an error. We initialize the price with an unrealistically large amount so that it “loses” in the comparison operation, because according to the conditions of the problem we select the lowest price.

← Functions of the 1C query language 8 | Joins in 1C 8 queries →