Working with strings in a query 1c 8.2. The peculiarity of the substring () function. Using logical OR in conditions

In the 1C: Enterprise query language, the function SUBSTRATE () v format SUBSTRING (<Исходная строка>, <Начало>, <Длина>) can be applied to data of string type and allows to highlight the fragment<Исходной строки>starting with the character number<Начало>(characters in the string are numbered from 1) and length<Длина>characters. The result of evaluating the SUBSTRING () function has a variable-length string type, and the length will be considered unbounded if<Исходная строка>has unlimited length and parameter<Длина>is not constant or exceeds 1024.

Evaluating the SUBSTRATE () function on a SQL server:
In the client-server mode of operation, the SUBSTRING () function is implemented using the SUBSTRING () function of the corresponding SQL statement transmitted to the database server SQL data Server, which calculates the type of the result of the SUBSTRING () function according to complex rules depending on the type and values of its parameters, as well as depending on the context in which it is used.

In most cases, these rules do not affect the execution of a 1C: Enterprise query, however, there are cases when the maximum length of the result string, calculated SQL Server... It is important to keep in mind that in some contexts where the SUBSTRING () function is used, the maximum length of its result may be the maximum length of a bounded string, which is 4000 characters in SQL Server. This can lead to an unexpected abnormal termination of the request.

For example, the request:
Code 1C v 8.x SELECT
CHOICE

ELSE NULL
END LIKE View,
CHOICE
WHEN Kind = & LegalAddressPhysPersons
THEN SUBSTRING (View, 0, 200)
ELSE NULL
END LIKE View1
FROM

SORT BY
Representation,
Performance1
terminates abnormally with the message DBMS error:
Microsoft OLE DB Provider for SQL Server: Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.
HRESULT = 80040E14, SQLSTATE = 42000, native = 8618
This is because Microsoft SQL Server computed the maximum string length that is the result of the expression:
Code 1C v 8.x CHOICE
WHEN Kind = & LegalAddressPhysPersons
THEN SUBSTRING (View, 0, 200)
ELSE NULL
END LIKE View,
equals 4000 characters. Therefore, the length of a record consisting of two such fields exceeds the 8000 bytes allowed for the sorting operation.

Due to the described feature of the SUBSTRING () function execution on SQL Server, it is not recommended to use the SUBSTRING () function to convert strings of unlimited length to strings of limited length. It is better to use a cast operation like EXPRESS () instead. In particular, the above example can be rewritten as:
Code 1C v 8.x SELECT
CHOICE
WHEN Kind = & LegalAddressPhysPersons
ELSE NULL
END LIKE View,
CHOICE
WHEN Kind = & LegalAddressPhysPersons
THEN EXPRESS (Representing AS String (200))
ELSE NULL
END LIKE View1
FROM
Information Register.Contact Information AS Contact Information
SORT BY
Representation,
Performance1

The "String" type is found in all programming languages. It is primitive, and in 1C there are many functions for working with it. In this article, we will take a closer look at different ways working with string types in 1C 8.3 and 8.2 by examples.

Line

In order to convert a variable of any type into a string, there is a function of the same name "String ()". The input parameter will be the variable itself, the string representation of which is to be obtained.

String (False) // returns "No"
String (12345) // returns "12 345"
String (CurrentDate ()) // 07/21/2017 11:55:36 ″

It is possible to convert not only primitive types to a string, but also others, for example, elements of reference books, documents.

SocrLP, SocrL, SocrP

The input parameters of these functions are a string type variable. Functions remove insignificant characters (spaces, carriage returns and others): from the left and right sides, only from the left side, and only from the right, respectively.

AbbrLP ("Spaces on both sides will be removed") // "Spaces on both sides will be removed"
Abbreviated ("Spaces on both sides will be removed") // "Spaces on the left will be removed"
Abbreviation ("Spaces on both sides will be removed") // "Spaces on the right will be removed"

Leo, Right, Wednesday

These functions allow you to trim part of a string. The "Lion ()" function will return the part of the string from its left side of the specified length. The "Right ()" function is the same, but the cropping is done on the right. The "Avg ()" function allows you to specify the number of the character from which the string will be selected and its length.

Lion ("String variable", 4) // returns "Stro"
Right ("String variable", 7) // returns "variable"
Wed ("String variable", 2, 5) // returns "troco"

Str Length

The function determines the number of characters that are contained in the string variable.

StrLength ("Word") // execution result will be number 5

Find

The function makes it possible to search for a part of a string in any string variable. The return value will be a number that shows the position of the beginning of the found string. If no match is found, zero is returned.

Please note that searches are case sensitive. If the original string contains more than one occurrence of the search substring, the function will return the beginning of the first occurrence.

Find ("one, two, one, two, three", "two") // the function will return the number 6

Empty line

Using this function allows you to determine if a string is empty. Insignificant characters such as space, carriage return, and others are ignored.

EmptyString ("Pupkin Vasily Ivanovich") // the function will return the value False
EmptyString ("") // function will return True

VReg, NReg, Treg

