Feature of the substring() function. Features of the execution of the substring() function String functions in 1C queries

Let's look at the rest now.

Functions for working with strings in 1C queries

There are few functions and operators for working with string data in 1C queries.

Firstly, strings in queries can be added. To do this, use the “+” operator:

Request. Text = "SELECT
" "Line: " " + Source.Name
;

Secondly, you can select part of the line. To do this, use the function SUBSTRUCTION. The function is similar to the built-in 1C language. It has three parameters:

Source string.
The number of the character with which the selected line should begin.
Characters.

Request. Text= "CHOOSE
SUBSTRING(""Line: " ", 4, 3) AS A Result"; // Result: oka

Function ISNULL

NULL is a special data type on the 1C:Enterprise platform. It is also the only possible value of this type. NULL can appear in queries in several cases: when connecting query sources, if a corresponding value was not found in one of the tables; when accessing the details of a non-existent object; if NULL was specified in the list of query fields (for example, when combining selection results from several tables), etc.

Because NULL is neither null, nor the empty string, nor even a value Undefined, it is often useful to replace it with some more useful data type. This is what the function is for. ISNULL.

It has two parameters:

The value being checked.
The value to replace the first parameter with if it turns out to be NULL.

Request. Text= "CHOOSE
ISNULL(Source.Remainder, 0) AS Remainder"; // If the result of the request is the field remainder = NULL,
// then it will be replaced by 0, and you can perform mathematical operations with it

Functions PERFORMANCE And INTRODUCTIONLINKS

These functions are designed to obtain string representations of various values. That is, they convert references, numbers, booleans, etc. into plain text. The difference between them is that the function PERFORMANCE converts any data types to text (string), and the function INTRODUCTIONLINKS- only links, and returns the remaining values as is, not converted.

Request. Text= "CHOOSE
REPRESENTATION(TRUE) AS Boolean,
REPRESENTATION (4) AS A Number,
REPRESENTATION (Source.Link) AS Link,
REPRESENTATION(DATETIME(2016,10,07)) AS Date";
// Boolean = "Yes", Number = "4", Link = "Document Cash receipt order No.... from..."
// Date="07.10.2016 0:00:00"

Request. Text= "CHOOSE
REPRESENTATIONREFERENCE(TRUE) AS Boolean,
REPRESENTATIONREFERENCE(4) AS NUMBER
PRESENTINGLINK(Source.Link) AS Link,
REPRESENTATIONREFERENCE(DATETIME(2016,10,07)) AS Date";
// Boolean = TRUE, Number = 4, Link = "Document Cash receipt order No.... from..."
// Date=07.10.2016 0:00:00

Functions TYPE And TYPE VALUES

Function TYPE returns the 1C:Enterprise platform data type.

Request. Text= "CHOOSE
TYPE (Number)
TYPE (String),
TYPE (Document. Expenditure Cash Order)";

Function TYPE VALUES returns the type of the value passed to it.

Request. Text= "CHOOSE
VALUES TYPE (5) AS Number,
TYPE (""Line" ") AS String,
TYPE (Source.Link) AS Reference
From the Directory.Source AS Source";
//Number=Number, String=String, Directory = DirectoryLink.Source

These functions are convenient to use, for example, when you need to find out whether a field received in a request is a value of some type. For example, let’s get the contact information of counterparties from the ContactInformation information register (contacts of not only counterparties, but also organizations, individuals, etc. are stored there):

Request. Text= "CHOOSE

FROM

WHERE
VALUES TYPE(ContactInformation.Object) = TYPE(Directory.Counterparties)";

Function MEANING

Function Meaning allows you to use 1C configuration objects directly in a request, without using .

Let's add one more condition to the previous example. You only need to get the phone numbers of your counterparties.

Request. Text= "CHOOSE
ContactInformation.Introduction
FROM
Register of Information. Contact Information HOW Contact Information
WHERE
VALUES TYPE(ContactInformation.Object) = TYPE(Directory.Counterparties)
AND ContactInfo.Type = VALUE(Enum.ContactInfoTypes.Phone)";

