Simple logical expressions in 1s. Logical operations (1C enterprise). Data Composition System Expression Language

1. Introduction

2. 1C object structure

• Constants.
• Directories.
• Documentation.
• Document logs.
• Transfers.
• Reports.
• Processing.
• Charts of accounts.
• Types of subconto.
• Operation.
• Wiring.
• Registers.
• Calculation journals.
• Types of calculations.
• Settlement groups.
• Calendars.

• Text.
• XBase.
• AccountingResults.
• Correct Postings.
• Recalculation Rule.
• Subsequence.
• Periodic.
• ListValues.
• Value Table.
• Table.
• Request.
• Picture.
• FS (File System).

3. Language 1C. Modularity

4. General provisions for writing modules

4.1.Comments

4.2. Variable, procedure, and function names

The name of a variable, user procedure, or function can be any sequence of letters, numbers, and underscores "_" starting with a letter or underscore "_". Newly created names must not match reserved words of the language or names of existing procedures and functions available at the time of execution. Variable, procedure, and function names are recognized in a case-insensitive manner.

4.3. Reserved words

The following keywords are reserved and cannot be used as created variable names or declared procedure and function names. In this version of the language, each of the keywords has two representations - Russian and English. The English representation is traditional for programming languages. Keywords in Russian and English representation can be freely mixed in one source text. The case of the letters of the keywords does not matter. Below is a list of keywords in both views.

4.4. Software module structure

The structure of the software module can be divided into the following sections:

• variable definition section;
• procedures and functions section;
• section of the main program.

In a specific software module, any of the sections may be missing.

Variable definition section placed from the beginning of the module text to the first Procedure or Function statement or any executable statement. This section can only contain Variable variable declaration statements.

Procedures and functions section placed from the first Procedure or Function statement to any executable statement outside the body of the procedure or function description.

Main program section is placed from the first executable statement outside the body of procedures or functions to the end of the module. This section can only contain executable statements. The main program section is executed when the module is launched for execution (see “Types of program modules”). Usually, in a section of the main program, it makes sense to place operators for initializing variables with any specific values, which must be carried out before the first call to any of the procedures or functions of the module.

5. Format for describing expressions and type conversions

5.1. Data types in expressions and their recording format.

Let's take a closer look at the elements of expressions and the arithmetic operations used to work with them.

Numbers

Numbers are written in numbers from 0 to 9; write down if necessary a fractional number whole part separated from the fractional part by a dot ".".
For example:
45 is a positive integer
-12 is a negative integer
16.67 is a fractional positive number.

To work with numbers, you can use the provided arithmetic operations (see below).

Dates

Dates are written as a sequence of characters in the following format:
"DD.MM.YY"
or
"DD.MM.YYYY" ,
Where:
DD - day of the month. It must be written in two digits: if the day of the month is less than 9, a 0 is placed in front, for example “02”;
MM - month number, also written in two digits;
YY (YYYY) - year, can be written in two or four digits.

For example: "01/01/2007"

Please note that dates are indicated only in single quotes. With such a recording, a sequence with a symbol like “01/01/2007” will be perceived and processed by the 1C:Enterprise system as a date, and not as a sequence of characters “representing” the date. We'll tell you why this is important below.

In addition to specific dates, in the built-in language there is such a thing as an “empty date”. An empty date is specified by the sequence "00.00.00". Typically, an empty date is used for comparison. You can also get an empty date using the Date(0) conversion method.

Note.

There is no Time type in 1C, just like there is no DateTime type.

String values

String values ​​are sequences of characters enclosed in double or single quotes. Example: "Example string".

If you need to specify quotes in a string value, use 2 quote characters in a row. For example, when writing the contents of an operation in a formula, a string value of the form

" Example line ""string in quotes"""

will form a string in the contents of the operation

Example string "string in quotes"

In the considered example, in a string value:
the first quote character is the opening quote of the string value as a whole;
2 quotation marks in a row are an opening quotation mark and in the resulting string they are replaced by a single quotation mark;
3 quote characters in a row is a group consisting of: 2 quotes - a closing quote, which in the resulting string are replaced by a single quote character, and one quote - a closing quote of the string value as a whole.

As practice shows, for some reason specifying quotes in string values ​​causes some difficulties. Although there is nothing complicated about this, you just need to remember a simple rule: the first and last quotes are one character each, and all quotes inside them are two characters each.

String values ​​have the concept " empty line". An empty string is specified by a pair of quotes: "". An empty string has all the properties of a "normal" string value, but has no length.

