Logical operations (1C enterprise). Logical operations (1c enterprise) Using logical OR in conditions

Conditions in queries are applied when it is necessary to select not all records from a 1C infobase table, but only those that meet one or more selection criteria.

Conditions in 1C queries can be set in various ways depending on the characteristics of the query itself and the data source.

Keyword "WHERE"

Keyword WHERE used in 1C queries when a condition is applied to records selected from the infobase. In this case, filtering of records occurs at the stage of selecting them from the infobase table.

For example, there is a table Payments, containing a list of employees and payments made to them.

If you need to select payments in the amount of at least 10,000, then the request will look like this

Request. Text= "CHOOSE
| Payments.Employee,
| Payments. Salary
|FROM
| Payments
|WHERE
| Payments. Salary >=10000"

The result of the query will be the following table.

Keyword "HAVING"

Keyword HAVING in 1C queries it is used when it is necessary to select among already selected records. For example, this keyword is used when you need to filter grouped records as a result of a query.

Let's return to the example discussed above. Let's say you need to select from a table Payments employees who received more than 20,000 in total. To do this, you first need to enter a grouping by field Employee, calculate the sum by field Salary for each employee, and then from the received records select those that satisfy the condition.

The request text will look like this.

Request. Text= "CHOOSE
| Payments.Employee,
| AMOUNT(Payments.Salary) AS Salary
|FROM
| Payments
|GROUP BY
| Payments.Employee
|HAVING
| AMOUNT(Payments.Salary) > 20000"

The result of executing this request will be like this.

This example clearly shows the difference between keywords WHERE And HAVING. If we used the word WHERE instead of HAVING, then records with a salary greater than 20,000 would first be selected, and then the amount for each employee would be calculated from them. As a result, we would receive an empty query result, because no employee received more than 20,000 in one payment.

Selection conditions in virtual register tables

All registers in the 1C:Enterprise system have virtual tables: accumulation registers, information registers, accounting registers. You can set conditions for virtual tables in two ways:

1. through the parameters of the virtual table;
2. via request sections WHERE or HAVING.

Differences between sections WHERE And HAVING we have already looked at. To understand how the conditions in these sections of the request differ from the conditions in the parameters of the virtual table, you need to understand what virtual register tables in 1C. Their important feature is that they do not exist in the information base. This is a tool provided to us by the 1C:Enterprise platform to optimize the configuration.

Thus, if a condition is specified in the parameters of a virtual table, then it is applied at the stage of its formation from records in the real register table. And if in sections WHERE or HAVING, then - to the records of an already formed virtual table.

Let's look at an example. A table of the periodic information register is given Currency rates.

You need to select currencies for the lowest date whose rate is less than 30 rubles.

A query with a condition in the virtual table parameters will look like this:

In this case we will get one entry: Dollar 28.3 dated 02/01/2007. This will be the right solution to the problem.

If we put the condition in the section WHERE, then the query result will be empty, since first a cut will be made of the first ones for each currency (as a result we will get two records from 01/01/2007 Dollar 30.25 And Euro 40.5), and then records that satisfy the condition will be selected from them. But both received records are subject to Well< 30 do not satisfy.

The query language in 1C 8 is a simplified analogue of the well-known “structured programming language” (as it is more often called, SQL). But in 1C it is used only for reading data; an object data model is used to change data.

Another interesting difference is the Russian syntax. Although in fact you can use English-language constructions.

Example request:

CHOOSE
Banks.Name,
Banks.CorrespondentAccount
FROM
Directory.Banks HOW Banks

This request will allow us to see information about the name and correspondent account of all banks existing in the database.

Query language is the simplest and most effective way to obtain information. As can be seen from the example above, in the query language you need to use metadata names (this is a list of system objects that make up the configuration, i.e. directories, documents, registers, etc.).

Description of query language constructs

Query structure

To obtain data, it is enough to use the “SELECT” and “FROM” constructions. The simplest request looks like this:

SELECT * FROM Directories.Nomenclature

Where “*” means selecting all fields of the table, and Directories.Nomenclature – the name of the table in the database.

Let's look at a more complex and general example:

CHOOSE
<ИмяПоля1>HOW<ПредставлениеПоля1>,
Sum(<ИмяПоля2>) HOW<ПредставлениеПоля2>
FROM
<ИмяТаблицы1>HOW<ПредставлениеТаблицы1>
<ТипСоединения>COMPOUND<ИмяТаблицы2>HOW<ПредставлениеТаблицы2>
BY<УсловиеСоединениеТаблиц>

WHERE
<УсловиеОтбораДанных>

GROUP BY
<ИмяПоля1>

ORDER BY
<ИмяПоля1>

RESULTS
<ИмяПоля2>
BY
<ИмяПоля1>

In this query, we select the data of the fields “FieldName1” and “FieldName1” from the tables “TableName1” and “TableName”, assign synonyms to the fields using the “HOW” operator, and connect them using a certain condition “TableConnectionCondition”.

From the received data, we select only data that meets the condition from “WHERE” “Data Selection Condition”. Next, we group the request by the field “Field Name1”, while summing “Field Name2”. We create totals for the field “Field Name1” and the final field “Field Name2”.

The last step is to sort the request using the ORDER BY construct.

General designs

Let's look at the general structures of the 1C 8.2 query language.

FIRSTn