It should be noted that this function can only be used with predefined values, i.e. with values that can be accessed directly from the configurator. That is, the function MEANING cannot be used with directory elements created by users, but can work with enumerations, with predefined directory elements, with values EmptyLink.

Operator LINK

Operator LINK is designed to check the values returned by a request to see if they belong to a specific reference type. The same task can be accomplished using functions TYPE And TYPE VALUES(which have a wider scope and were discussed above).

For example, the task of selecting contact information for counterparties could be solved this way:

Request. Text= "CHOOSE
ContactInformation.Introduction
FROM
Register of Information. Contact Information HOW Contact Information
WHERE
ContactInformation.Object LINK Directory.Counterparties";

Operator EXPRESS

Operator EXPRESS used in 1C queries in two cases:

when you need to change the characteristics of a primitive type;
when you need to turn a field with a compound data type into a field with a single type.

Primitive data types include: number, string, date, boolean. Some of these data types have additional characteristics. Type Number has length and precision, type Line - length or unlimited.

Operator EXPRESS allows you to change not the data type, but additional characteristics. For example, he can turn a string with unlimited length into a string with limited length. This can be useful if you need to group query results by such a field. You can't group by fields with an unlimited length, so we convert it to a string with a length of 200 characters.

Request. Text= "CHOOSE
QUANTITY (DIFFERENT Arrival of Goods and Services. Link) AS Link
FROM
Document. Receipt of Goods and Services HOW Receipt of Goods and Services
GROUP BY
EXPRESS(Receipt of Goods and Services. Comment AS ROW (200))";

In some cases, queries to fields with a composite data type may not be processed optimally by the 1C platform. This results in longer query execution times, so it may be useful to convert the compound type to a single type in advance.

Request. Text= "CHOOSE
EXPRESS(Movement of GoodsTurnover.Order AS Document.Customer Order).Date AS Order Date,
Movement of GoodsTurnover.Nomenclature
FROM
RegisterAccumulations.Movement of Goods.Turnover AS Movement of GoodsTurnover
WHERE
Movement of GoodsTurnover.Order LINK Document.Client Order";

Operators CHOICE And IS NULL

Operator CHOICE similar to operator IF in the built-in 1C language, but has somewhat reduced functionality.

Let's say we want to receive contact information from the ContactInformation information register and, at the same time, indicate in a separate request field whether it belongs to a counterparty or an individual.

Request. Text= "CHOOSE
ContactInformation.Introduction,
CHOICE
WHEN VALUES TYPE(ContactInformation.Object) = TYPE(Directory.Counterparties)
THEN " Counterparty "
ELSE CHOICE
WHEN VALUES TYPE(ContactInformation.Object) = TYPE(Directory.Individuals)
THEN " Individual"
ELSE "Someone else" "
END
END AS OWNER
FROM
Register of Information. Contact Information AS Contact Information";

As can be seen from the example, in the design CHOICE there is always a condition after the word WHEN; value applied if the condition is true after the word THEN and the value applied if the condition is not met, after the word OTHERWISE. All three design elements CHOICE are mandatory. Omit element OTHERWISE, the same way as when using the operator IF in the built-in 1C language, it is impossible. Also from the operator CHOICE there is no analogue of the design ELSEIF, but you can invest one CHOICE in another, as was done in our example.

Operator IS NULL used in design CHOICE to compare a query field with type NULL.

Request. Text= "CHOOSE
CHOICE
WHEN THE VALUE IS NULL THEN 0
ELSE Meaning
END";

In addition, the operator IS NULL can be used in query conditions, such as in a sentence WHERE.

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

Calculation of the SUBSTRING() function on SQL server:
In the client-server version of the work, the SUBSTRING() function is implemented using the SUBSTRING() function of the corresponding SQL statement, passed to the SQL Server database server, which calculates the type of the result of the SUBSTRING() function using 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 by SQL Server is essential for query execution. It is important to keep in mind that in some contexts when using the SUBSTRING() function, the maximum length of its result may be equal to the maximum length of a limited-length string, which in SQL Server is 4000 characters. This may cause the request to terminate unexpectedly.

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