5.2. Arithmetic operations

Now let's look at the arithmetically operations used in the 1C language (Op - “operand”):

However, the use of arithmetic operations has certain features, depending on the types of operands.

WITH numerical values You can use all the arithmetic operations listed above, with virtually no restrictions.

For string values Only the "+" operation is allowed, which in this case is called the operation concatenation (accession). This operation appends one string value to another.

For example, the result of the expression:

"Example string " + "January 2007"

there will be a line

Example string January 2007

Please note that string values ​​are concatenated to one another without modification - the length of the resulting string value is equal to the sum of the lengths of the connected string values. In order for the result of the concatenation operation to be a “readable” string, you should not forget about the trailing and leading spaces in the concatenated lines, as was done, for example, in the line “Example line”.

The concatenation operation can concatenate more than two string values ​​at the same time. For example, the following expression is acceptable:

"Ivanov" + " " + "Ivan" +" " + "Ivanovich"

The result will be the string

Ivanov Ivan Ivanovich

For date type values Addition and subtraction operations are valid, but the rules for using these operations are a little more complex than for numbers and strings.

You can add a number to the date, for example:

"02.08.2000"+2

and the result of the expression will be a date later than the first term by a number of days equal to the second term. In this case, the result will be 08/04/2000, or August 4, 2000.

"02.03.2000"-2

in this case, we will get a date earlier than the one being reduced. It should be noted that with such an increase or decrease in dates, a correct transition between months and years is performed, that is, the number of days in a month, leap years, and so on are taken into account. In our example, the result of the expression will, of course, not be 03/00/2000, but 07/29/2000 - February 29, 2000.

"02.10.2000" - "02.08.2000"

in this case, the result will be the number of days between the one being reduced and the one being subtracted.

As you've probably noticed, unlike number and string operations, date operations have a different type of expression result than the type of its operands. This should be kept in mind when writing formulas: if we write an expression like the one above into a formula to calculate the transaction date, we will most likely receive an error message.

"02.08.2000" + "02.10.2000"

and we won't even get an error message, but the result of such an expression has no physical meaning.

To have a more general understanding of expressions, it should be said that the operands of arithmetic operations can be not only specific numbers, strings or dates, but also expressions that result in numbers, strings, or dates.

For example, in the expression

“Depreciation for” + Format(WorkingDate(), “D MMMMYYYY”)

the second operand is expression, the result of which will be a string value representing the month in words and the year of the working date.

5.3. Automatic type conversion

In general, the operands of an expression must have the same type or types, as determined by the rules for constructing expressions. For example, numbers should be added with numbers, strings with strings, but both a date and a number can be subtracted from a date.

As you write expressions, you may need to convert the data types of individual expression operands according to rules. For this purpose, the built-in language has functions specially designed for this. Let us give an example of an expression already familiar to us:

"Depreciation for " + Format(WorkingDate(), "D MMMMYYYY")

In this expression, the second operand of WorkingDate(), which is of type "date", is converted to type "string" using the Format() function.

However, the 1C:Enterprise system can automatically convert operands before calculating expressions different types to one type, guided by type conversion rules.

For basic types The following conversion rules are defined:

Number -> String

If a number has no format settings, then its string representation is the full fixed-point representation of the number.

Date -> String

A date converted to a string type has the form DD.MM.YY, where YY is two digits of the year, MM is the numeric designation of the month (01, ..., 12), DD is the day of the month.

String -> Number

The string is converted to a number as long as possible. The resulting number is considered the result of the transformation. (For example, the string "1.22 Glass" will be converted to the number 1.22). If there is nothing at the beginning of the line that could be interpreted as a number, then the result is 0.

Date -> Number

The result of this conversion is a numerical representation of the date.

String -> Date

If the beginning of the line contains something that can be interpreted as a string representation of the date in the form DD.MM.YY, where YY is two digits of the year, MM is the numeric designation of the month (01, ..., 12), DD is the day month, the corresponding conversion will be made. Otherwise the date value will be null.

Number -> Date

An attempt is made to take an entire part of a Number and interpret it as a numerical representation of a date. If the number is negative, the result is a zero date.

So, first of all, you should remember that the type of the result of an expression is determined by the type of the first operand of this expression. The other operands are cast to the type of the first operand. Knowing this rule, you can both predict the type of result of the expression as a whole and control the type of the result.

For example, the result of the expression

2+"A"

will be the number 2, because the first operand of the expression is of a numeric type, and the second operand, in accordance with the type conversion rules, became equal to 0.