Using this operator, you can get the n number of first records. The order of the records is determined by the order in the query.

SELECT FIRST 100
Banks.Name,
Banks. Code AS BIC
FROM
Directory.Banks HOW Banks
ORDER BY
Banks.Name

The request will receive the first 100 entries of the “Banks” directory, sorted alphabetically.

ALLOWED

This design is relevant for working with the mechanism. The essence of the mechanism is to restrict reading (and other actions) to users for specific records in a database table, and not the table as a whole.

If a user tries to use a query to read records that are inaccessible to him, he will receive an error message. To avoid this, you should use the “ALLOWED” construction, i.e. the request will read only records that are allowed to it.

SELECT ALLOWED
Repository of Additional Information. Link
FROM
Directory.Repository of Additional Information

VARIOUS

Using “DIFFERENT” will prevent duplicate lines from entering the 1C query result. Duplication means that all request fields match.

SELECT FIRST 100
Banks.Name,
Banks. Code AS BIC
FROM
Directory.Banks HOW Banks

EmptyTable

This construction is used very rarely to combine queries. When joining, you may need to specify an empty nested table in one of the tables. The “EmptyTable” operator is just right for this.

Example from 1C 8 help:

SELECT Link.Number, EMPTY TABLE.(No., Item, Quantity) AS Composition
FROM Document.Expense Invoice
COMBINE EVERYTHING
SELECT Link.Number, Contents.(LineNumber, Product, Quantity)
FROM Document.Invoice Document.Invoice.Composition.*

ISNULL

A very useful feature that allows you to avoid many mistakes. YesNULL() allows you to replace the NULL value with the desired one. Very often used in checking for the presence of a value in joined tables, for example:

CHOOSE
Nomenclature Ref. Link,
IsNULL(Item Remaining.QuantityRemaining,0) AS QuantityRemaining
FROM

Can be used in other ways. For example, if for each row it is not known in which table the value exists:

ISNULL(InvoiceReceived.Date, InvoiceIssued.Date)

HOW is an operator that allows us to assign a name (synonym) to a table or field. We saw an example of use above.

These constructions are very similar - they allow you to get a string representation of the desired value. The only difference is that REPRESENTATION converts any values ​​to a string type, while REPRESENTATIONREF converts only reference values. REFERENCE REPRESENTATION is recommended to be used in data composition system queries for optimization, unless, of course, the reference data field is planned to be used in selections.

CHOOSE
View(Link), //string, for example “Advance report No. 123 dated 10/10/2015
View(DeletionMark) AS DeleteMarkText, //string, “Yes” or “No”
ViewReferences(DeletionMark) AS DeleteMarkBoolean //boolean, True or False
FROM
Document.Advance Report

EXPRESS

Express allows you to convert field values ​​to the desired data type. You can convert a value to either a primitive type or a reference type.

Express for a reference type is used to restrict the requested data types in fields of a complex type, often used to optimize system performance. Example:

EXPRESS(TableCost.Subconto1 AS Directory.Cost Items).Type of ActivityForTaxAccountingCosts

For primitive types, this function is often used to limit the number of characters in fields of unlimited length (such fields cannot be compared with). To avoid the error " Invalid parameters in comparison operation. Cannot compare fields
unlimited length and fields of incompatible types", you need to express such fields as follows:

EXPRESS(Comment AS Line(150))

DIFFERENCEDATE

Get 267 video lessons on 1C for free:

An example of using IS NULL in a 1C request:

SELECT * FROM
Ref
LEFT CONNECTION RegisterAccumulations.ProductsInWarehouses.Remaining AS Product Remaining
Software NomenclatureRef.Link = Sold GoodsCommitteesRemains.Nomenclature
WHERE NOT Remaining Products. QuantityRemaining IS NULL

The data type in a query can be determined by using the TYPE() and VALUETYPE() functions, or by using the logical REFERENCE operator. The two functions are similar.

Predefined values

In addition to using passed parameters in queries in the 1C query language, you can use predefined values ​​or . For example, transfers, predefined directories, charts of accounts, and so on. For this, the “Value()” construct is used.

Usage example:

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

WHERE Counterparties.Type of Contact Information = Value(Enumeration.Types of Contact Information.Phone)

WHERE Account Balances.Accounting Account = Value(Chart of Accounts.Post-accounting.ProfitsLoss)

Connections

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.

For example:

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

RIGHT JOIN in 1C 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:

FROM

FULL CONNECTION
Directory.Banks HOW Banks

BY

The query language will return both tables completely only if the condition to join the records 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.

Associations

The JOIN and JOIN ALL constructs combine two results into one. Those. the result of performing two will be “merged” into one, common one.

That is, the system works exactly the same as regular ones, only for a temporary table.

How to use INDEX BY

However, one point should be taken into account. Building an index on a temporary table also takes time to complete. Therefore, it is advisable to use the “ ” construction only if it is known for sure that there will be more than 1-2 records in the temporary table. Otherwise, the effect may be the opposite - the performance of indexed fields does not compensate for the time it takes to build the index.