These functions are very useful for comparing and converting string variables. Breg () will return the original string in uppercase, HPreg () in lowercase, and TPreg () will format it so that the first character of each individual word will be capitalized and all subsequent ones will be lowercase.

Vreg ("GENERAL DIRECTOR") // return value - "GENERAL DIRECTOR"
НReg ("GENERAL DIRECTOR") // return value - "general director"
TREG ("GENERAL DIRECTOR") // return value - "General Director"

PageReplace

This function is analogous to the replacement in text editors... It allows you to substitute one character or set of characters for another in string variables.

StrReplace ("red, white, yellow", ",", ";") // will return "red; White; yellow"

StrNumberStrings

The function allows you to determine the number of lines separated by carriage returns in a text variable.

The loop in the example below will go through three circles because the RowNumber of Rows function will return a value of 3:

For ind = 1 by StrNumberStrings ("String1" + Symbols.PS + "String2" + Symbols.PS + "String3") Loop
<тело цикла>
End of Cycle;

StrGetString

This function works with multi-line text in the same way as the previous one. It allows you to get a specific string from a text variable.

StrGetString ("String1" + Symbols.PS + "String2" + Symbols.PS + "String3", 2) // will return "String2"

StrNumber of Occurrences

The function counts the number of occurrences of a character or substring in the search string.

Row of Attachments ("a; b; c; d;", ";") // the function will return the number 4

Symbol and Symbol Code

These functions allow you to get a character by its Unicode code, as well as to determine this code by the character itself.

SymbolCode ("A") // the function will return the number 1 040
SymbolCode (1040) // the function will return "A"

Frequent tasks when working with strings

Concatenating strings

To concatenate multiple strings (to perform concatenation), it is sufficient to use the addition operator.

"Line 1 ″ +" Line 2 ″ // the result of adding two lines will be "Line 1 Line 2"

Type conversion

In order to convert a type to a string, for example, a reference to a dictionary item, a number, etc., it is enough to use the "String ()" function. Functions like "Abbreviation ()" will also convert variables to a string, but immediately with the truncation of insignificant characters.

String (1000) // will return "1000"

Please note that when converting a number to a string, the program automatically added a space separating a thousand. In order to avoid this, you can use the following constructions:

StrReplace (String (1000), Characters.NPP, "") // will return "1000"

String (Format (1000, "CHG =")) // will return "1000"

Quotes in a string

Quite often, you will have to deal with the need to specify quotation marks in a string variable. It can be either a request text written in the configurator, or just a variable. To solve this problem, you just need to set two quotation marks.