However, it is worth changing the expression a little, writing it like this:

""+ 2+"A"

how its result will change radically: the result of the expression will be the string "2A".

Why did it happen? Above we talked about the concept of an “empty string”, which has all the properties of a string value, including the “string” type. In our example, the empty string is the first operand of the expression, and when the expression was evaluated, all of its operands were converted to type "string" according to the type conversion rules.

One more example. We emphasized above that a value of type “date” is specified by a character string in single quotes, and the same sequence of characters, but in double quotes, is a string. Now we will illustrate this with examples.

As we have already said, the result of the expression "08/02/2000"+2 will be the date 08/04/2000. But by specifying the first operand in double quotes:

"02.08.2000"+2

we will get a completely different result - the string "02.08.20002".

Let's summarize. You can control the type of the result of the entire expression by setting the first operand to an empty value of the corresponding type. The advantage of this method is that an empty value does not change the result of the expression. For example, putting 0 at the beginning of the expression:

0+ "23"+45

we will get the number 68 as a result, because the expression as a whole is of type "number", and the character string, in accordance with the rules of type conversion, was successfully converted to the number 45.

Here it is appropriate to remember about the empty date. What will be the result of the expression "00.00.00"+ 2+"02.08.2000"? Of course, the date is 08/04/2000 (August 4, 2000), because the first operand of the expression was a date value.

On the contrary, by removing the empty date

2+"02.08.2000"

we will get 2451761 as a result - this is the number of days from date zero to August 2, 2000, plus 2 more days.

5.4. Logical comparison operators

> More

< Меньше

<>Not equal

These Boolean expressions can be used for all three basic data types.

In relation to the numeric data type, there are no problems; the standard number (integer or fraction) is compared.

For string values, comparison occurs to their so-called “scan codes” (in the understanding of 1C, such a code can be obtained using the CodeCharacter() method). The comparison is made character by character. So, if strings of equal length are compared, then the codes of the corresponding characters are compared sequentially. The comparison occurs before the first inequality in the line (if the line consists of more than one character). If strings are compared different lengths, then the missing values ​​in the operator are supplemented with empty string characters (not with a space character (Code = 32)). Those. the value of space (CodeCym(32)) is greater than the value of the empty string.

For date types, the date itself is compared (but it is possible that before comparison, 1C converts the date into a number that is equal to the number of days from Date(0)).

6. Rounding problems

For example, this situation:
There are 16 units of goods at a price (including VAT) of 110.50 UAH.
- total cost is 110.50 * 16 = 1768 UAH.
- VAT (at a rate of 20%, including) is 1768 * 20 / 120 = 294.67 UAH.

If now this product is sold “piece by piece” without any markup, the VAT will be different:
- VAT per unit of goods will be 110.50 * 20 / 120 = 18.42 UAH.
- VAT for all 16 units will be 18.42 * 16 = 294.72 UAH.

The difference in VAT between a “group” receipt and a “piece” sale is 5 kopecks. Having no added value, we still owe 5 kopecks to the budget in the form of tax on it.

Note that when goods arrived, we could not calculate VAT “by the piece” - calculating the tax amount in the cost of one unit of goods and multiplying by the quantity of goods. In this case, there may be even greater rounding losses. For example, if the price of one unit of goods is 3 kopecks, then what is VAT equal to?

When such “rounding results” occur, you should remember that the problem is not in the program, not in the calculation formulas, but in the greater (compared to “manual” accounting) accuracy and rigor of the calculation rules. By keeping manual records, an accountant often allows himself to adjust the calculation result by hiding these “pennies” in intermediate calculations.

IN typical configurations"1C:Enterprise", where possible, takes into account and correctly processes such situations. For example, if 3 units of goods with a total cost of 1 hryvnia are capitalized, then the first two units will be written off at a cost of 33 kopecks, and the last - 34 kopecks. But not all cases of rounding can be taken into account in the algorithms, including the above example with five kopecks of VAT cannot be correctly processed in the program.
In some cases, such errors can be avoided by setting a “suitable” price for the product, so that VAT is calculated entirely from it, without any remainder. But this rule applies only to sales prices - you don’t have to choose purchase prices. In other cases, program users need to analyze where the “hanging pennies” came from and decide what to do with them.

7. Limitations of the V77 system

Number
maximum number of values ​​= 64 (64 characters).

Line
Maximum number of values ​​= limited by free physical memory (without paging file), It is assumed that there cannot be more than 2^32-1 characters.