CHOOSE
Currency rates Latest cross-section. Currency AS Currency,
Currency rates Latest cross-section.
PUT Currency Rates
FROM
Information Register.Currency Rates.Last Slice(&Period,) AS Currency RatesLast Slice
INDEX BY
Currency
;
CHOOSE
PricesNomenclature.Nomenclature,
PricesNomenclatures.Price,
PricesNomenclatures.Currency,
Currency rates.Rate
FROM
Information Register.Nomenclature Prices.Last Slice(&Period,
Nomenclature B (&Nomenclature) AND PriceType = &PriceType) AS PriceNomenclature
LEFT JOIN Currency Rates AS Currency Rates
Software PricesNomenclatures.Currency = Currency Rates.Currency

Grouping

The 1C query language allows you to use special aggregate functions when grouping query results. Grouping can also be used without aggregate functions to “eliminate” duplicates.

The following functions exist:

Amount, Quantity, Number of different, Maximum, Minimum, Average.

Example #1:

CHOOSE
Sales of Goods and Services Goods. Nomenclature,
SUM(Sales of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Sales of GoodsServicesGoods.Amount) AS Amount
FROM

GROUP BY
Sales of Goods and Services Goods. Nomenclature

The query receives all lines with goods and summarizes them by quantity and amounts by item.

Example No. 2

CHOOSE
Banks.Code,
QUANTITY(DIFFERENT Banks.Link) AS Number Of Duplicates
FROM
Directory.Banks HOW Banks
GROUP BY
Banks.Code

This example will display a list of BICs in the “Banks” directory and show how many duplicates exist for each of them.

Results

Results are a way to obtain data from a system with a hierarchical structure. Aggregate functions can be used for summary fields, just as for groupings.

One of the most popular ways to use results in practice is batch write-off of goods.

CHOOSE




FROM
Document. Sales of Goods and Services. Goods HOW to Sale of Goods and Services Goods
ORDER BY

RESULTS
SUM(Quantity),
SUM(Sum)
BY
Nomenclature

The result of the query will be the following hierarchical:

General results

If you need to get totals for all “totals”, use the “GENERAL” operator.

CHOOSE
Sales of Goods and Services Goods. Nomenclature AS Nomenclature,
Sales of Goods and Services Goods. Link AS Document,
Sales of Goods and Services Goods. Quantity AS Quantity,
Sales of Goods and Services Goods. Amount AS Amount
FROM
Document. Sales of Goods and Services. Goods HOW to Sale of Goods and Services Goods
ORDER BY
Sales of Goods and Services Goods. Link. Date
RESULTS
SUM(Quantity),
SUM(Sum)
BY
GENERAL,
Nomenclature

As a result of executing the request, we get the following result:

In which 1 level of grouping is the aggregation of all necessary fields.

Arranging

The ORDER BY operator is used to sort the result of a query.

Sorting for primitive types (string, number, boolean) follows the usual rules. For reference type fields, sorting occurs by the internal representation of the link (the unique identifier), rather than by code or by reference representation.

CHOOSE

FROM
Directory.Nomenclature AS Nomenclature
ORDER BY
Name

The request will display a list of names in the nomenclature directory, sorted alphabetically.

Auto-order

The result of a query without sorting is a chaotically presented set of rows. 1C platform developers do not guarantee that rows will be output in the same sequence when executing identical queries.

If you need to display table records in a constant order, you must use the Auto-Order construct.

CHOOSE
Nomenclature.Name AS Name
FROM
Directory.Nomenclature AS Nomenclature
AUTO ORDER

Virtual tables

Virtual tables in 1C are a unique feature of the 1C query language that is not found in other similar syntaxes. A virtual table is a quick way to obtain profile information from registers.

Each register type has its own set of virtual tables, which may differ depending on the register settings.

• cut of the first;
• cut of the latter.

• leftovers;
• revolutions;
• balances and turnover.

• movements from subconto;
• revolutions;
• speed Dt Kt;
• leftovers;
• balances and turnover
• subconto.

• base;
• graph data;
• actual period of validity.

For the solution developer, the data is taken from one (virtual) table, but in fact the 1C platform takes it from many tables, transforming them into the required form.

CHOOSE
Products in Warehouses Remains and Turnover. Nomenclature,
ProductsInWarehousesRemainingAndTurnover.QuantityInitialRemaining,
ProductsInWarehousesRemainsAndTurnover.QuantityTurnover,
GoodsInWarehousesRemainsAndTurnover.QuantityIncoming,
ProductsIn WarehousesRemainsAndTurnover.QuantityConsumption,
ProductsInWarehousesRemainingsAndTurnover.QuantityFinalRemaining
FROM
RegisterAccumulations.GoodsInWarehouses.RemainsAndTurnover AS GoodsInWarehousesRemainsAndTurnover

This query allows you to quickly retrieve a large amount of data.

Virtual Table Options

A very important aspect of working with virtual tables is the use of parameters. Virtual table parameters – specialized parameters for selection and configuration.

For such tables, it is considered incorrect to use selection in the “WHERE” construction. In addition to the fact that the query becomes suboptimal, it is possible to receive incorrect data.

An example of using these parameters:

Register of Accumulations. Goods in Warehouses. Balances and Turnovers (& Beginning of the Period, & End of the Period, Month, Movements and Borders of the Period, Nomenclature = & Required Nomenclature)

Algorithm for virtual tables

For example, the most used virtual table of the “Remains” type stores data from two physical tables – balances and movements.

When using a virtual table, the system performs the following manipulations:

1. We get the closest calculated value in terms of date and measurements in the totals table.
2. We “add” the amount from the movement table to the amount from the totals table.