Title = String ("Horns and Hooves LLC" - that's us! ") // returns" Horns and Hooves LLC - that's us! "

Multiple lines, line break

In order to create multi-line text, it is enough to add line break characters (Symbols.PS) to it.

MultilineText = "First line" + Symbols.PS + "Second line"

How to remove spaces

In order to remove spaces to the right or to the left, you can use the function "Abbreviation ()" (as well as "Abbreviation ()" and "Abbreviation ()"):

StringNoSpaces = AbbreviatedLP ("Many letters") // the function will return the value "Many letters"

If, after converting a number to a string, you need to remove non-breaking spaces, use the following construction:

StringNoSpaces = StrReplace (String (99999), Characters.NPP, "") // will return "99999"

Also, programmers often use the following construction, which allows you to remove or replace all spaces of a text variable with another character:

StringNoSpaces = StrReplace ("welcome", "", "") // returns "hello"

Comparing strings to each other

The terms can be compared with the usual equal sign. The comparison is case sensitive.

"Hello" = "hello" // will return False
"Hello" = "Hello" // will return True
"Hello" = "Goodbye" // will return False

Strings in 1C 8.3 in the built-in language 1c represent values of a primitive type Line... The values of this type contain an arbitrary length Unicode string. Variables of string type are a set of characters enclosed in quotation marks.

Example 1. Let's create a string variable with text.

StringVariable = "Hello world!";

Functions for working with strings in 1c 8.3

In this section, the main functions will be given that allow you to change the lines in 1c, or analyze the information contained in them.

Str Length

StrLength (<Строка>) ... Returns the number of characters contained in the string passed in the parameter.

Example 2. Let's count the number of characters in the string "Hello world!"

String = "Hello world!"; Number of Characters = StrLength (String); Report (Number of Characters);

The result of executing this code will be the display of the number of characters in the string: 11.

Abbreviation

AbbrL (<Строка>) ... Truncates insignificant characters to the left of the first significant character in the string.
Minor characters:

space;
non-breaking space;
tabulation;
carriage return;
line translation;
translation of the form (page).

Example 3. Remove all spaces from the left side of the line "world!" and attach the string "Hello" to it.

String = abbreviated ("peace!"); String = "Hello" + String; Report (String);

The result of the execution of this code will be the output of the line "Hello world!"

Abbreviation

Abbreviation (<Строка>) ... Truncates insignificant characters to the right of the first significant character in the string.

Example 4. Form "Hello" and "World!" the phrase "Hello world!"

String = abbreviated ("Hello") + "" + abbreviated ("world!"); Report (String);

SokrLP

SocrLP (<Строка>) ... Truncates insignificant characters to the right of the first significant character in a string, and also cuts insignificant characters to the left of the first significant character in a string. This function is used more often than the previous two, since it is more versatile.

Example 5. Remove insignificant characters on the left and right in the name of the counterparty.

Counterparty = Directories.Contractors.NaytiPoRequisite ("INN", "0777121211"); CounterpartyObject = Counterparty.GetObject (); CounterpartyObject.Name = SokrLP (CounterpartyObject.Name); CounterpartyObject.Write ();

a lion

A lion(<Строка>, <ЧислоСимволов>) ... Receives the first characters of a string, the number of characters is specified in the parameter Number of Characters.

Example 6. Let in the structure Employee contain the name, surname and patronymic of the employee. Get a string with the last name and initials.

InitialName = Leo (Employee.Name, 1); Patronymic Initial = Leo (Employee, Patronymic, 1); FullName = Employee.LastName + "" + InitialName + "." + Initial of Patronymic + ".";

Right

Right (<Строка>, <ЧислоСимволов>) ... Gets the last characters of a string, the number of characters is specified in the parameter Number of Characters. If the specified number of characters exceeds the length of the string, then the entire string is returned.

Example 7. Suppose that the end of a string variable contains a date in the format "yyyymmdd", get a string with a date and convert it to the type date.

String = " The current date: 20170910 "; StringDate = Right (String, 8); Date = Date (StringDate);

Wednesday

Wednesday (<Строка>, <НачальныйНомер>, <ЧислоСимволов>) ... Gets a substring from a string passed as a parameter Line, starting from the character whose number is specified in the parameter StartNumber and the length passed to the parameter Number of Characters. The numbering of characters in the line starts from 1. If in the parameter StartNumber a value is specified that is less than or equal to zero, then the parameter takes on the value 1. If the parameter Number of Symbols is not specified, then characters up to the end of the line are selected.

Example 8. Suppose that the string variable starting from the ninth position contains the region code, you should get it and write it in a separate line.

Line = "Region: 99 Moscow"; Region = Wed (Line, 9, 2);

Search

Find (<Строка>, <ПодстрокаПоиска>, <НаправлениеПоиска>, <НачальнаяПозиция>, <НомерВхождения>) ... Searches for the specified substring in a string, returns the position number of the first character of the found substring. Consider the parameters of this function:

Line... Source string;
Substring... The substring to be searched for;
Search direction... Specifies the direction to search for a substring in a string. Can take values:
- Search Direction. From Start;
- Search Direction.;
Initial Position... Specifies the position in the string from which the search begins;
Number of Entries... Specifies the number of occurrences of the desired substring in the original string.

Example 9. In the line "Hello world!" determine the position of the last occurrence of the symbol "and".

PositionNumber = StrNayti ("Hello world!", "And", DirectionSearch.End); Report (Position Number);

The result of executing this code will be the display of the number of the last occurrence of the symbol "and": 9.

VReg

BReg (<Строка>) ... Converts all characters in the specified string in 1s 8 to uppercase.

Example 10. Convert the string "hello world!" to uppercase.

StringBreg = BReg ("hello world!"); Report (StringVreg);

The result of executing this code will be the display of the line "HELLO WORLD!"

Nreg

HPreg (<Строка>) ... Converts all characters of the specified string in 1s 8 to lowercase.

Example 11. Convert the string "HELLO WORLD!" to lower case.

StringNreg = НReg ("HELLO WORLD!"); Report (StringVreg);

The result of the execution of this code will be the output of the line "hello world!"

Treg

Treg (<Строка>) ... Converts a string as follows: the first character of each word is converted to uppercase, the remaining characters of the word are converted to lowercase.

Example 12. Capitalize the first letters of the words in the string "hello world!"

StringTreg = Treg ("hello world!"); Report (StringTreg);

The result of the execution of this code will be the output on the screen of the line "Hello World!"

Symbol

Symbol(<КодСимвола>) ... Gets a character by its Unicod code.

Example 13. Let's add to the left and right in the line "Hello World!" symbol ★

StringWithStars = Symbol ("9733") + "Hello World!" + Symbol ("9733"); Report (StringWithStars);

The result of executing this code will be the display of the line "★ Hello World! ★"

Symbol Code

SymbolCode (<Строка>, <НомерСимвола>) ... Gets the Unicode character code from the string specified in the first parameter, located at the position specified in the second parameter.

Example 14. Find out the code of the last character in the "Hello World!"

String = "Hello World!"; CharacterCode = CharacterCode (String, StrLength (String)); Report (SymbolCode);

The result of the execution of this code will be the display of the symbol code "!" - 33.

Empty line

Empty line(<Строка>) ... Checks if a string contains only insignificant characters, that is, if it is empty.

Example 15. Check if a string consisting of three spaces is empty.

Empty = EmptyString (""); Report (Empty);

The result of the execution of this code will be the display of the word "Yes" (string expression of the logical value True).

PageReplace

PReplace (<Строка>, <ПодстрокаПоиска>, <ПодстрокаЗамены>) ... Finds all occurrences of the search substring in the original string and replaces it with the replacement substring.

Example 16. In the line "Hello World!" replace the word "Peace" with the word "Friends".

String = StrReplace ("Hello World!", "World", "Friends"); Report (String);

The result of the execution of this code will be the display of the line "Hello Friends!"

StrNumberStrings

StrNumber of Lines (<Строка>) ... Counts the number of lines in a multi-line string. To go to new line in 1s 8 the symbol is used PS(line feed character).

Example 17. Determine the number of lines in the text:
"First line
Second line
Third line "

Number = StrNumberStrings ("First line" + Symbols.PS + "Second line" + Symbols.PS + "Third line"); Report (Number);

The result of executing this code will be the display of the number of lines in the text: 3

StrGetString

StrGetString (<Строка>, <НомерСтроки>) ... Gets a string in a multi-line string by its number. Line numbering starts at 1.

Example 18. Get the last line in the text:
"First line
Second line
Third line "

Text = "First line" + Symbols.PS + "Second line" + Symbols.PS + "Third line"; LastRow = StrGetString (Text, StrNumberLines (Text)); Report (LastLine);

The result of the execution of this code will be the display of the line "Third line".

StrNumber of Occurrences

StrNumber of Occurrences (<Строка>, <ПодстрокаПоиска>) ... Returns the number of occurrences of the specified substring in a string. The function is case sensitive.

Example 19. Determine how many times the letter "c" appears in the line "Lines in 1s 8.3 and 8.2", regardless of its case.

Line = "Lines in 1s 8.3 and 8.2"; Number of Occurrences = StrNumber of Occurrences (Vreg (String), "S"); Report (Number of Occurrences);

The result of executing this code will be the display of the number of occurrences: 2.

PageStarts With

PageStarts With (<Строка>, <СтрокаПоиска>) ... Checks if the string passed in the first parameter starts with the string in the second parameter.

Example 20. Determine whether the INN of the selected counterparty begins with the number 1. Let in the variable Counterparty Contractors.

TIN = Counterparty.INN; Starts withUnits = StrStarts with (INN, "1"); If BeginsUnits Then // Your Code EndIf;

PageEnds With

PageEnds With (<Строка>, <СтрокаПоиска>) ... Checks if the string passed in the first parameter ends with the string in the second parameter.

Example 21. Determine whether the TIN of the selected counterparty ends with the digit 2. Let in the variable Counterparty a link to a directory item is stored Contractors.

TIN = Counterparty.INN; Ends With Two = Str Ends With (INN, "2"); If Ends WithDouble Then // Your code EndIf;

PageSplit

StrSplit (<Строка>, <Разделитель>, <ВключатьПустые>) ... Splits a string into parts using the specified delimiter characters and writes the resulting strings to an array. The first parameter stores the original string, the second contains the string containing the separator, the third indicates whether to write to the array blank lines(default True).

Example 22. Suppose we have a string containing numbers separated by ";", get an array of numbers from the string.

String = "1; 2; 3"; Array = StrSplit (String, ";"); For Rd = 0 By Array.Quantity () - 1 Cycle Attempt Array [Rd] = Number (AbbrLP (Array [Rd])); Exception Array [Сч] = 0; End of Attempts End of Loop;

As a result of execution, an array with numbers from 1 to 3 will be obtained.

StrConnect

StrConnect (<Строки>, <Разделитель>) ... Converts an array of strings from the first parameter to a string containing all the elements of the array, separated by the delimiter specified in the second parameter.

Example 23. Using the array of numbers from the previous example, get the original string.

For Rd = 0 By Array.Quantity () - 1 Cycle Array [Rd] = String (Array [Rd]); End of Cycle; String = StrConnect (Array, ";");

A string is one of the primitive data types in 1C: Enterprise 8. Variables with the type line contain text.

Values of type variables line are enclosed in double quotes. Several variables of this type can be added.

Per1 = "Word 1";
Per2 = "Word 2";
Per3 = Per1 + "" + Per2;

Eventually Per3 will mean “ Word 1 Word 2 ″.

In addition, 1C: Enterprise 8 systems provide functions for working with strings. Let's consider the main ones:

Enter String (<Строка>, <Подсказка>, <Длина>, <Многострочность>) — the function is intended to display a dialog box in which the user can specify a value variable type Line... Parameter <Строка> is required and contains the name of the variable to which the entered string will be written. Parameter <Подсказка> optional is the title of the dialog box. Parameter <Длина> optional, shows maximum length the input string. The default is zero, which means unlimited length. Parameter <Многострочность> optional. Determines the input mode for multi-line text: True - input of multi-line text with line separators; False - input of a simple string.

The string can be entered and given the Unicode character code:

Symbol(<КодСимвола>) — the code is entered as a number.

Letter = Symbol (1103); // I AM

There is also an inverse function that allows you to find out the code of a character.

SymbolCode (<Строка>, <НомерСимвола>) — returns the specified Unicode character number as a number.

Case conversion functions:

BReg (<Строка>) - converts all characters in the string to uppercase.

HPreg (<Строка>) - converts all characters in a string to lowercase.

Treg (<Строка>) - converts all characters in a string to uppercase. That is, the first letters in all words are converted to uppercase, and the remaining letters are converted to lowercase.

Functions for searching and replacing characters in a string:

Find(<Строка>, <ПодстрокаПоиска>) - finds the character number of the occurrence of the search substring. For instance:

Find ("String", "Oka"); // 4

Find (<Строка>, <ПодстрокаПоиска>, <НаправлениеПоиска>, <НачальнаяПозиция>, <НомерВхождения>) - finds the character number of the occurrence of the search substring, the occurrence number is indicated in the corresponding parameter. In this case, the search begins with the character, the number of which is specified in the parameter Initial Position. Search is possible from the beginning or from the end of the string. For instance:

Number4 Occurrences = StrFind ( "Defense capability", "o", Search Direction. From the beginning, 1, 4); // 7

PReplace (<Строка>, <ПодстрокаПоиска>, <ПодстрокаЗамены>) - finds all occurrences of the search substring in the original string and replaces it with the replacement substring.

StrReplace ("String", "Oka", ""); // Page

Empty line(<Строка>) - checks a string for significant characters. If there are no significant characters, or no characters at all, then the value is returned True... Otherwise - Lie.

StrNumber of Occurrences (<Строка>, <ПодстрокаПоиска>) - calculates the number of occurrences of the search substring in the original string.

StrNumber of Occurrences ( "Study, study and study again", "to study" , "" ) ; // 3

StrPattern (<Строка>, <ЗначениеПодстановки1>…<ЗначениеПодстановкиN> — substitutes parameters into the string by number. The string must contain substitution markers of the form: "% 1 ..% N". Markers are numbered starting from 1. If the parameter value Undefined, an empty string is substituted.

StrPattern ( "Parameter 1 =% 1, Parameter 2 =% 2", "1" , "2" ) ; // Parameter 1 = 1, Parameter 2 = 2

String conversion functions:

A lion(<Строка>, <ЧислоСимволов>) - returns the first characters of the string first.

Right (<Строка>, <ЧислоСимволов>) - returns the last characters of the string.

Wednesday (<Строка>, <НачальныйНомер>, <ЧислоСимволов>) - returns a string of length<ЧислоСимволов>starting with the character<НачальныйНомер>.

AbbrL (<Строка>) —truncates insignificant characters to the left of the first significant character in the string.

Abbreviation (<Строка>) - cuts off insignificant characters to the right of the last significant character in the string.

SocrLP (<Строка>) - cuts off insignificant characters to the left of the first significant character in the line and to the right of the last significant character in the line.

StrGetString (<Строка>, <НомерСтроки>) - gets a multi-line string by number.

Other functions:

StrLength (<Строка>) - returns the number of characters in a string.

StrNumber of Lines (<Строка>) - returns the number of lines in a multi-line string. A line is considered new if it is separated from the previous line by a newline character.

Compare (<Строка1>, <Строка2> ) - compares two strings, case insensitive. The function works in the same way as an object Comparison of Values... Returns:

1 - if the first line is greater than the second
-1 - if the second line is greater than the first
0 - if strings are equal

StrCompare ("First line", "Second line"); // one

The query language in 1C 8 is a simplified analogue of the well-known "structured programming language" (as it is 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:

SELECT
Banks. Name,
Banks.CorrAccount
FROM
Directory. Banks AS Banks

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

The query language is the simplest and effective method obtaining information. As you can see from the example above, in the query language you need to appeal with the names of metadata (this is a list of system objects that make up the configuration, that is, directories, documents, registers, etc.).

Description of Query Language Constructs

Query structure

To get the data, it is enough to use the "SELECT" (select) and "FROM" (from) constructions. The simplest request as follows:

SELECT * FROM Directories.Nomenclature

Where "*" means selection of all fields of the table, and References.Nomenclature is the name of the table in the database.

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

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

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

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

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

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

V this request we select the data of the “FieldName1” and “FieldName1” fields from the “TableName1” and “TableName” tables, assign synonyms to the fields using the “AS” operator, join them according to a certain “TableCondition” condition.

From the received data, we select only data that meets the condition from "WHERE" "DataFeedback Condition". Next, we group the query by the "FieldName1" field, while summing "FieldName2". We create totals for the "FieldNameField1" field and the final field "FieldName2".

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

General constructions

Let's consider the general constructions of the query language 1C 8.2.

FIRSTn

Via this operator you can get n the number of first records. The order of the entries is determined by the order in the request.

SELECT FIRST 100
Banks. Name,
Banks. Code AS BIK
FROM
Directory. Banks AS Banks
SORT BY
Banks.

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

ALLOWED

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

If a user tries to read records that are inaccessible to him with a request, he will receive an error message. To avoid this, you should use the "ALLOWED" construction, that is, the request will read only the records allowed for it.

SELECT ALLOWED
Additional Information Store.Link
FROM
Reference.Additional Information Storage

VARIOUS

Using "DIFFERENT" will allow you to exclude duplicate strings from entering the 1C query result. Duplication means that all fields of the request match.

SELECT FIRST 100
Banks. Name,
Banks. Code AS BIK
FROM
Directory. Banks AS Banks

EmptyTable

This construct is rarely used to combine queries. When joining, it may be necessary to specify an empty nested table in one of the tables. The "EmptyTable" operator is just suitable for this.

Example from help 1C 8:

SELECT Reference.Number, BLANK TABLE. (Nom, Tov, Qty) AS Composition
FROM Document.Expense
COMBINE ALL
SELECT Reference.Number, Composition. (LineNumber, Item, Quantity)
FROM Document.Invoice Document.Invoice.Content. *

IS NULL

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

SELECT
NomenclatureRef.Ref.
There is NULL (Item Balance.Quantity Balance, 0) AS Quantity Balance
FROM

You can use it in another way. For example, if for each row it is not known in which table the value exists:

IS NULL (InvoiceInvoiceReceived.Date, InvoiceInvoiced.Date)

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

These constructs 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, and REFERENCE REPRESENTATION converts only reference values. REFERENCE REPRESENTATION is recommended to be used in queries of the data composition system for optimization, unless, of course, the reference data field is planned to be used in the filters.

SELECT
View (Link), // line, for example "Advance report No. 123 dated 10/10/2015
Representation (Deletion Mark) AS Deletion Mark Text, // string, "Yes" or "No"
ReferenceRepresentation (Deletion Mark) AS Deletion Mark Boolean // 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 reference type is used to restrict the requested data types in fields of a complex type, often used to optimize system performance. Example:

EXPRESS (Cost.Subconto1 Table AS A Reference.Cost Articles) .ActivityTypeFor Tax Cost Accounting

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 the comparison operation. You cannot compare fields
unlimited length and fields of incompatible types", It is necessary to express such fields as follows:

EXPRESS (Comment AS Line (150))

DIFFERENCE

Get 267 1C video tutorials for free:

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

CHOOSE FROM
Spr
LEFT JOIN Accumulation Register.ProductsIn Warehouses.Left AS RemainingGoods
Software NomenclatureRef.Ref = ImplementedCommentsComponentsRemains.Nomenclature
WHERE IS THE BALANCE OF THE GOODS.Quantity The balance IS NULL

You can define the data type in a query as follows: using the TYPE () and TYPE () functions, or using logical operator LINK. The two functions are similar.

Predefined values

In addition to using the passed parameters in requests in the 1C query language, you can use predefined values or. For example, enumerations, predefined lookups, charts of accounts, and so on. For this, the "Value ()" construct is used.

Usage example:

WHERE Nomenclature.NomenclatureType = Value (Directory.NomenclatureType.Product)

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

WHERE Account Balances.Account = Value (Plan of Accounts. Self-supporting.ProfitsLoss)

Connections

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

LEFT and RIGHT JOIN

Joins are used to link two tables according to a specific condition. Feature at LEFT JOINT in that we take the first specified table in full and bind the second table by condition. The fields of the second table that could not be bound by condition are filled with the value NULL.

For instance:

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 NULL.

RIGHT JOINT in 1C language absolutely the same LEFT connection, with the exception of one difference - in RIGHT CONNECTION The “main” table is the second, not the first.

FULL CONNECTION

FULL CONNECTION differs from the left and right in that it displays all records from two tables, joins only those that it can join by condition.

For instance:

FROM

FULL CONNECTION
Directory. Banks AS Banks

ON

The query language will return both tables in full only if the condition of joining records is fulfilled. Unlike left / right join, it is possible for NULLs to appear in two fields.

INTERNAL CONNECTION

INTERNAL CONNECTION differs from the complete one in that it displays only those records that could be connected by the specified condition.

For instance:

FROM
Directory. Contractors AS Clients

INTERNAL CONNECTION
Directory. Banks AS Banks

ON
Clients.Name = Banks.Name

This request will return only lines in which the bank and the counterparty have the same name.

Associations

The COMBINE and COMBINE ALL construct combines two results into one. Those. the result of performing two will be "merged" into one, common.

That is, the system works in the same way as usual, only for a temporary table.

How to use INDEX SOFTWARE

However, there is one point to keep in mind. 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 not be 1-2 records in the temporary table. Otherwise, the effect may be the opposite - the performance from indexed fields does not compensate for the index building time.

SELECT
Currency RatesSliceLast.Currency AS Currency,
Currency RatesSliceLast.Course
POST Currency Rates
FROM
Information Register.Currency Rates.SliceLast (& Period,) AS Currency RatesSliceLast
INDEX BY
Currency
;
SELECT
Prices
PricesNomenclatures .Price,
PricesNomenclatures. Currency,
Currency Rates.
FROM
Information Register.NomenclaturePrices.SliceLast (& Period,
Nomenclature B (& Nomenclature) AND PriceType = & PriceType) AS Prices
LEFT UNION Currency Rates AS Currency Rates
BY RatesNomenclature.Currency = RatesCurrency.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 are available:

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

Example # 1:

SELECT
Sale of goods, services, goods, nomenclature,
AMOUNT (Sale of Goods, Services, Goods.Quantity) AS Quantity,
AMOUNT (Sale of GoodsServicesGoods.Amount) AS Amount
FROM

LOAD BY
Realization of GoodsServicesGoods.Nomenclature

The request receives all lines with goods and summarizes them by quantity and amounts in the context of the item.

Example No. 2

SELECT
Banks. Code,
QUANTITY (DIFFERENT Banks.Link) AS QuantityDoubles
FROM
Directory. Banks AS Banks
LOAD 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.

Outcomes

Totals are a way to get data from a hierarchical system. For summary fields, aggregate functions can be used, as for groupings.

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

SELECT

FROM
Document.Realization ofGoodsServices.Goods AS Sale ofGoodsServicesGoods
SORT BY

RESULTS
SUM (Quantity),
SUM (Amount)
ON
Nomenclature

The result of the query will be the following hierarchical:

General summary

If you want to get totals for all "totals", use the "GENERAL" operator.

SELECT
Realization of GoodsServicesGoods. Nomenclature AS Nomenclature,
Realization of GoodsServicesGoods.Link AS Document,
Realization of goods, services, goods. Quantity AS Quantity,
Sale of GoodsServicesGoods.Amount AS Amount
FROM
Document.Realization ofGoodsServices.Goods AS Sale ofGoodsServicesGoods
SORT BY
Implementation of GoodsServicesGoods.Link.Date
RESULTS
SUM (Quantity),
SUM (Amount)
ON
ARE COMMON,
Nomenclature

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

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

Ordering

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

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

SELECT

FROM
Reference.Nomenclature AS Nomenclature
SORT BY
Name

The query will display a list of the names of the stock list catalog, sorted alphabetically.

Auto-ordering

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

If you need to display table records in a consistent order, you must use the "Autoorder" construct.

SELECT
Nomenclature Name AS Name
FROM
Reference.Nomenclature AS Nomenclature
AUTO ORDERING

Virtual tables

Virtual tables in 1C are a unique feature of the 1C query language, which is not found in other similar syntaxes. Virtual table - quick way obtaining profile information from registers.

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

a cut of the first;
a cut of the latter.

leftovers;
revolutions;
balances and turnovers.

movements from the subconto;
revolutions;
RPM Dt Kt;
leftovers;
balances and turnovers
subconto.

base;
graph data;
the actual period of validity.

For a solution developer, data is taken from one (virtual) table, but in fact, the 1C platform takes from a variety of tables, transforming them into the desired form.

SELECT
ProductsIn WarehousesBalances andTurnovers.Nomenclature,
ProductsIn WarehousesBalancesAndTurnover.QuantityBeginning Balance,
GoodsIn WarehousesBalances andTurnovers.QuantityTurnover,
GoodsIn WarehousesBalancesAndTurnover.NumberIncome,
GoodsIn WarehousesBalances andTurnovers.QuantityConsumption,
ProductsIn WarehousesBalancesAndTurnovers.QuantityFinal Balance
FROM
Accumulation Register.GoodsIn Warehouses.Balances AND Turnovers AS GoodsIn WarehousesBalances And Turnovers

Such a request allows you to quickly receive a large number of data.

Virtual table parameters

Very important aspect working with virtual tables - using parameters. Virtual table parameters - specialized parameters for selection and tuning.

For such tables, it is considered incorrect to use selection in the "WHERE" construct. In addition to the fact that the request becomes not optimal, it is possible to receive incorrect data.

An example of using such parameters:

Accumulation Register.ProductsIn Warehouses.RemainsAndTurnovers (& BeginningPeriod, & EndPeriod, Month, Movements And PeriodBorders, Nomenclature = & DesiredNomenclature)

Algorithm of virtual tables

For example, the most commonly used virtual table of the type "Residuals" stores data from two physical tables - balances and movements.

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

We get the calculated value closest in terms of date and dimensions in the totals table.
“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 constructor- a tool built into the 1C enterprise system, which greatly facilitates the development of queries to the database.

The query constructor has a fairly simple, intuitive clear interface... However, let's take a closer look at using the query constructor.

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

Description of the 1C query constructor

Let's take a closer look at each design tab. The exception is the Builder tab, which is a topic for another discussion.

Tab Tables and Fields

This tab specifies the data source and fields that you want to display in the report. As a matter of fact, the constructions CHOOSE .. FROM are described here.

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

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

Links tab

The tab is used to describe the connections of several tables, 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 goes in the request text after the WHERE construction, that is, for all the conditions imposed on the received data.

Advanced tab

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

Grouping Fetching records:

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

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

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

Unions / Aliases Tab

On this tab of the query designer, you can set the ability to combine different tables and aliases (HOW construction). Tables are indicated on the left. If you set the flags in front of the table, the UNION construction will be used, otherwise - UNION ALL (the two methods differ). The right side indicates the correspondence of fields in different tables, if no match is specified, the query will return NULL.

Order tab

Here you specify the sorting order of values (ORDER BY) - descending (DESCENT) or ascending (RETURN).

There is also an interesting flag - Auto-ordering(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 the data by internal data.

Query Package Tab

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

Query Button in Query Builder

In the lower left corner of the query constructor there is a Query button, with which you can view the query 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 an easy and convenient way to debug complex queries and get information quickly. In this article I will try to describe how to use the Query Console and give a link to download the Query Console.

Let's take a closer look at this tool.

Download 1C Query Console

First of all, to get started with the Query Console, you need to download it from somewhere. Treatments are usually divided into two types - on managed forms and ordinary (or, sometimes, they are called on 8.1 and 8.2 / 8.3).

I tried to combine these two types in one processing - in the required mode of operation, the required form opens (in controlled mode the console only works in bold 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 header of the query console, you can see the execution time of the last query with millisecond precision, this allows you to compare different constructs in terms of performance.

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

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

Executeinquiry- simple execution and getting the result
Run package- allows you to view all intermediate requests in the request batch
Viewing temporary tables- allows you to see the results that are returned by temporary queries in the 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 Uniform parameters for all requests- when installed, its processing does not clear the parameters when moving from request to request in general list requests.

Set parameter as a list of values it is very simple, when you select a parameter value, press the button to clear the value (cross), the system will offer to select a data type, where you need to select “List of values”:

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

Here you can specify options for autosave queries and options for executing a query.

The query text is entered in the console query field. This can be done by a simple query test suite or by calling a special tool - the query designer.

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

Also in this menu there are such useful functions as clearing or adding line break characters (“|”) to the request, or getting the request code in such a convenient form:

Request = New Request;
Request.Text = "
| CHOOSE
| Currencies.Link
| FROM
| Directory. Currencies AS Currencies ”;
QueryResult = Query.Run ();

In the lower field of the query console, the query result field is displayed, for which 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.

Optimizing queries

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 certification... Below we will talk about typical reasons for not optimal performance queries and methods of optimization.

Selections in the virtual table using the WHERE construction

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

RIGHT:

SELECT

FROM
Accumulation register.Mutual settlementsWithDeponentsOrganizations.Balances (
,
Organization = & Organization
And Individual = & Individual) HOW Mutual settlementsWith DeponentsOrganizations

NOT PROPERLY:

SELECT
Mutual settlementsWithDeponentsOrganizationsBalances.AmountBalance
FROM
Accumulation register.Mutual settlementsWith DeponentsOrganizations.Balances (,) AS Mutual settlementsWithDeponentsOrganizationsBalances
WHERE
SettlementsWith DeponentsOrganizationsBalances.Organization = & Organization
And Mutual settlementsWith DeponentsOrganizationsBalances.Personal = & Individuals

Getting the value of a complex type field through a dot

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

For example, it is highly undesirable for optimization to refer to the register record field - the registrar. The registrar has a composite data type, including all possible types of documents that can write data to the register.

NOT PROPERLY:

SELECT
Record Set.Recorder.Date,
Set of Records.Number
FROM
Accumulation Register.ProductsOrganizations AS SetRecords

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

RIGHT:

SELECT
CHOICE
WHEN GoodsOrg.Registrar LINK Document.Realization of GoodsServices
THEN EXPRESS (GoodsOrg.Registrar AS Document.Realization of GoodsServices) .Date
WHEN GoodsOrg.Registrar LINK Document.Arrival of GoodsServices
THEN EXPRESS (GoodsOrg.Registrar AS Document.Arrival of GoodsServices) .Date
END AS Date,
ProductsOrg.Quantity
FROM
Accumulation Register.ProductsOrg

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

RIGHT:

SELECT
ProductsOrganizations.Date,
ProductsOrganizations.Number
FROM
Accumulation Register.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 BT objects, having previously indexed them by the connection fields.

NOT PROPERLY:

SELECT …

LEFT JOINT (
SELECT FROM INFORMATION REGISTER.Limits
WHERE …
LOAD BY ...
) ON …

RIGHT:

SELECT …
POST Limits
FROM Information Register.Limits
WHERE …
LOAD BY ...
INDEX BY…;

SELECT …
FROM Document.Realization of GoodsServices
LEFT JOIN Limits
ON …;

Joining records with virtual tables

There are situations when the system does not perform optimally when joining a virtual table with others. In this case, to optimize the query performance, you can try to put the virtual table in the temporary table, remembering 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 of the DBMS, as a result, a subquery is made to select them, and the problem is similar to the previous point.

Using filters 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 perform the query optimally if filtering by non-indexed fields is imposed in the query. If a temporary table is taken, it is also necessary to index the join fields.

There must be a suitable index for each condition. An index that meets the following requirements is suitable:

The index contains all the fields listed in the condition.
These fields are located at the very beginning of the index.
These selections are in a row, that is, values that are not involved in the query condition are not "wedged" between them.

If the DBMS does not find the correct indexes, then 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 basics of query optimization aspects that every 1C expert should know.

A very useful free video course on query development and optimization, highly recommend for beginners and not only!