Document
Document - 9999 types of documents can be created.
A document can have and work correctly with no more than 9999 lines.
The maximum code length is 20, i.e. a total of (27+33+10)^20 documents could theoretically be created (70^20 = 7.9792266297612001e+36).
There can be no more in total in the system (length of ID code in the journal = 9), practically the length of the ID is 6 characters (3 per URIB) 36^6 = 2176782336 documents.
The number of entered documents with the same time is limited to 36^4 = 1679616

Ukrainian 1C forum: everything about 1C 8.3, 1C 8.2, 1C 8.1, 1C 8.0, 1C 7.7 _ Data composition system _ Data composition system expression language

Content
1 Data composition system expression language
2 Literals
2.1 String
2.2 Number
2.3 Date
2.4 Boolean
2.5 Meaning
3 Operations on numbers
3.1 Unary –
3.2 Unary +
3.3 Binary -
3.4 Binary+
3.5 Work
3.6 Division
3.7 Remainder
4 String operations
4.1 Concatenation (Binary +)
4.2 Similar
5 Comparison operations
5.1 Equal
5.2 Not equal
5.3 Less
5.4 More
5.5 Less than or equal to
5.6 Greater than or equal to
5.7 Operation B
5.8 Operation of checking the presence of a value in a data set
5.9 Operation of checking a value for NULL
5.10 Operation of checking a value for NULL inequality
6 Logical operations
6.1 Operation NOT
6.2 Operation I
6.3 OR operation
7 Aggregate functions
7.1 Amount
7.2 Quantity
7.3 Number of different
7.4 Maximum
7.5 Minimum
7.6 Average
8 Other operations
8.1 SELECT operation
9 Rules for comparing two values
10 Working with NULL value
11 Operation priorities
12 Functions of the data composition system expression language
12.1 Calculate
12.2 Level
12.3 NumberInOrder
12.4 Number in Order in Grouping
12.5 Format
12.6 Beginning of the Period
12.7 End of Period
12.8 AddToDate
12.9 Date Difference
12.10 Substring
12.11 Line Length
12.12 Year
12.13 Quarter
12.14 Month
12.15 Day of the Year
12.16 Day
12.17 Week
12.18 Day of the Week
12.19 Hour
12.20 Minute
12.21 Second
12.22 Express
12.23 YesNull
12.24 Functions of common modules

Data Composition System Expression Language

The data composition system expression language is designed to write expressions used in various parts of the system.

Expressions are used in the following subsystems:

• data layout diagram - to describe calculated fields, total fields, connection expressions, etc.;
• data layout settings - to describe custom field expressions;
• data layout layout - for describing expressions for connecting data sets, describing layout parameters, etc.

Literals

The expression may contain literals. The following types of literals are possible:

• Line;
• Number;
• Date of;
• Boolean.

Line

A string literal is written in “” characters, for example:

“String literal”

For example:

“Literal ““in quotes”““

Number

The number is written without spaces, in decimal format. The fractional part is separated using the "." symbol. For example:

date

A date literal is written using the key literal DATETIME. After this keyword, in brackets, separated by commas, the year, month, day, hours, minutes, seconds are listed. Time specification is not required.

For example:
DATETIME(1975, 1, 06) – January 6th, 1975
DATETIME(2006, 12, 2, 23, 56, 57) – December 2, 2006, 23 hours 56 minutes 57 seconds, 23 hours 56 minutes 57 seconds

Boolean

Boolean values ​​can be written using the literals True (True), False (False).

Meaning

To specify literals of other types (system enumerations, predefined data), the keyword Value is used, followed by the name of the literal in parentheses.
Value(AccountType. Active)

Operations on numbers

Unary –

This operation is intended to change the sign of a number to the opposite sign. For example:
-Sales.Quantity

Unary +

This operation does not perform any actions on the number. For example:
+Sales.Quantity

Binary -

This operation is intended to calculate the difference of two numbers. For example:
RemainsAndTurnover.InitialRemainder – RemainsAndTurnover.FinalRemainder
RemainsAndTurnover.InitialRemaining - 100
400 – 357

Binary +

This operation is designed to calculate the sum of two numbers. For example:
RemainsAndTurnover.InitialRemaining + RemainsAndTurnover.Turnover
RemainsAndTurnover.InitialRemaining + 100
400 + 357

Work

This operation is designed to calculate the product of two numbers. For example:
Nomenclature.Price * 1.2
2 * 3.14

Division

This operation is designed to obtain the result of dividing one operand by another. For example:
Nomenclature.Price / 1.2
2 / 3.14