Such simple actions can significantly improve the performance of the system as a whole.

Using the Query Builder

Query Builder– a tool built into the 1C Enterprise system that greatly facilitates the development of database queries.

The query builder has a fairly simple, intuitive interface. Nevertheless, let's look at using the query constructor in more detail.

The query text constructor is launched from the context menu (right mouse button) in the desired place in the program code.

Description of the 1C request constructor

Let's look at each tab of the designer in more detail. The exception is the Builder tab, which is a topic for another discussion.

Tables and Fields tab

This tab specifies the data source and fields that need to be displayed in the report. In essence, the constructions SELECT.. FROM are described here.

The source can be a physical database table, a virtual register table, temporary tables, nested queries, etc.

In the context menu of virtual tables, you can set virtual table parameters:

Connections tab

The tab is used to describe connections of several tables and creates constructions with the word CONNECTION.

Grouping tab

On this tab, the system allows you to group and summarize the required fields of the table result. Describes the use of the constructions GROUP BY, SUM, MINIMUM, AVERAGE, MAXIMUM, QUANTITY, NUMBER OF DIFFERENT.

Conditions tab

Responsible for everything that comes in the request text after the WHERE construction, i.e. for all the conditions imposed on the received data.

Advanced tab

Tab Additionally replete with all sorts of parameters that are very important. Let's look at each of the properties.

Grouping Selecting records:

• First N– a parameter that returns only N records to the query (the FIRST operator)
• No duplicates– ensures the uniqueness of the received records (DIFFERENT operator)
• Allowed– allows you to select only those records that the system allows you to select taking into account (ALLOWED construction)

Grouping Request type determines what type of query will be: retrieving data, creating a temporary table, or destroying a temporary table.

Below there is a flag Lock received data for later modification. It allows you to enable the ability to set data locking, which ensures the safety of data from the moment it is read until it is changed (relevant only for the Automatic locking mode, design FOR CHANGE).

Joins/Aliases Tab

On this tab of the query designer, you can set the ability to join different tables and aliases (the HOW construct). The tables are indicated on the left side. If you set the flags opposite the table, the UNITE construction will be used, otherwise - UNITE ALL (differences between the two methods). On the right side, the correspondence of fields in different tables is indicated; if the correspondence is not specified, the query will return NULL.

Order tab

This specifies the order in which the values ​​are sorted (ORDER BY) - descending (DESC) or ascending (ASC).

There is also an interesting flag - Auto-order(in the request - AUTO ORDERING). By default, the 1C system displays data in a “chaotic” order. If you set this flag, the system will sort data by internal data.

Query Batch tab

On the query designer tab, you can create new ones and also use it as a navigation. In the request text, packets are separated by the symbol “;” (comma).

“Query” button in the query designer

In the lower left corner of the request designer there is a Request button, with which you can view the request text at any time:

In this window, you can make adjustments to the request and execute it.

Using the Query Console

The Query Console is a simple and convenient way to debug complex queries and quickly obtain information. In this article, I will try to describe how to use the Query Console and provide a link to download the Query Console.

Let's take a closer look at this tool.

Download 1C query console

First of all, to start working with the query console, you need to download it from somewhere. Treatments are usually divided into two types - controlled forms and conventional ones (or, sometimes, they are called 8.1 and 8.2/8.3).

I tried to combine these two views in one processing - the desired form opens in the desired operating mode (in managed mode, the console only works in thick mode).

Description of the 1C query console

Let's start looking at the query console with a description of the main processing panel:

In the query console header, you can see the execution time of the last query with millisecond accuracy, this allows you to compare different designs in terms of performance.

The first group of buttons in the command bar is responsible for saving current queries to an external file. This is very convenient; you can always return to writing a complex request. Or, for example, store a list of typical examples of certain designs.

On the left, in the “Request” field, you can create new requests and save them in a tree structure. The second group of buttons is responsible for managing the list of requests. Using it you can create, copy, delete, move a request.

• Executerequest– simple execution and results
• Execute package– allows you to view all intermediate queries in a batch of queries
• Viewing temporary tables– allows you to see the results that temporary queries return on a table

Request parameters:

Allows you to set the current parameters for the request.

In the query parameters window, the following is interesting:

• Button Get from request automatically finds all parameters in the request for the convenience of the developer.
• Flag Common parameters for all requests– when installed, its processing does not clear the parameters when moving from request to request in the general list of requests.

Set a parameter with a list of values It’s very simple, just when choosing a parameter value, click on the clear value button (cross), the system will prompt you to select the data type, where you need to select “Value List”:

Also in the top panel there is a button for calling up the query console settings:

Here you can specify parameters for autosaving queries and query execution parameters.

The request text is entered into the console request field. This can be done by simply typing a query test or by calling a special tool - the query designer.

The 1C 8 query designer is called from the context menu (right mouse button) when you click on the input field:

This menu also has such useful functions as clearing or adding line breaks (“|”) to the request, or receiving the request code in this convenient form:

Request = New Request;
Request.Text = ”
|SELECT
| Currencies.Link
|FROM
| Directory.Currencies AS Currencies”;
RequestResult = Request.Execute();

The lower field of the query console displays the query result field, which is why this processing was created:

Also, the query console, in addition to the list, can display data in the form of a tree - for queries containing totals.

Query optimization

