Functions for working with query strings 1s. Feature of the substring() function. Getting the value of a field of a complex type using a dot

25.10.2023

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(SELECT 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 section contains a description of the peculiarities of executing the SUBSTRING() function of the query language in the client-server version of work and the resulting recommendations for constructing queries.

SUBSTRING() function

In the 1C:Enterprise query language, the SUBSTRING() function in the SUBSTRING(,) format can be applied to string type data and allows you to select a fragment starting with the character number (characters in a string are numbered starting with 1) and the length of the characters. The result of the SUBSTRING() function is a variable-length string type, and the length will be considered unlimited if it has unlimited length and the parameter is not a constant or exceeds 1024.

Calculating the SUBSTRING() function in 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:

CHOOSE
CHOICE

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

SORT BY
Performance,
Performance1

crashes 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:

CHOICE
WHEN Type = & Legal AddressIndividual
THEN SUBSTRING(Representation, 0 , 200 )
ELSE NULL
THE 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:

CHOOSE
CHOICE
WHEN Type = & Legal AddressIndividual
ELSE NULL
THE END AS A Presentation,
CHOICE
WHEN Type = & Legal AddressIndividual
THEN EXPRESS(Representation AS String(200 ) )
ELSE NULL
END AS Presentation1
FROM
Information Register. ContactInformation HOW TO ContactInformation
SORT BY
Performance,
Performance1

In this article we want to discuss everything with you 1C query language functions, and query language constructs. What is the difference between function and design? The function is called with parentheses and possible parameters in them, and the construct is written without parentheses. Undoubtedly all structures and functions of the 1C query language make the data acquisition process flexible and multifunctional. These functions and constructs apply to query fields, and some also apply to conditions.

1C Query Language Functions

Because a clear description 1c query language functions is much less common than descriptions of structures, we decided to start looking at functions. Now let's look at each one separately, describing its purpose, syntax and example of use, so:

1. Function DATE TIME- this function creates a constant field with the "Date" type.

Syntax: DATE TIME(<Год>,<Месяц>,<День>,<Час>,<Минута>,<Секунда>)

Usage example:

2. DATE DIFFERENCE function- returns the difference between two dates in one of the dimensions (year, month, day, hour, minute, second). The measurement is passed as a parameter.

Syntax: DIFFERENCEDATE(<Дата1>, <Дата2>, <Тип>)

Usage example:

Query.Text = "SELECT | DIFFERENCEDATE(DATETIME(2015, 4, 17), DATETIME(2015, 2, 1), DAY) | AS Number of Days";

3. Function VALUE- sets a constant field with a predefined record from the database; you can also get an empty link of any type.

Syntax: VALUE(<Имя>)

Usage example:

Request.Text = "SELECT //predefined element | VALUE(Directory.Currencies.Dollar) AS Dollar, //empty link | VALUE(Document.Receipt of Goods and Services.EmptyLink) AS Receipt, //transfer value | VALUE(Transfer. Legal Individual. Individual) AS Individual, //predefined account VALUE(Chart of Accounts. Self-Accounting.Materials) AS Account_10" ;

4. SELECT function- we have before us an analogue of the IF construction, which is used in the code, only this one is used in 1C queries.

Syntax: CHOICE WHEN<Выражение>THEN<Выражение>OTHERWISE<Выражение>END

Usage example:

Request.Text = //if the amount is more than 7500, then there should be a discount of 300 rubles, //so if the condition is triggered then the function //returns Sum - 300 //otherwise the request will return simply Sum "SELECT | SELECT | WHEN TCReceipts.Amount > 7500 | THEN TCReceipts.Amount - 300 | ELSE TCReceipts.Amount | END AS AmountWithDiscount | FROM |

5. EXPRESS function- allows you to express a constant field with a specific type.

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

Is there another option for using the EXPRESS function in fields of mixed types, where do they occur? The simplest example is the “Registrar” for any register. So why might we need to qualify the type in the registrar? Let's consider the situation when we select the "Number" field from the registrar, from which table will the number be selected? The correct answer of all! Therefore, in order for our query to work quickly, we must specify an explicit type using the EXPRESS function

Usage example:

Query.Text = "SELECT | EXPRESS(Nomenclature.Comment AS Line(300)) AS Comment, | EXPRESS(Nomenclature.Sum AS Number(15,2)) AS Sum |FROM | Directory.Nomenclature AS Nomenclature";

6. ISNULL function(alternative spelling ISNULL) - if the field is of type NULL, then it is replaced with the second parameter of the function.

Syntax: ISNULL(<Поле>, <ПодставляемоеЗначение>)

Usage example:

Also note that it is advisable to ALWAYS replace the NULL type with some value, because comparison with type NULL always returns FALSE even if you compare NULL with NULL. Most often, NULL values are formed as a result of joining tables (all types of joins except internal ones).

Query.Text = //Select the entire item and its balances //if there is no balance in some item, then there will be a field //NULL which will be replaced with the value 0 "SELECT | No. Link, | ISNULL(ProductsInStockRemains.InStockRemaining, 0) AS Remaining | FROM | Directory.Nomenclature AS No. | LEFT CONNECTION RegisterAccumulations.GoodsInWarehouses.Remainings AS GoodsInWarehousesRemainings | PO (GoodsInWarehousesRemainings.Nomenclature = No.Link)";

7. REPRESENTATION function- allows you to get a representation of the request field.

Syntax: PERFORMANCE(<НаименованиеПоля>)

Usage example:

Query.Text = "SELECT | REPRESENTATION(FreeRemainingRemains.Nomenclature) AS Nomenclature, | REPRESENTATION(FreeRemainingRemaining.Warehouse) AS Warehouse, | FreeRemainingRemaining.InStockRemaining |FROM |Accumulation Register.FreeRemaining.Remaining AS FreeRemainingRemaining";

Constructs in the 1C query language

We discussed with you above 1C query language functions, now it's time to consider constructs in the 1C query language, they are no less important and useful, let’s get started.

1. Construction LINK- is a logical operator for checking a reference type. Most often encountered when checking a field of a complex type against a specific type. Syntax: LINK<Имя таблицы>

Usage example:

Request.Text = //if the recorder value type is document Receipt, //then the query will return "Receipt of goods", otherwise "Sales of goods" "SELECT | SELECT | WHEN Remainings.Registrar LINK Document.Receipt of Goods and Services | THEN ""Receipt"" | ELSE ""Consumption"" | END AS Type of Movement | FROM | Register of Accumulation. Remaining Products in Warehouses AS Remaining" ;

2. Design BETWEEN- this operator checks whether the value is within the specified range.

Syntax: BETWEEN<Выражение>AND<Выражение>

Usage example:

3. Construction B and B HIERARCHY- check whether the value is in the transferred list (arrays, tables of values, etc. can be transferred as a list). The IN HIERARCHY operator allows you to view the hierarchy (an example of using the Chart of Accounts).

Syntax: IN(<СписокЗначений>), IN HIERARCHY(<СписокЗначений>)

Usage example:

4. Design SIMILAR- This function allows us to compare a string with a string pattern.

Syntax: LIKE "<ТекстШаблона>"

Row pattern options:

% - a sequence containing any number of arbitrary characters.

One arbitrary character.

[...] - any single character or sequence of characters listed inside square brackets. The enumeration can specify ranges, for example a-z, meaning an arbitrary character included in the range, including the ends of the range.

[^...] - any single character or sequence of characters listed inside square brackets except those listed after the negation sign.

Usage example:

5. Design ALLOWED- this operator allows you to select only those records from the database for which the caller has read permission. These rights are configured at the record level (RLS).

Syntax: ALLOWED is written after the keyword SELECT

Usage example:

Request.Text = "SELECT ALLOWED | Counterparties. Link | FROM | Directory. Counterparties AS Counterparties";

6. Design VARIOUS- allows you to select records in which there are no duplicate records.

Syntax: VARIOUS is written after the keyword SELECT

Usage example:

Request.Text = //selects records to which the reader has rights "SELECT VARIOUS | Counterparties.Name |FROM | Directory. Counterparties AS Counterparties" ;

Also, the VARIOUS construction can be used with the ALLOWED operator and other operators.

Usage example:

Request.Text = //selects various records to which the reader has rights "SELECT ALLOWED VARIOUS | Counterparties.Name |FROM | Directory. Counterparties AS Counterparties";

7. Design FIRST- selects the number of records specified in the parameter from the query result.

Syntax: FIRST<число>

Usage example:

Request.Text = //select the first 4 CCD numbers from the directory "SELECT FIRST 4 | CCD Numbers. Link | FROM | Directory. CCD Numbers AS CCD Numbers";

8. Design FOR CHANGE- allows you to lock a table, works only in transactions (relevant only for automatic locks).

Syntax: FOR CHANGE<НаименованиеТаблицы>

Usage example:

9. Design ORDER BY- organizes data by a specific field. If the field is a link, then when setting the flag AUTO ORDER Sorting will occur by link representation; if the flag is turned off, then links are sorted by the seniority of the link address in memory.

Syntax: SORT BY<НаименованиеПоля>AUTO ORDER

Usage example:

10. Design GROUP BY- used to group query strings by specific fields. Numeric fields must be used with any aggregate function.

Syntax: GROUP BY<НаименованиеПоля1>, .... , <НаименованиеПоляN>

Usage example:

11. Design HAVING- allows you to apply an aggregate function to a data selection condition, similar to the WHERE construction.

Syntax: HAVING<агрегатная функция с условием>

Usage example:

12. Construction INDEX BY- used for indexing the query field. A query with indexing takes longer to complete, but speeds up searching through indexed fields. Can only be used in virtual tables.

Syntax: INDEX BY<Поле1, ... , ПолеN>

Usage example:

13. Design WHERE- allows you to impose a condition on any selection fields. The result will include only records that satisfy the condition.

Syntax: WHERE<Условие1 ОператорЛогСоединения УсловиеN>

Usage example:

Query.Text = //all records with CompensationRemaining are selected<>0 and //AmountForCalcCompRemaining > 100 "SELECT | CompensationRPORemains.Counterparty, |CompensationRPORemains.Child, | CompensationRPORemains.CompensationRemaining, | CompensationRPORemains.AmountForCalcCompRemains |Place DataTz |FROM | Accumulation Register.CompensationRP.Remains AS CompensationRPORemains |WHERE |CompensationRPORemaining.CompensationRemaining<>0 | And CompensationRPORemains.AmountForCalcCompRemaining> 100" ;

14. Design RESULTS... GENERAL- used to calculate totals; the design specifies the fields by which totals will be calculated and aggregate functions applied to the total fields. When using totals for each field following the TOTAL construction, data is grouped. There is an optional GENERAL construct; its use also provides additional grouping. You will see an example of the request result below.

Syntax: RESULTS<АгрегатнаяФункция1, ... , АгрегатнаяФункцияN>BY<ОБЩИЕ> <Поле1, ... , ПолеN>

Usage example:

The figure outlines the groupings that were formed during the execution of the request, the top one refers to the GENERAL section, and the second to the Counterparty AgreementAgreement Type field.

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 designed 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 times, so it can be useful to convert a 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.

views

Save VKontakte