Remainder of the division

This operation is designed to obtain the remainder when one operand is divided by another. For example:
Nomenclature.Price% 1.2
2 % 3.14

String Operations

Concatenation (Binary +)

This operation is designed to concatenate two strings. For example:
Nomenclature.Article + “: ”+ Nomenclature.Name

Like

This operation checks whether the string matches the passed pattern.

The value of the LIKE operator is TRUE if the value<Выражения>satisfies the pattern, and FALSE otherwise.

The following characters in<Строке_шаблона>have a meaning different from just another character in the line:

• % - percentage: a sequence containing zero or more arbitrary characters;
• _ - underscore: one arbitrary character;
• […] - one or more characters in square brackets: one character, any of those listed inside square brackets. The enumeration may contain ranges, for example a-z, meaning an arbitrary character included in the range, including the ends of the range;
• [^...] - in square brackets a negation icon followed by one or more characters: any character except those listed after the negation icon;

Any other symbol means itself and does not carry any additional load. If one of the listed characters needs to be written as itself, then it must be preceded by<Спецсимвол>, specified after the SPECIAL CHARACTER keyword (ESCAPE).

For example, template
“%ABV[abvg]\_abv%” SPECIAL CHARACTER “\”

means a substring consisting of a sequence of characters: the letter A; letters B; letters B; one digit; one of the letters a, b, c or d; underscore; letters a; letters b; letters v. Moreover, this sequence can be located starting from an arbitrary position in the line.

Comparison Operations

Equals

This operation is designed to compare two operands for equality. For example:
Sales.Counterparty = Sales.NomenclatureMainSupplier

Not equal

This operation is intended to compare two operands for inequality. For example:
Sales. Counterparty Sales. Nomenclature Main Supplier

Less

This operation is designed to check that the first operand is less than the second. For example:
SalesCurrent.Amount

More

This operation is designed to check that the first operand is greater than the second. For example:
SalesCurrent.Sum > SalesPast.Sum

Less or equal

This operation is designed to check that the first operand is less than or equal to the second. For example:
SalesCurrent.Amount

More or equal

This operation is designed to check that the first operand is greater than or equal to the second. For example:
SalesCurrent.Amount >= SalesPast.Amount

Operation B

This operation checks for the presence of a value in the passed list of values. The result of the operation will be True if the value is found, or False otherwise. For example:
Item B (&Product1, &Product2)

Operation of checking the presence of a value in a data set

The operation checks for the presence of a value in the specified data set. The validation dataset must contain one field. For example:
Sales. Counterparty To Counterparties

Operation of checking a value for NULL

This operation returns True if the value is NULL. For example:
Sales.Counterparty IS NULL

Operation of checking a value for NULL inequality

This operation returns True if the value is not NULL. For example:
Sales.Counterparty IS NOT NULL

Logical operations

Logical operations accept expressions of type Boolean as operands.

Operation NOT

The NOT operation returns True if its operand is False and False if its operand is True. For example:
NOT Document.Consignee = Document.Consignor

Operation I

The AND operation returns True if both operands are True, and False if one of the operands is False. For example:
Document.Consignee = Document.Consignor AND Document.Consignee = &Counterparty

OR operation

The OR operation returns True if one of its operands is True, and False if both operands are False. For example:
Document.Consignee = Document.Consignor OR Document.Consignee = &Counterparty

Aggregate functions

Aggregate functions perform some action on a set of data.

Sum

The Sum aggregate function calculates the sum of the values ​​of the expressions passed to it as an argument for all detail records. For example:
Amount(Sales.AmountTurnover)

Quantity

The Count function calculates the number of values ​​other than NULL. For example:
Quantity(Sales.Counterparty)

Number of different

This function calculates the number of distinct values. For example:
Quantity(Various Sales.Counterparty)

Maximum

The function gets maximum value. For example:
Maximum(Remaining.Quantity)

Minimum

The function gets the minimum value. For example:
Minimum(Remaining.Quantity)

Average

The function gets the average of non-NULL values. For example:
Average(Remaining.Quantity)

Other operations

Operation SELECT

The Select operation is intended to select one of several values ​​when certain conditions are met. For example:
Select When Amount > 1000 Then Amount Otherwise 0 End

Rules for comparing two values

If the types of the values ​​being compared are different from each other, then the relationships between the values ​​are determined based on the precedence of the types:
NULL (lowest);
Boolean;
Number;
Date of;
Line;
Reference types