One of the most important points in increasing the productivity of 1C enterprise 8.3 is optimizationrequests. This point is also very important when passing the certification. Below we will talk about typical reasons for non-optimal query performance and optimization methods.

Selections in a virtual table using the WHERE construct

It is necessary to apply filters to the virtual table details only through the VT parameters. Under no circumstances should you use the WHERE construct for selection in a virtual table; this is a serious mistake from an optimization point of view. In the case of selection using WHERE, in fact, the system will receive ALL records and only then select the necessary ones.

RIGHT:

CHOOSE

FROM
Register of Accumulations. Mutual settlements with Participants of Organizations. Balances (
,
Organization = &Organization
AND Individual = &Individual) HOW Mutual settlements with Participants of Organizations Balances

WRONG:

CHOOSE
Mutual settlements with Participants of Organizations Balances. Amount Balance
FROM
Register of Accumulations. Mutual settlements with Participants of Organizations. Balances (,) HOW Mutual settlements with Participants of Organizations Balances
WHERE
Mutual settlements with Participants of Organizations Balances. Organization = & Organization
AND Mutual settlements with Participants of Organizations Balances. Individual = &Individual

Getting the value of a field of a complex type using a dot

When receiving data of a complex type in a query through a dot, the system connects with a left join exactly as many tables as there are types possible in the field of the complex type.

For example, it is highly undesirable for optimization to access the register record field – registrar. The registrar has a composite data type, among which are all possible document types that can write data to the register.

WRONG:

CHOOSE
Record Set.Recorder.Date,
RecordSet.Quantity
FROM
RegisterAccumulations.ProductsOrganizations AS SetRecords

That is, in fact, such a query will access not one table, but 22 database tables (this register has 21 registrar types).

RIGHT:

CHOOSE
CHOICE
WHEN ProductsOrg.Registrar LINK Document.Sales of Products and Services
THEN EXPRESS(ProductsOrg.Registrar AS Document.Sales of GoodsServices).Date
WHEN GoodsOrg.Registrar LINK Document.Receipt of GoodsServices
THEN EXPRESS(GoodsOrg.Registrar AS Document.Receipt of GoodsServices).Date
END AS DATE,
ProductsOrg.Quantity
FROM
RegisterAccumulations.ProductsOrganizations AS ProductsOrganization

Or the second option is to add such information to the details, for example, in our case, adding a date.

RIGHT:

CHOOSE
ProductsOrganizations.Date,
ProductsOrganizations.Quantity
FROM
Register of Accumulations. Goods of Organizations AS Goods of Organizations

Subqueries in a join condition

For optimization, it is unacceptable to use subqueries in join conditions; this significantly slows down the query. It is advisable to use VT in such cases. To connect, you need to use only metadata and VT objects, having previously indexed them by connection fields.

WRONG:

CHOOSE …

LEFT JOIN (
SELECT FROM RegisterInformation.Limits
WHERE …
GROUP BY...
) BY …

RIGHT:

CHOOSE …
PUT Limits
FROM Information Register.Limits
WHERE …
GROUP BY...
INDEX BY...;

CHOOSE …
FROM Document. Sales of Goods and Services
LEFT JOIN Limits
BY …;

Joining Records with Virtual Tables

There are situations when, when connecting a virtual table to others, the system does not work optimally. In this case, to optimize the performance of the query, you can try placing the virtual table in a temporary one, not forgetting to index the joined fields in the temporary table query. This is due to the fact that VTs are often contained in several physical DBMS tables; as a result, a subquery is compiled to select them, and the problem turns out to be similar to the previous point.

Using selections based on non-indexed fields

One of the most common mistakes when writing queries is using conditions on non-indexed fields, this contradicts query optimization rules. The DBMS cannot execute a query optimally if the query includes selection on non-indexable fields. If you take a temporary table, you also need to index the connection fields.

There must be a suitable index for each condition. A suitable index is one that satisfies the following requirements:

1. The index contains all the fields listed in the condition.
2. These fields are at the very beginning of the index.
3. These selections are consecutive, that is, values ​​that are not involved in the query condition are not “wedged” between them.

If the DBMS does not select the correct indexes, the entire table will be scanned - this will have a very negative impact on performance and can lead to prolonged blocking of the entire set of records.

Using logical OR in conditions

That's all, this article covered the basic aspects of query optimization that every 1C expert should know.

A very useful free video course on query development and optimization, I strongly recommend for beginners and more!

The number in 1C 8 is a primitive type value Number. The numeric type can represent any decimal number. With numerical data it is possible to perform basic arithmetic operations: addition, subtraction, multiplication and division. The maximum number of digits for a number in 1s 8: 38 characters.

Number literals: a set of digits of the form: [+|-](0|1|2|3|4|5|6|7|8|9)[.(0|1|2|3|4|5|6| 7|8|9)] “.” is used as a separator between the integer and fractional parts. (dot).

Example 1: Let's create a numeric variable

NumberPi = 3.14;

Converting values ​​of another type to a number

You can convert values ​​of a string type or a Boolean type ( Boolean). Type value Boolean is converted to a number according to the following rules:

• Lie converted to 0;
• True is converted to 1.

Type value Line is converted to a number if it is the string representation of a numeric literal.

There is a special function for conversion Number(<Значение>) , which returns the resulting number if the conversion succeeds and produces an error message otherwise.

Example 2. Convert the string “1.25” to 1c number