ELSE NULL
END AS A Presentation,
CHOICE
WHEN Type = &LegalAddressIndividual
THEN SUBSTRING(Representation, 0, 200)
ELSE NULL
END AS Presentation1
FROM

SORT BY
Performance,
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 occurs because Microsoft SQL Server calculates the maximum length of the string that results from the expression:
Code 1C v 8.x SELECTION
WHEN Type = &LegalAddressIndividual
THEN SUBSTRING(Representation, 0, 200)
ELSE NULL
END AS A Presentation,
equals 4000 characters. Therefore, the length of a record consisting of two such fields exceeds the 8000 bytes allowed for the sort operation.

Due to the described peculiarity of the execution of the SUBSTRING() function on SQL Server, using the SUBSTRING() function to convert strings of unlimited length to strings of limited length is not recommended. Instead, it is better to use the cast operator EXPRESS(). In particular, the above example can be rewritten as:
Code 1C v 8.x SELECT
CHOICE
WHEN Type = &LegalAddressIndividual
ELSE NULL
END AS A Presentation,
CHOICE
WHEN Type = &LegalAddressIndividual
THEN EXPRESS(Representation AS String(200))
ELSE NULL
END AS Presentation1
FROM
Register of Information. Contact Information HOW Contact Information
SORT BY
Performance,
Performance1

Greetings, dear readers of the blog site! Today, with the help of examples, we will look at how you can use the 1C query function in practice SUBSTRING. Using this function is useful not only in simple queries, which we will look at here, but also in queries and queries.

The task was what was needed from the document details Compliance select two lines and sort by them. Compliance with an account of this type: 779000/004599. How can I split this value into two?

The SUBSTRING function has three parameters. The first is the source string from which you want to extract part of the characters - a substring. It is clear that the value type of this parameter is Line. Attention, this is a very important note, if you use the type of the first parameter other than a string, the function will not work, the request will generate an error! The second parameter determines the position of the character in the source string from which the selection of part of the string begins, and the third is the length of the selected substring. The value type for the second and third parameters is number.

Let's move on to practice:

SELECT Typical.Recorder AS Name, SUBSTRING(Typical.Registrar.According.Name, 1, 6) AS Cost Account, SUBSTRING(Typical.Registrar.According.Name, 8, 11) AS Department, AMOUNT(SELECT WHEN MONTH(Typical.Period) = 3 THEN EXPRESS(Typical.Amount AS NUMBER(15, 2)) ELSE 0 END) AS March FROM RegisterAccounting .Typical AS Typical WHERE Typical.AccountKt = &AccountKt AND Typical.Period BETWEEN &PeriodStart AND &PeriodEnGROUP BY SUBSTRING(Typical.Recorder.According.Name, 1, 6), SUBSTRING(Typical.Registrar.According.Name, 8, 11), Typical.Registrar ORDER BY Department, Cost Account

The result of this query is the following:

Name	Cost Account	Department	March
Outgoing payment order 00000000319 from 03/01/2010 14:42:54	779000	004599	9 000
Outgoing payment order 00000000320 from 03/02/2010 12:07:34	779000	004599	4 721,6
Outgoing payment order 00000000203 from 03/01/2010 12:28:52	786500	004599	987 614,51
Outgoing payment order 00000000227 from 03/03/2010 14:16:00	786500	004599	400 000
Outgoing payment order 00000000238 from 03/05/2010 12:37:57	732000	004600	5 400
Outgoing payment order 00000000197 from 03/01/2010 11:53:11	732500	004600	12 100
Outgoing payment order 00000000198 from 03/01/2010 11:55:39	732500	004600	12 100
Outgoing payment order 00000000279 from 03/26/2010 0:00:00	734100	004600	19 609
Outgoing payment order 00000000287 from 03/29/2010 14:15:36	734100	004600	55 300
Outgoing payment order 00000000291 from 03/30/2010 11:01:10	734100	004600	18 090
Outgoing payment order 00000000268 from 03/18/2010 10:34:25	738000	004600	10 050
Outgoing payment order 00000000276 from 03/18/2010 12:20:20	750400	004600	13 060,98
Outgoing payment order 00000000281 from 03/29/2010 12:33:46	750400	004600	555 645,41
Outgoing payment order 00000000234 from 03/04/2010 12:21:55	754450	004600	24 120
Outgoing payment order 00000000290 from 03/30/2010 10:44:39	754450	004600	100 000
Outgoing payment order 00000000240 from 03/09/2010 10:53:24	786300	004600	20 800
Outgoing payment order 00000000269 from 03/18/2010 10:58:04	786300	004600	61 012
Outgoing payment order 00000000289 from 03/30/2010 9:27:14	786300	004600	6 000
Outgoing payment order 00000000223 from 03/03/2010 12:13:38	786500	004600	36 000
Outgoing payment order 00000000228 from 03/04/2010 9:52:35	786500	004600	378 138,85
Outgoing payment order 00000000229 from 03/04/2010 9:57:50	786503	004600	126 117,75
Outgoing payment order 00000000200 from 03/01/2010 11:58:06	754422	004762	63 000
Outgoing payment order 00000000286 from 03/29/2010 14:10:18	764422	004762	10 000
Outgoing payment order 00000000267 from 03/17/2010 0:00:00	764423	004762	464 370
Outgoing payment order 00000000261 from 03/15/2010 11:16:28	764522	004762	81 357

So, if we take the line 779000/004599, then SUBSTRING(Typical.Recorder.Respective.Name, 1, 6) will highlight the line “779000”. A SUBSTRING(Typical.Recorder.Respective.Name, 8, 11) will output “004599”.

In the same query, the expression is used:

SUM(CHOICE WHEN MONTH(Typical.Period) = 3 THEN EXPRESS(Typical.Sum AS NUMBER(15, 2)) ELSE 0 END) AS March

In the same task, it was necessary to add new columns with the names of the months and with the amounts for them. This expression solves this problem. If you need to use other months, for example January, then replace the expression:

AMOUNT(CHOICE WHEN MONTH(Typical.Period) = 1 THEN EXPRESS(Typical.Sum AS NUMBER(15, 2)) ELSE 0 END) AS January

I will give an example of a request text in which month fields (in bold) are formed using a cycle, starting from March to February.

So, using the code above (in bold), parts of the request with months are displayed. Thus, new columns are added with the names of the months and with the amounts for them.

The query language is one of the fundamental mechanisms of 1C 8.3 for developers. Using queries, you can quickly retrieve any data stored in the database. Its syntax is very similar to SQL, but there are some differences.

The main advantages of the 1C 8.3 (8.2) query language over SQL:

dereferencing reference fields (referring one or more points to object details);
working with results is very convenient;
the ability to create virtual tables;
the request can be written in both English and Russian;
ability to block data to avoid deadlocks.

Disadvantages of the query language in 1C:

unlike SQL, in 1C queries do not allow changing data;
lack of stored procedures;
impossibility of converting a string to a number.

Let's take a look at our mini tutorial on the basic constructs of the 1C query language.

Due to the fact that queries in 1C only allow you to receive data, any query must begin with the word “SELECT”. After this command, the fields from which data must be obtained are indicated. If you specify “*”, all available fields will be selected. The place from which the data will be selected (documents, registers, directories, etc.) is indicated after the word “FROM”.

In the example discussed below, the names of the entire nomenclature are selected from the “Nomenclature” directory. After the word “HOW”, aliases (names) for tables and fields are indicated.

CHOOSE
Nomenclature. Name AS Name of Nomenclature
FROM
Directory.Nomenclature AS Nomenclature

Next to the “SELECT” command you can specify keywords:

VARIOUS. The query will select only rows that differ in at least one field (without duplicates).
FIRST n, Where n– the number of rows from the beginning of the result that need to be selected. Most often, this construction is used in conjunction with sorting (ORDER BY). For example, when you need to select a certain number of documents that are recent by date.
ALLOWED. This design allows you to select from the database only those records that are available to the current user. Based on the use of this keyword, the user will receive an error message when attempting to query records that they do not have access to.