The relationships between different reference types are determined based on the reference numbers of the tables corresponding to a particular type.

If the data types are the same, then the values ​​are compared according to the following rules:
for the Boolean type the value TRUE is greater than the value FALSE;
the Number type has the usual comparison rules for numbers;
for the Date type, earlier dates are less than later ones;
for the String type - comparison of strings in accordance with the established national characteristics of the database;
reference types are compared based on their values ​​(record number, etc.).

Working with NULL Value

Any operation in which one of the operands is NULL will produce a NULL result.

There are exceptions:
The AND operation will return NULL only if none of the operands is False;
The OR operation will only return NULL if none of its operands are True.

Operation priorities

The operations have the following priorities (the first line has the lowest priority):
OR;
AND;
NOT;
B, IS NULL, IS NOT NULL;
=, <>, <=, <, >=, >;
Binary +, Binary – ;
*, /, %;
Unary +, Unary -.

Data Composition System Expression Language Functions

Calculate

The Calculate function is designed to calculate an expression in the context of a certain grouping. The function has the following parameters:
Expression. Type String. Contains a calculated expression;
Grouping. Type String. Contains the name of the grouping in the context of which the expression is to be evaluated. If an empty string is used as the grouping name, the calculation will be performed in the context of the current grouping. If the GrandTotal string is used as the group name, the calculation will be performed in the context of the grand total. Otherwise, the calculation will be performed in the context of the parent group with the same name. For example:
Sum(Sales.SumTurnover) / Calculate("Sum(Sales.SumTurnover)", "Total")

IN in this example the result will be the ratio of the amount for the “Sales.SumTurnover” field of the grouping record to the amount of the same field in the entire layout.

Level

The function is designed to obtain the current recording level.

Example:
Level()

NumberInOrder

Get the next sequence number.

Example:
NumberByOrder()

NumberInOrderInGrouping

Returns the next ordinal number in the current grouping.

Example:
NumberByOrderInGroup()

Format

Get a formatted string of the passed value.

The format string is set in accordance with the 1C:Enterprise format string.

Options:
Meaning;
Format string.

Example:
Format(Invoices.Doc Amount, "NPV=2")

Beginning of period

Options:

• • Minute;
  • Day;
  • A week;
  • Month;
  • Quarter;
  • Decade;
  • Half year.

Example:
StartPeriod(DateTime(2002, 10, 12, 10, 15, 34), "Month")

Result:

01.10.2002 0:00:00

End of Period

The function is designed to extract a specific date from a given date.

Options:

• Date of. Type Date. Specified date;
• Period type. Type String. Contains one of the following values:
  • Minute;
  • Day;
  • A week;
  • Month;
  • Quarter;
  • Decade;
  • Half year.

Example:
EndPeriod(DateTime(2002, 10, 12, 10, 15, 34), "Week")

Result:

13.10.2002 23:59:59

AddToDate

The function is designed to add a certain value to a date.

Options:

• Magnification type. Type String. Contains one of the following values:
  • Minute;
  • Day;
  • A week;
  • Month;
  • Quarter;
  • Decade;
  • Half year.

Example:
AddToDate(DateTime(2002, 10, 12, 10, 15, 34), "Month", 1)

Result:

12.11.2002 10:15:34

Date Difference

The function is designed to get the difference between two dates.

Options:

• Expression. Type Date. Original date;
• Expression. Type Date. Subtracted date;
• Difference type. Type String. Contains one of the following values:
  • Second;
  • Minute;
  • Day;
  • Month;
  • Quarter;

Example:
DATEDIFFERENCE(DATETIME(2002, 10, 12, 10, 15, 34),
DATETIME(2002, 10, 14, 9, 18, 06), "DAY")

Result:

Substring

This function is designed to extract a substring from a string.

Options:

• Line. Type String. The string from which the substring is extracted;
• Position. Type Number. The position of the character from which the substring to be extracted from the string begins;
• Length. Type Number. Length of the allocated substring.

Example:
SUBSTRING(Accounts.Address, 1, 4)

Line Length

The function is designed to determine the length of a string.

Parameter:
Line. Type String. A string whose length is determined.

Example:
Line(Counterparties.Address)

This function is designed to extract the year from a Date type value.

Parameter:
Date of. Type Date. The date by which the year is determined.

YEAR(Expense.Date)

Quarter

This function is designed to extract the quarter number from a Date type value. The quarter number normally ranges from 1 to 4.

Parameter
Date of. Type Date. The date by which the quarter is determined

QUARTER(Expense.Date)