String = "1.25"; ReceivedNumber = Number(String);

After executing this code in the variable ReceivedNumber will contain a numeric value 1.25

Example 3: Convert the string “Hello world!” to a number.

String = "Hello world!"; ReceivedNumber = Number(String);

If you try to execute this code, you will receive an error message: “Converting a value to a type Number cannot be fulfilled."

Functions for working with values ​​of type Number in 1s 8.3

This section will discuss the main functions of working with numbers in 1c 8 and provide examples of their use.

Cel

Cel(<Число>) . Returns the integer part of the number passed in the parameter.

Example 4. Determine whether the number 121 is divisible by 11.

Dividend = 121; Divisor = 11; Result = Dividend / Divisor; If Goal(Result) = Result Then Report("Divides entirely"); Else Report("Does not divide entirely"); endIf;

The result of executing this code will be the display of the message “Divide by whole” on the screen.

Okr.

Okr(<Число>, <Разрядность>, <РежимОкругления> ). The function rounds the number passed in the first parameter to the decimal place passed in the second parameter. The bit value can be either zero (rounding to an integer) or negative (rounding to the corresponding digit of the integer). Parameter Rounding Mode can take values:

• 0 (or Rounding Mode. Round 15 as 10). Rounds down, that is, rounding 1.5 will return 1;
• 1 (or Rounding Mode. Round 15 as 20). Rounds up, that is, rounding 1.5 will return 2;

Example 5. In order to better understand the principles of rounding, let’s consider rounding up and down to a whole number, on a number of numbers from 1.1 to 1.9

Array = New Array; Step = 0.1; Number = 1; Bye Number< 1.9 Цикл Число = Число + Шаг; Массив.Добавить(Число); КонецЦикла; Для Каждого Стр Из Массив Цикл Сообщить("Исходное: "+Стр+" В меньшую: "+Окр(Стр,0,0)+" В большую:"+Окр(Стр,0,1)); КонецЦикла;

Pow

Pow(<Основание>, <Показатель>) . Raises the number passed in the first parameter to the power passed in the second parameter.

Example 6: Take the square root of the number 144 and then square it, the result should be 144 again.

Number = 144; KvRoot = Pow(Number, 1/2); Number = Pow(KvRoot, 2); Report(Number);

The result of executing the code will be the display of the number 144.

Other functions for working with Number type values ​​in 1s 8.3

• Log(<Число>) . Gets the natural logarithm of the number passed in the parameter;
• Log10(<Число>) . Gets the decimal logarithm of the number passed as a parameter;
• Sin(<Угол>) . Gets the sine of an angle specified in radians. The angle is passed as a parameter;
• Cos(<Угол>) . Gets the cosine of the angle;
• Tan(<Угол>) . Gets the tangent of an angle;
• ASin(<Угол>) . Gets the arcsine of the angle;
• ACos(<Угол>) . Gets the arc cosine of an angle;
• ATan(<Угол>) . Gets the arctangent of an angle;

Exp(<Число>). Gets the exponent of the number passed to the parameter.

As a rule, learning any programming language begins with an example of writing the first simplest program (“Hello world!”). This is done in order to clearly demonstrate the work with basic syntactic structures. We will not make an exception to this generally accepted way of presenting material when exploring a new development environment, and therefore our first article should be considered in the same vein. In it we will analyze in detail the answers to the following questions regarding programming on the 1C:Enterprise 8 platform:

• Where and with what help to write program code in the built-in 1C language?
• What are software modules, what are the rules for working with them?
• What is a variable, how to work with it, how and where to declare it?
• What comparison, assignment, and conditional operators are there and how do you use them?
• Boolean operations - what are they and how to work with them?
• Why do we need loops and how to use them?

The article will be useful to all those who are not yet familiar with development on the 1C:Enterprise 8 platform, but want to learn how to program on 1C.

Applicability

The material is relevant for the 1C:Enterprise 8 platform, edition 8.2. and 8.3.

Variables and Operators

In this article we begin to study the built-in language 1C:Enterprise 8. The executable code is contained in program modules.

There are quite a large number of modules that are designed to handle various events.

So, the user’s login to the system is processed in one module, and the processing of the user’s click on a certain button is processed in a completely different one.

Thus, each module describes the behavior of the configuration at a certain point. The module contains, first of all, a section for describing variables. Those. we can declare some variables in the module.

In the future, they can be used in procedures and functions of this module. If a variable is defined with the Export keyword, it will be available outside of this module. Example variable declaration line:

Perem Warehouse, Division, Storekeeper Export;

After the declaration of variables there is a section of procedures and functions.

Behind them is a section of the main program that will be executed when this module is accessed.

For example, in a section of the main program you can initialize variables, i.e. give them some initial values:

State=1;
NewExpression=2;
Result=3;

A module can be thought of as a combination of different operators that perform different actions we need.

The operator separator is the symbol ";" (semicolon). This sign marks the end of the statement. Those. the operator can be written as follows:

Result=100X200
+400
-600;

It does not matter how many lines the statement is located on.

Of course, it is often more convenient and clearer to place the operator on one line, but sometimes the operators are quite long (the number of lines can reasonably reach several dozen).

The semicolon does not need to be placed in the final statement of a given construct, for example, a procedure. Those. the following code will work:

Procedure CalculateValue()

InitialValue = 100;
IntermediateValue = InitialValue/5;
FinalValue = InitialValue+IntermediateValue