These keywords can be used together or separately.

FOR CHANGE

This proposal blocks data to prevent mutual conflicts. Locked data will not be read from another connection until the transaction ends. In this clause, you can specify specific tables that need to be locked. Otherwise, everyone will be blocked. The design is relevant only for the automatic locking mode.

Most often, the “FOR CHANGE” clause is used when receiving balances. After all, when several users work in the program simultaneously, while one receives balances, another can change them. In this case, the resulting remainder will no longer be correct. If you block the data with this proposal, then until the first employee receives the correct balance and performs all the necessary manipulations with it, the second employee will be forced to wait.

CHOOSE
Mutual settlements. Employee,
Mutual settlements. Amount of mutual settlements Balance
FROM
Register of Accumulations. Mutual settlements with employees. Balances AS Mutual settlements
FOR CHANGE

WHERE

The design is necessary to impose some kind of selection on the uploaded data. In some cases of obtaining data from registers, it is more reasonable to specify selection conditions in the parameters of virtual tables. When using "WHERE", all records are retrieved first, and only then selection is applied, which significantly slows down the query.

Below is an example of a request to obtain contact persons for a specific position. The selection parameter has the format: &ParameterName (the parameter name is arbitrary).

SELECTION (CASE)

The design allows you to specify conditions directly in the body of the request.

In the example below, the “AdditionalField” will contain text depending on whether the document is posted or not:

CHOOSE
AdmissionT&U.Link,
CHOICE
WHEN AdmissionT&U.Performed
THEN “The document has been passed!”
ELSE “The document was not posted...”
END AS AdditionalField
FROM
Document. Receipt of Goods and Services HOW Receipt T&C

JOIN

Joins link two tables based on a specific relationship condition.

LEFT/RIGHT CONNECTION

The essence of the LEFT join is that the first specified table is taken in its entirety and the second one is linked to it according to the connection condition. If there are no records corresponding to the first table in the second, then NULL is substituted as their values. Simply put, the main table is the first specified table and the data of the second table (if any) is already substituted for its data.

For example, it is necessary to obtain item items from the “Receipt of goods and services” documents and prices from the information register “Item prices”. In this case, if the price for any position is not found, substitute NULL instead. All items from the document will be selected regardless of whether they have a price or not.

CHOOSE
Receipt&U.Nomenclature,
Prices.Price
FROM
Document. Receipt of Goods and Services. Goods HOW Receipt T&C
INTERNAL JOIN RegisterInformation.PricesNomenclature.SliceLast AS Prices
Software Receipt&U.Nomenclature = Prices.Nomenclature

IN THE RIGHT everything is exactly the opposite.

FULL CONNECTION

This type of connection differs from the previous ones in that as a result all records of both the first table and the second will be returned. If no records are found in the first or second table based on the specified link condition, NULL will be returned instead.

When using a full connection in the previous example, all item items from the “Receipt of Goods and Services” document and all the latest prices from the “Item Prices” register will be selected. The values of not found records in both the first and second tables will be equal to NULL.

INNER JOIN

The difference between an INNER JOIN and a FULL JOIN is that if a record is not found in at least one of the tables, the query will not display it at all. As a result, only those item items from the document “Receipt of goods and services” will be selected for which there are records in the information register “Item prices”, if in the previous example we replace “FULL” with “INTERNAL”.

GROUP BY

Grouping in 1C queries allows you to collapse table rows (grouping fields) according to a certain common characteristic (grouping fields). Grouping fields can only be displayed using aggregate functions.

The result of the following query will be a list of product types with maximum prices for them.

CHOOSE
,
MAX(Price.Price) AS Price
FROM

GROUP BY
Prices.Nomenclature.Type of Nomenclature

RESULTS

Unlike grouping, when using totals, all records are displayed and total rows are added to them. Grouping displays only generalized records.

Results can be summarized for the entire table (using the keyword “GENERAL”), for several fields, for fields with a hierarchical structure (keywords “HIERARCHY”, “ONLY HIERARCHY”). When summarizing results, it is not necessary to use aggregate functions.