Month

This function is designed to extract the month number from a Date type value. The month number normally ranges from 1 to 12.
Date of. Type Date. The date by which the month is determined.
MONTH(Expense.Date)

Day of the Year

This function is designed to obtain the day of the year from a Date type value. The day of the year normally ranges from 1 to 365 (366).
Date of. Type Date. The date by which the day of the year is determined.
DAYYEAR(ExpenseAccount.Date)

Day

This function is designed to obtain the day of the month from a Date type value. The day of the month normally ranges from 1 to 31.
Date of. Type Date. The date by which the day of the month is determined.
DAY(Expense.Date)

A week

This function is designed to obtain the week number of the year from a Date type value. The weeks of the year are numbered starting from 1.
Date of. Type Date. The date by which week numbers are determined.
WEEK(Expense.Date)

Day of the Week

This function is designed to obtain the day of the week from a Date type value. The normal day of the week ranges from 1 (Monday) to 7 (Sunday).
Date of. Type Date. The date by which the day of the week is determined.
DAY OF THE WEEK (Expense Invoice Date)

This function is designed to obtain the hour of day from a Date type value. The hour of the day ranges from 0 to 23.
Date of. Type Date. The date by which the hour of the day is determined.
HOUR(Expense.Date)

Minute

This function is designed to obtain the minute of the hour from a Date type value. The minute of the hour ranges from 0 to 59.
Date of. Type Date. The date by which the minute of the hour is determined.
MINUTE(Expense.Date)

Second

This function is designed to obtain the second of a minute from a Date type value. The second of a minute ranges from 0 to 59.
Date of. Type Date. The date by which the seconds of the minute are determined.
SECOND(Expense.Date)

Express

This function is designed to extract a type from an expression that may contain a compound type. If the expression contains a type other than the required type, NULL will be returned.

Options:
Expression to convert;
Type indication. Type String. Contains a type string. For example, "Number", "String", etc. Besides primitive types given line may contain the table name. In this case, an attempt will be made to express a reference to the specified table.

Example:
Express(Data.Props1, "Number(10,3)")

IsNull

This function returns the value of the second parameter if the value of the first parameter is NULL.

Otherwise, the value of the first parameter will be returned.

Example:
YesNULL(Amount(Sales.AmountTurnover), 0)

Functions of common modules

A data composition engine expression may contain calls to functions of global common configuration modules. No additional syntax is required to call such functions.

Example:
AbbreviatedName(Documents.Link, Documents.Date, Documents.Number)

In this example, the "AbbreviatedName" function will be called from the general configuration module.

Note that the use of common module functions is only permitted if the appropriate data composition processor parameter is specified.

Additionally, functions of common modules cannot be used in custom field expressions.

Php?http://wiki.kint.ru/index.php/Data Composition System Expression Language(1Cv82)#.D0.9A.D0.BE.D0.BD.D0.B5.D1.86.D0.9F.D0.B5. D1.80.D0.B8.D0.BE.D0.B4.D0.B0

Ukrainian 1C forum: everything about 1C 8.3, 1C 8.2, 1C 8.1, 1C 8.0, 1C 7.7
http://site

Attention! This is an introductory version of the lesson, the materials of which may be incomplete.

Login to the site as a student

Log in as a student to access school materials

Internal programming language 1C 8.3 for beginner programmers: simple logical expressions in 1C

In the last lesson we learned how to give names and enter their values ​​from the user.

Gain strength and patience. Lessons No. 5 and No. 6 will be difficult, but very important for further understanding of programming in 1C. Therefore, if at least something remains incomprehensible or undisclosed, re-read it, think about it, ask questions.

Simple Boolean Expressions

Today, to the already studied data types (string, number and date), we will add one more - logical type. It can take only two values: True or Lie.

Boolean value ( True or Lie) is the result of some logical expression.

A Boolean expression is an expression composed using comparison operations. You can compare numbers, dates, strings and other data. You are reading a trial version of the lesson, full lessons are available. About a logical expression we can say, it is true ( True) or incorrect ( Lie).

Comparison operations are as follows:

I think it is intuitively clear what each of the operations means.

It reads, for example, like this:

• 1 = 1 (“one equals one”).
• 4 <>5 ("four does not equal five")
• 3 > 1 (“three is more than one”).

Please note that the listed three examples of logical expressions take the value True, since they are all correct.

Let's look at an example:

Task No. 14. For each logical expression, indicate its result - True or Lie.