End of Procedure

However, it is better to use a semicolon in the final statement. It is possible that over time the construction will be continued, and the final statement will no longer be final. We will have to specifically monitor this situation.

Variables are designed to store some value of any data type. They are used for intermediate storage of information for processing.

In almost any software module that performs some action, there are various variables. Typing variables by values ​​in Platform 1C:Enterprise 8 is soft.

For example, a variable can contain a value of one data type, and then a few lines later - another type:

Created = False;
Created = True;
Created =100;

In the first two statements, the value of the variables is Boolean, and in the third it changes to a numeric value. Those. typing depends on the value that is assigned to a given variable.
Variables can be described in two ways:

• implicit method (the mention on the left side of the assignment operator describes this variable, there is no preliminary description of the variable with the word Variable, i.e. there is no special section for describing variables);
• explicit description of variables (Variable Control Data;). Explicit declaration of variables is used, for example, if the subsequent passing of this variable to a function is intended.

Variables are named using the classic identifier description. The identifier consists of letters, numbers and underscores. The identifier must begin with either a letter or an underscore.

In this case, the name of the variable should reflect the meaning of this variable. Single letter variable names (like A, B, C) are bad examples. They do not reflect the essence of the variables.

Examples of correct variable names: Counter (increasing variable for a loop), Counterparty. If the variable name contains several words, then each new word, for clarity, should begin with a capital letter.

Reserved words such as Procedure, Function, Loop, EndLoop, etc. cannot be used in variable names. (these structures are highlighted in red in the program module).

Reserved words are built-in language operators and there are a fairly small number of them. All of them are presented in Syntax Assistant.

It should be noted that data types are not reserved words (for example, Array, Boolean, True, False). The system will accept such variable names correctly.

Case does not matter when writing program code. For example, the word Procedure can be written with either a capital or a small letter. Moreover, large and small letters can be alternated within a word.

This does not matter to the Platform. However, according to the rules of good manners, the beginning of a word should be written with a capital letter, and all other letters with small letters.

Regarding the language. You can use Russian, English, or a combination of the two languages. If it is convenient for someone, you can safely use English to write program code, as well as combine Russian and English. This is not important for the Platform.

Many names in English are quite difficult to remember. When using a combination of two languages, the readability of the program code deteriorates.

Boolean operations

Comparison operators often use Boolean logic that returns True or False.

For example, in a conditional operator you can compare: If Event = Sale Then the algorithm will follow one branch (i.e., if the value is True), if the condition is False, another branch of the algorithm will be executed.

The conditions can be quite complex, they can be combined, and the following operators are used: AND, OR, and NOT. So, for the AND operator:

Truth And Truth = Truth;
True And False = False;
False And True = False;
False AND False = False.

For the OR operator, it is enough that one of the operands is equal to True, then the value of the combination will be True. The value False is obtained only when both operands are False.

The NOT operator simply inverts the current value (False to True, True to False).

Using a combination of these operators, you can build quite complex conditions. When composing complex conditional statements, you should consider priorities.

The NOT operator has the highest precedence, followed by the AND operator, followed by the OR operator. Whatever is enclosed in parentheses has the highest priority and is executed first.

For example, let’s set priorities (execution sequence) for operations in the given expression:

NOT(Condition1 OR Condition2) AND Condition3 OR Condition4
1.Result1 = (Condition1 OR Condition2);
2. Result2 = NOT Result1;
3. Result3 = Result2 AND Condition1;
4. Result = Result3 OR Condition4;

There is a conversion rule:

NOT (Condition1 OR Condition2) = NOT Condition1 AND NOT Condition2.

However, one should not always strive to simplify the expression, since often, logically, an expanded expression is easier to read.

Assignment operator

The assignment operator should not be confused with the equality operator, even though they have the same spelling.

The principle of the assignment operator is that the left value (the variable on the left side) is assigned the value that is to the right of the equal sign. Let's look at an example:

Variable1 = Variable2 = Variable3;

Variable1 is assigned the equality value from Boolean logic, i.e. True if Variable2 = Variable3, or False otherwise.

When testing for the position of a novice programmer, a task quite often used is to swap the values ​​of two variables.

This problem is solved using the assignment operator and has two solutions.

Solution #1 using a temporary variable:
TemporaryVariable = Variable1;
Variable1 = Variable2;
Variable2 = TemporaryVariable;

Solution #2:
Variable1 = Variable1 + Variable2;
Variable2 = Variable1 – Variable2;
Variable1 = Variable1 – Variable2;

Conditional operator

There is such an operator If, after which it is necessary to describe some condition (the condition itself can be quite large). The condition is followed by the word Then and the statements to be executed.

This may be followed by the keyword Else and a series of other statements. If there are several different conditions, you can use a series of keywords OtherwiseIf(see example below). The entire construction must end with the keyword EndIf, followed by a semicolon.

In addition to simple and multiple conditions, there is a shortened form of the conditional operator: ?(Condition, Expression1, Expression2);

If the condition is true, then it will be executed Expression1, otherwise – Expression2. Example code: ExpensiveProduct = ?(Product.Price>100000, True, False);

In practice, instead of comparison records with the value True (Lie) type:

If Variable = True Then
And
If Variable = False Then

the equivalent notations actually used are:

If Variable Then
And
If NOT Variable Then