Let's look at an example similar to the example above using grouping. In this case, the query result will return not only grouped fields, but also detailed records.

CHOOSE
Prices.Nomenclature.Type of Nomenclature AS Type of Nomenclature,
Prices.Price AS Price
FROM
Register of Information. Prices of Nomenclature. Snapshot of the Latest AS Prices
RESULTS
MAXIMUM(Price)
BY
TypeNomenclature

HAVING

This operator is similar to the WHERE operator, but is used only for aggregate functions. The remaining fields, except those used by this operator, must be grouped. The WHERE operator is not applicable to aggregate functions.

In the example below, the maximum prices of an item are selected if they exceed 1000, grouped by item type.

CHOOSE

MAX(Price.Price) AS Price
FROM
Register of Information. Prices of Nomenclature. Snapshot of the Latest AS Prices
GROUP BY
Prices.Nomenclature.Type of Nomenclature
HAVING
MAXIMUM(Prices.Price) > 1000

SORT BY

The ORDER BY operator sorts the result of a query. To ensure that records are displayed in a consistent order, AUTO ORDER is used. Primitive types are sorted according to the usual rules. Reference types are sorted by GUID.

An example of getting a list of employees sorted by name:

CHOOSE
Employees.Name AS Name
FROM
Directory.Employees HOW Employees
SORT BY
Name
AUTO ORDER

Other 1C query language constructs

COMBINE– results of two queries into one.
COMBINE EVERYTHING– similar to COMBINE, but without grouping identical rows.
EMPTY TABLE– sometimes used when joining queries to specify an empty nested table.
PLACE– creates a temporary table to optimize complex 1C queries. Such requests are called batch requests.

Query Language Features

SUBSTRING truncates a string from a specified position to a specified number of characters.
YEAR...SECOND allow you to get the selected value of a numeric type. The input parameter is the date.
BEGINNING OF PERIOD and END OF PERIOD used when working with dates. The type of period (DAY, MONTH, YEAR, etc.) is indicated as an additional parameter.
ADDKDATE allows you to add or subtract a specified time of a certain type from a date (SECOND, MINUTE, DAY, etc.).
DIFFERENCEDATE determines the difference between two dates, indicating the type of output value (DAY, YEAR, MONTH, etc.).
ISNULL replaces the missing value with the specified expression.
REPRESENTATION and REPRESENTATIONLINKS get a string representation of the specified field. Apply to any values and only reference values, respectively.
TYPE, TYPE VALUES are used to determine the type of the input parameter.
LINK is a logical comparison operator for the attribute value type.
EXPRESS used to convert a value to the desired type.
DATE TIME gets a Date value from numeric values (Year, Month, Day, Hour, Minute, Second).
MEANING in a 1C request it is used to indicate predefined values - directories, enumerations, plans for types of characteristics. Usage example: " Where Legal Individual = Value(Enumeration. Legal Individual. Individual)«.

Query Builder

To create queries with 1C there is a very convenient built-in mechanism - the query designer. It contains the following main tabs:

“Tables and Fields” - contains the fields that need to be selected and their sources.
“Connections” - describes the conditions for the CONNECTION structure.
“Grouping”—contains a description of grouping structures and summed fields based on them.
“Conditions” - is responsible for selecting data in the request.
“Advanced” - additional query parameters, such as keywords for the “SELECT” command, etc.
“Joins/Aliases” - the possibilities of joining tables are indicated and aliases are specified (the “HOW” construct).
“Order” is responsible for sorting the result of queries.
“Totals” - similar to the “Grouping” tab, but used for the “TOTALS” construct.

The text of the request itself can be viewed by clicking on the “Request” button in the lower left corner. In this form, it can be corrected manually or copied.

Request Console

To quickly view the result of a query in Enterprise mode, or debug complex queries, use . It contains the text of the request, sets the parameters, and displays the result.

You can download the query console on the ITS disk, or via .

Presentation on the topic "Holidays in USA (Holidays in the USA)"

How Maps Are Made: A Brief History of Cartography

Utilities program

Prepositions with dates in English (with months, days of the week)