1. "Venus" = "Jupiter"
2. "Venus"<>"Jupiter"
3. 123 = 321
4. 123 < 321
5. 123 <= 321
6. 123 <= 123
7. "20000101" <> "20140101"
8. "20000101" = "20000101"
9. "20140101" > "20120101"
10. 25 + 25 = 50
11. 2 * (10 + 10) > 50
12. "Yuri" + "Gagarin" = "Yuri Gagarin"

Try to answer all the questions yourself. You are reading a trial version of the lesson, full lessons are available. Then look at the answers below and try to figure it out if you made a mistake somewhere.

1. "Venus" = "Jupiter" ( Lie, the strings are not equal to each other, but the expression states that they are equal).
2. "Venus"<>"Jupiter" ( True, the rows are not equal to each other, as stated in the expression).
3. 123 = 321 (Lie, since the numbers are not equal to each other, but the expression states that they are equal).
4. 123 < 321 (True, since 123 is less than 321, as stated in the expression).
5. 123 <= 321 (True, since 123 is less than 321, and this statement is true if the left number is less than or equal to the right).
6. 123 <= 123 (True, since 123 is equal to 123, and this statement is true if the left number is less than or equal to the right).
7. "20000101" <> "20140101" (True, since the left date 01/01/2000 is not equal to 01/01/2014, as stated in the expression).
8. "20000101" = "20000101" (True, since the left date 01/01/2000 is equal to 01/01/2000, as stated in the expression).
9. "20140101" > "20120101" (True, since the first of January 2014 is greater than the first of January 2012, as stated in the expression).
10. 25 + 25 = 50 (True, since 25 plus 25 really equals fifty, as stated in the expression).
11. 2 * (10 + 10) > 50 (Lie, since the result of the left expression is 40, and 40 is less than 50, although the statement says the opposite).
12. "Yuri" + "Gagarin" = "Yuri Gagarin" ( True, since the sum of the strings "Yuri" and "Gagarin" forms one string "Yuri Gagarin", which is equal to the string on the right, as stated in the expression).

Let's make you countcomputer

But what do we ourselves consider “true” or “wrong”. After all, we have a computer at hand! Let's entrust this task to him, and at the same time test ourselves.

For example, let's check the expression 2 * (10 + 10) > 50.
To do this, we will write the following program:

If we run it, the computer will say “No”, which means the result is Lie.

How did he calculate this expression?

1. The computer saw the Report command.
2. I looked at what parameter we are passing to this command.
3. I saw that the logical expression 2 * (10 + 10) > 50 was specified as a parameter.
4. I began to calculate the result of a logical expression.
5. I saw that the expression consists of the left side 2 * (10 + 10), the right side 50 and the greater than sign between them.
6. I calculated the result on the left side and found out that it was equal to forty.
7. He looked at the expression again, only in a simplified form: 40 > 50, and since 40, of course, is less than 50, he printed “No”.

Task No. 15. Evaluate all examples of logical expressions from the previous task on the computer.

For example, the expression "Venus" = "Jupiter", the value of which, as we found out, is equal to Lie.

The computer displays the value "No" at startup, which means: the result is really equal to Lie and we calculated it ourselves absolutely correctly.

Reference solution. First we write the code ourselves, check it on the computer (like all example programs from the lessons), only then check

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; it is used to change data object model 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>

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

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

IN this request 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

By using of this operator you can get 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
SORT 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 right type data. 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 (Table of Costs. Subconto1 AS Directory. Items of Costs). Type of Activity for Tax Accounting of Costs

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:

CHOOSE 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 as follows: using the TYPE() and VALUETYPE() functions, or using logical operator LINK. 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.Profit.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 of topics, which 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 request 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
SORT 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
SORT BY
Sales of Goods and Services Goods. Link. Date
RESULTS
SUM(Quantity),
SUM(Sum)
BY
ARE COMMON,
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
SORT 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. Virtual table – quick way obtaining 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 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 are 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. “Add” the amount from the movement table to the amount from the totals table.

Such simple steps 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 required fields 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 request will be: data retrieval, creation of a temporary table, or destruction of 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 Automatic mode interlocks, design TO 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 simple and convenient way for debugging complex queries and quickly obtaining 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 regular (or sometimes 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 in 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 request constructor is called from context menu (right button mouse) when clicking on the input field:

Also in this menu there are such useful features, like clearing or adding line breaks (“|”) to the request, or getting 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.Sale 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 perform optimally. In this case, to optimize the performance of the query, you can try placing the virtual table in a temporary table, 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 tables DBMS, 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!