Cyclic operators

For any type of loop, it is necessary to explicitly indicate the end of this loop using the keyword End of the Cycle. There are several types of cycles.

Loop on a counter– a cycle with a fixed number of repetitions. The condition for exiting the cycle is exceeding the limit value. Example use for calculating the value of A!

A = 5;
Factorial = 1;
For Counter = 1 By A Cycle
Factorial = Factorial * Counter;
EndCycle;

Loop by condition– the condition of the given loop is fulfilled as long as it is true. Example:

RemainingAmount = 1000;
Additional Product Price = 243;
Quantity = 0;
While Remaining Amount>0 Cycle
Quantity = Quantity+1;
Remaining Amount = Remaining Amount – Quantity*Price of Additional Product;
Price of Additional Product = Price of Additional Product * 0.8;
End of the Cycle
Quantity = Quantity-1;

This cycle calculates how many units of a product can be purchased for a given amount (1000 rubles) if, after purchasing each unit of a product, its previous price is multiplied by a factor of 0.8. The original price of the product is 243 rubles.

An example of an error when using this type of loop for beginners is an eternal loop, when the loop condition is initially true, but within the loop itself it does not change in any way.

Cycle through collections (another name for everyone).

There are quite a large number of collections in the Platform (these are containers that contain elements of a certain type).

You can iterate through the elements of a collection using a special type of loop.

For example, there is an array of numbers, you need to calculate the sum of all elements of the array:

Amount = 0;
For Each Element From Array Loop
Sum=Sum+Item;
EndCycle;

There are special operators for loops: Continue And Abort.

If at some point in the loop the execution of further operators of a given loop becomes meaningless, then the operator is used to return to the beginning of the loop and organize its next loop Continue.

Operator Abort allows the loop to end execution even if the loop condition is true.

This concludes our first acquaintance with development in the internal 1C language.

What about Hello World? We haven't written it yet, have we? Yes, but nothing prevents you from doing it yourself, because... knowledge is ALREADY enough. Well, if it doesn’t work out, then you can take a look here.

The following apply logical operations:

NOT logical NOT (negation); And logical AND;

OR is logical OR.

The operands of logical operations must be logical expressions. In table 2.5 shows the results of logical operations on the logical expressions LP1 and LP2, taking the values true(I) or lie(L).

Truth table

Table 2.5

The negation operator is a unary operator located to the left of the operand. All other operations considered are binary.

2.6.5. PRIORITY OF OPERATIONS

All 1C operations are performed in the expression from left to right in accordance with their priority (precedence), that is, if two consecutive operations have equal priority, the left operation is performed initially. Subexpressions enclosed in parentheses are evaluated first. In table 2.6 1C operations are arranged in descending order of their priority.

Operation priority

Table 2. b

% *,/ +, NOT AND OR<, <=, >, >=, =, <>

Comment. Each table cell contains operations with equal priority.

Example:

Since logical operations are older than relational operations, the following logical expression is incorrect:

3>2and4< 5 // Так неверно

since the logical subexpression 2 and 4 is initially evaluated

whose operands do not exist true or lie. And this is not true.

But the logical expression is correct

(3 > 2) and (4< 5) // Это true Example. Calculate the result of a Boolean expression

(x/a = 1) or (b/(a + b)< 1) и не (б = а) или (х <>6) when x = 6.0, A= 2.0 and b = 3.0.

Having calculated the result of operations of subexpressions enclosed in parentheses, we obtain:

lie or true and not lie or lie.

lie or true And true or lie. After execution true And true: false or true or lie. Final result: true.

2.7. ARRAYS

Array is a data object containing multiple values ​​that are accessed by their number ( index).

The number of elements in an array is called its size. The array size can only be an integer literal constant.

Variable operator;

declares a one-dimensional array (vector) A of five elements. The array elements have the following names: a, a, a, a And A. In these names, values ​​1-5 are indexes of array elements.

An array is considered defined if the values ​​of all its elements are specified. To set the initial values ​​of array elements (array initialization), the following loop is possible:

for in = 1 to 5 cycle

Assignments

will change the values ​​of the 2nd and 5th elements of the array accordingly A.

In general, numeric expressions called index expressions. For example:

a = 9.1; // Change the value of the 3rd element of the array A

If an array index expression is evaluated with a non-integer value, then the integer part of that value is taken as the index. For example:

a = 9.1; // Change the value of the 1st element of the array A

The index value must not exceed the bounds of the array. So, when working with a previously declared array A of the five elements the operators are erroneous

Elements of the same array can be of different types. For example:

a = '25.11.01'; // Element of type Date Array elements can be of aggregate type. For example:

cEmployees = CreateObject(“Directory.Employees”);

table = CreateObject(“Table”);

An array cannot contain other arrays as elements.

An array can be a formal parameter of a software component (procedure or function). In this case, the size of the array is not specified, and the square brackets are retained. To determine the size of the array passed to the procedure (function), the built-in function Size is used.

Example:

procedure InitializationArray(a) variable, size;

sizeA = size(a); // Built-in function Size will return the size of the array A

for in = 1 in size cycle

a[in] = 1; // Now all array elements are equal to one endCycle;

endProcedure // Initialization of Array

procedure Execute() variable;

// Call a procedure that sets the initial values ​​of array elements

// Its actual parameter is the array name InitializeArray(a);

endProcedure // Execute