Working with strings in a query 1s 8.2. Subqueries in a join condition

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 / SUBSTRING. Using this function is useful not only in simple queries, which we will consider here, but also in queries, as well as in queries.

The task was that it was necessary from the details of the document Compliance select two lines and sort by them. Correspondence of the account, of this type: 779000/004599. How to split this value into two?

The SUBSTRING function has three parameters. The first string 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 not a string, the function will not work, the request will generate an error! The second parameter specifies the position of the character in the source string from which the selection of a 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 Generic.Registrar AS Name, SUBSTRING(Typical.Registrar.Resp.Description, 1, 6) AS Cost Invoice, SUBSTRING(Typical.Registrar.Resp.Name, 8, 11) AS Department, SUM(SELECT WHEN MONTH(Typical.Period) = 3 THEN EXPRESS(Typical.Amount AS A NUMBER(15, 2)) ELSE 0 END) AS March FROM Register Accounting .Type AS Typical WHERE Typical.Account = &Account AND Typical.Period BETWEEN &PeriodStart AND &PeriodEnd GROUP BY SUBSTRING(Typical.Registrar.Corresponding.Name, 1, 6), SUBSTRING(Typical.Registrar.Resp.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 dated 03/01/2010 14:42:54	779000	004599	9 000
Outgoing payment order 00000000320 dated 02.03.2010 12:07:34	779000	004599	4 721,6
Outgoing payment order 00000000203 dated 03/01/2010 12:28:52	786500	004599	987 614,51
Outgoing payment order 00000000227 dated 03.03.2010 14:16:00	786500	004599	400 000
Outgoing payment order 00000000238 dated 05.03.2010 12:37:57	732000	004600	5 400
Outgoing payment order 00000000197 dated 03/01/2010 11:53:11	732500	004600	12 100
Outgoing payment order 00000000198 dated 03/01/2010 11:55:39	732500	004600	12 100
Outgoing payment order 00000000279 dated 03/26/2010 0:00:00	734100	004600	19 609
Outgoing payment order 00000000287 dated 03/29/2010 14:15:36	734100	004600	55 300
Outgoing payment order 00000000291 dated 03/30/2010 11:01:10	734100	004600	18 090
Outgoing payment order 00000000268 dated 18.03.2010 10:34:25	738000	004600	10 050
Outgoing payment order 00000000276 dated 18.03.2010 12:20:20	750400	004600	13 060,98
Outgoing payment order 00000000281 dated 03/29/2010 12:33:46 PM	750400	004600	555 645,41
Outgoing payment order 00000000234 dated 04.03.2010 12:21:55	754450	004600	24 120
Outgoing payment order 00000000290 dated 30.03.2010 10:44:39	754450	004600	100 000
Outgoing payment order 00000000240 dated 03/09/2010 10:53:24	786300	004600	20 800
Outgoing payment order 00000000269 dated 18.03.2010 10:58:04	786300	004600	61 012
Outgoing payment order 00000000289 dated 03/30/2010 9:27:14	786300	004600	6 000
Outgoing payment order 00000000223 dated 03.03.2010 12:13:38	786500	004600	36 000
Outgoing payment order 00000000228 dated 03/04/2010 9:52:35	786500	004600	378 138,85
Outgoing payment order 00000000229 dated 03/04/2010 9:57:50	786503	004600	126 117,75
Outgoing payment order 00000000200 dated 03/01/2010 11:58:06	754422	004762	63 000
Outgoing payment order 00000000286 dated 03/29/2010 14:10:18	764422	004762	10 000
Outgoing payment order 00000000267 dated 03/17/2010 0:00:00	764423	004762	464 370
Outgoing payment order 00000000261 dated 15.03.2010 11:16:28	764522	004762	81 357

So, if we take the string 779000/004599, then SUBSTRING(Typical.Registrar.Respective.Name, 1, 6) will highlight the string "779000". A SUBSTRING(Typical.Registrar.Ac.Name, 8, 11) will output "004599".

In the same query, the expression is used:

SUM(SELECT 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. Just this expression solves this problem. If you need to use other months, for example January, then replace the expression:

SUM(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 query text in which the month fields are formed using a cycle (highlighted in bold), starting from March to February.

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

In this article, we want to discuss with you all query language functions 1s, and query language constructs. What is the difference between a function and a structure? The function is called with brackets and possible parameters in them, and the construction is written without brackets. Undoubtedly all constructions and functions of the 1s query language make the data acquisition process flexible and multifunctional. These functions and constructs apply to query fields, and some also apply to conditions.

Query Language Functions 1s

Since a clear description query language functions 1s is much less common than the description of structures, we decided to start looking at functions. Now let's analyze each one separately, describing its purpose, syntax and usage example, 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 of 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 | DATE DIFFERENCE(DATETIME(2015, 4, 17), DATETIME(2015, 2, 1), DAY) | AS Number of Days";

3. Function VALUE- sets a constant field with a predefined entry from the database, you can also get a null reference of any type.

Syntax: VALUE(<Имя>)

Usage example:

Query.Text = "SELECT //predefined element | VALUE(Currency.Dollar.Catalog.Dollar) AS Dollar, //empty reference | VALUE(Document.IncomingGoodsServices.EmptyReference) AS Receipt, //transfer value | VALUE(Transfer.LegalIndividual. Individual) AS Individual, //predefined account | VALUE(Chart of Accounts.Self-supporting.Materials) AS Account_10" ;

4. SELECT function- we have an analogue of the IF construction that 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, //therefore, if the condition is triggered, then the function //returns the Amount - 300 //otherwise, the request will simply return the Amount "SELECT | SELECT | WHEN PMIncome.Amount > 7500 | THEN PTReceipts.Amount - 300 | ELSE PTReceipts.Amount | END AS AmountDiscounted |FROM | Document.ReceiptofGoodsServices.Goods AS PTReceipts";

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

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

Still there is a variant of use of the EXPRESS function in fields of the mixed types where such meet? The simplest example is the "Registrar" for any register. So why would 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? 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 = "CHOOSE | EXPRESS(Nomenclature.Comment AS String(300)) AS Comment, | EXPRESS(Nomenclature.Sum AS Number(15,2)) AS Sum |FROM | Reference.Nomenclature AS Nomenclature";

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

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

Usage example:

Also note that it is desirable to ALWAYS replace the NULL type with some value, because comparison with NULL always evaluates to FALSE even if you are comparing NULL with NULL. Most often, NULL values are formed as a result of table joins (all types of joins except inner).

Query.Text = //Select the entire item and the balances on it //if there is no balance in some item, then there will be a field //NULL which will be replaced by the value 0 "SELECT | No.Reference, | ISNULL(GoodsInWarehouseRemains.InStockRemaining, 0) HOW Remainder | FROM | Directory. Nomenclature AS Nom | LEFT JOIN Accumulation register.

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

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

Usage example:

Query.Text = "SELECT | REPRESENTATION(Free RemainsRemains.Nomenclature) AS Nomenclature, | REPRESENTATION(FreeRemainsRemains.Warehouse) AS Warehouse, | FreeRemainsRemains.AvailableRemains |FROM | Accumulation Register.FreeRemains.Remains AS FreeRemainsRemains";

Constructions in the query language 1s

Above we discussed with you query language functions 1s, now it's time to consider constructions in query language 1s, they are no less important and useful, let's get started.

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

Usage example:

Query.Text = //if the value type of the registrar is document Receipt, //then the query will return "Incoming Goods", otherwise "Sale of Goods" "SELECT | SELECT | WHEN Remains.Registrar LINK Document.Incoming GoodsServices | THEN ""Incoming" | ELSE "Expense" | END AS Type of Movement | FROM | Accumulation Register. Remains of Goods in Warehouses AS Remains ";

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

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

Usage example:

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

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

Usage example:

Query.Text = // select all sub-accounts of the account "SELECT | Self-supporting. Link AS Account | FROM | Plan of Accounts. Self-supporting AS Self-supporting | WHERE | Self-supporting. Reference IN HIERARCHY VALUE(Schedule of Accounts. Self-supporting. Goods)";

4. Construction LIKE- this function allows us to compare a string against a string pattern.

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

Line template options:

% - a sequence containing any number of arbitrary characters.

One arbitrary character.

[...] - any single character, or a sequence of characters listed inside square brackets. The enumeration can contain ranges, such as a-z, meaning any character within the range, including the ends of the range.

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

Usage example:

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

Syntax: ALLOWED is written after the keyword SELECT

Usage example:

Query.Text = "SELECT ALLOWED | Counterparties.Reference |FROM | Directory.Counterparties AS Counterparties";

6. Construction VARIOUS- allows you to select records in which there are no repeated records.

Syntax: DIFFERENT is written after the keyword SELECT

Usage example:

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

The DIFFERENT construct can also be used with the ALLOWED operator and other operators.

Usage example:

Query.Text = //selects various records to which the reader has rights "SELECT ALLOWED DIFFERENT | Contractors.Name |FROM | Directory.Contractors AS Contractors";

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

Syntax: FIRST<число>

Usage example:

Query.Text = //select the first 4 GTD numbers from the directory "SELECT THE FIRST 4 | GTD Numbers.Reference |FROM | Catalog.GTE Numbers AS GTD Numbers";

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

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

Usage example:

9. Structure ORDER BY- sorts data by a specific field. If the field is a link, then when setting the flag AUTO ORDER will be sorted according to the link representation, if the flag is off, then the 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 certain 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 the data selection condition, similar to the WHERE construction.

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

Usage example:

12. INDEX BY construct- used to index the query field. An indexed query takes longer to complete, but speeds up searches on indexed fields. Can only be used in virtual tables.

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

Usage example:

13. Construction WHERE- allows you to impose a condition on any fields of the selection. Only records that meet the condition will be included in the result.

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

Usage example:

Query.Text = //select all records with CompensationRemainder<>0 and // AmountFor Calc.CompBalance > 100 "SELECT | Compensation RPO balances.Counterparty, | Compensation RPO balances.Child, | Compensation RPO balances. Compensation Balance, | Compensation RPO balances. AmountFor Calc.Comp Balance | PLACE DataTz | FROM | Accumulation Register. Compensation RP. Balances AS Compensation R Balances | WHERE | Compensation RPO balances. Compensation Balance<>0 | And Compensation RPO balances. Amount For CalcComp Residual > 100" ;

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

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

Usage example:

The groupings that were formed during the execution of the request are circled in the figure, the top one refers to the GENERAL section, and the second to the ContractContractorTypeContract 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.

First, strings in queries can be added. The "+" operator is used for this:

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

Secondly, you can select part of the string. For this, the function is used SUBSTRING. The function is similar to the built-in language 1C. It has three options:

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

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

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 occur in queries in several cases: when joining query sources, if the 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 the results of a selection from several tables), etc.

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

It has two options:

The value to check.
The value to replace the first parameter with if it is NULL.

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

Functions PERFORMANCE And PRESENTATIONLINKS

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

Request. Text= "CHOOSE
PRESENT(TRUE) AS A Boolean,
REPRESENTATION (4) AS A Number,
PRESENT(Source.Reference) AS Reference,
REPRESENTATION(DATETIME(2016,10,07)) AS Date";
// Boolean = "Yes", Number = "4", Reference = "Document Cash voucher #... dated..."
// Date="10/07/2016 0:00:00"

Request. Text= "CHOOSE
REFERENCE REPRESENTATION(TRUE) AS A Boolean,
REFERENCE REPRESENTATION (4) AS Number,
REPRESENTATIONLINK(Source.Link) AS Link,
REFERENCE REPRESENTATION(DATETIME(2016,10,07)) AS Date";
// Boolean = TRUE, Number = 4, Ref = "Document Cash voucher #... dated..."
// Date=07.10.2016 0:00:00

Functions TYPE And VALUETYPE

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

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

Function VALUETYPE returns the type of the value passed to it.

Request. Text= "CHOOSE
VALUETYPE(5) AS Number,
TYPE (""Line" ") AS String,
TYPE(Source.Reference) AS Reference
From Directory.Source AS Source";
//Number=Number, String=String, Directory = DirectoryReference.Source

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

Request. Text= "CHOOSE

FROM

WHERE
VALUE TYPE(ContactInformation.Object) = TYPE(Directory.Accounts)";

Function MEANING

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

Let's supplement the previous example with one more condition. You need to get only the phones of counterparties.

Request. Text= "CHOOSE
ContactInformation.View
FROM
Register of Information. Contact Information AS Contact Information
WHERE
VALUE TYPE(ContactInformation.Object) = TYPE(Catalog.Accounts)
AND ContactInformation.Type = VALUE(Enumeration.ContactInformation Types.Phone)";

Note 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 user-created lookup elements, but can work with enumerations, with predefined lookup elements, with values EmptyLink.

Operator LINK

Operator LINK is designed to check the values received by the request for belonging to a specific reference type. The same task can be performed using the functions TYPE And VALUETYPE(which have a wider scope and have been discussed above).

For example, the task of choosing the contact information of counterparties could be solved like this:

Request. Text= "CHOOSE
ContactInformation.View
FROM
Register of Information. Contact Information AS Contact Information
WHERE
Contact Information.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 it is necessary to make a field with a single type from a field with a composite data 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 make a string with a limited length from a string with unlimited length. This is useful if you want to group query results by such a field. Grouping by fields with unlimited length is not possible, so we will convert it to a string with a length of 200 characters.

Request. Text= "CHOOSE
QUANTITY(DIFFERENT INCOME OF GOODS/SERVICES.REF) AS REF
FROM
Document.Receipt of Goods/Services AS Receipt of Goods/Services
GROUP BY
EXPRESS(Incoming Goods/Services. Comment AS A STRING(200))";

In some cases, requests for fields with a composite data type may not be processed optimally by the 1C platform. This leads to increased query execution time, so it can be useful to convert a composite type to a single type in advance.

Request. Text= "CHOOSE
EXPRESS(Movement of Goods Turnovers.Order AS Document.Customer Order).Date AS OrderDate,
Movement of Goods Turnovers.Nomenclature
FROM
Accumulation Register. Movement of Goods. Turnovers AS Movement of Goods Turnovers
WHERE
Movement of Goods Turnovers. Order LINK Document. Order of the Client";

Operators CHOICE And IS NULL

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

Suppose we want to get contact information from the Contact Information register and, at the same time, indicate in a separate request field whether they belong to a counterparty or an individual.

Request. Text= "CHOOSE
Contact Information.Representation,
CHOICE
WHEN VALUE TYPE(ContactInformation.Object) = TYPE(Catalog.Accounts)
THEN " counterparty "
ELSE CHOICE
WHEN VALUE TYPE(ContactInformation.Object) = TYPE(Catalog.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; the value to apply 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 structural elements CHOICE are mandatory. Omit element OTHERWISE, just as it is done when using the operator IF in the built-in language 1C, it is impossible. Also the operator CHOICE there is no analogue of the design ELSEIF, but you can put one CHOICE in the other, as was done in our example.

Operator IS NULL used in construction CHOICE to compare the request field with the NULL type.

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

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

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, an object data model is used to change data.

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

Request example:

CHOOSE
Banks.Name,
Banks.CorrectAccount
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.

Query language is the simplest and most efficient way to get information. As you can see from the example above, in the query language, you need to appeal with metadata names ( is a list of system objects that make up the configuration, i.e. directories, documents, registers, etc.).

Description of query language constructs

Request Structure

To obtain data, it is enough to use the SELECT (select) and FROM (from) constructions. The simplest query looks like this:

SELECT * FROM Directories. Nomenclature

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

Consider a more complex and general example:

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

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

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

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

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

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

From the received data, we select only data that meets the condition from the “WHERE” “Data Selection Condition”. Next, we group the query by the “FieldName1” field, while summing up “FieldName2”. We create totals for the “FieldName1” field and the final field “FieldName2”.

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

General designs

Consider the general constructions of the query language 1C 8.2.

FIRSTn

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

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

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

ALLOWED

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

If the user attempts to read records that are not available to him with a query, he will receive an error message. To avoid this, you should use the "ALLOWED" construction, i.e. the request will read only records allowed to it.

SELECT ALLOWED
RepositoryAdditionalInformation.Link
FROM
Directory.Storage of Additional Information

VARIOUS

The use of "DIFFERENT" will make it possible to exclude duplicate rows from entering the result of a 1C query. Duplication means that all fields of the request match.

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

EmptyTable

This construction is used very rarely 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 right for this.

Example from help 1C 8:

CHOOSE Reference.Number, EMPTYTABLE.(Nom, Tov, Qty) AS COMPOSITION
FROM Document.Invoice
UNITE ALL
SELECT Link.Number, Composition.(LineNumber, Product, Quantity)
FROM Document.Invoice Document.Invoice.Composition.*

ISNULL

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

CHOOSE
NomenclatureRef.Reference,
IsNULL(Product Remaining.QuantityRemaining,0) AS QuantityRemaining
FROM

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

ISNULL(InvoiceInvoiceReceived.Date, InvoiceIssued.Date)

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

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

CHOOSE
View(Link), //string, for example "Advance report No. 123 dated 10/10/2015
Representation(DeletionMark) AS DeleteMarkText, //string, "Yes" or "No"
ReferenceRepresentation(DeletionMark) AS DeletionMarkBoolean //boolean, True or False
FROM
Document.AdvanceReport

EXPRESS

Express allows you to convert the 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 the fields of a composite type, often used to optimize system performance. Example:

EXPRESS(Table of Costs.Subconto1 AS Directory.Cost Items).Type of ActivityFor Tax Accounting of Costs

For primitive types, this function is often used to limit the number of characters in fields of unlimited length (cannot be compared against such fields). To avoid the error " Invalid parameters in compare operation. Can't compare fields
unlimited length and fields of incompatible types”, it is necessary to express such fields as follows:

EXPRESS(Comment AS String(150))

DATE DIFFERENCE

Get 267 1C video lessons for free:

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

CHOOSE FROM
Ref
LEFT JOIN
Software
WHERE NOT Remains of Goods. Quantity Remains IS NULL

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

Predefined values

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

Usage example:

WHERE

WHERE Counterparties.KindofContactInformation = Value(Enumeration.Types ofContactInformation.Phone)

WHERE Account Balances.Accounting Account = Value(Chart of Accounts.Self-supporting.Profit-Loss)

Connections

Connections are of 4 types: LEFT, RIGHT, COMPLETE, INTERNAL.

LEFT and RIGHT JOIN

Joins are used to link two tables by a certain condition. Feature at LEFT JOIN in that we take the first specified table completely and bind the second table by condition. The fields of the second table that could not be linked by condition are filled with the value NULL.

For example:

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

RIGHT JOIN in 1C language absolutely similar LEFT join except for one difference - in RIGHT JOIN the “master” table is the second, not the first.

FULL CONNECTION

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

For example:

FROM

FULL CONNECTION
Directory. Banks AS Banks

BY

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

INNER JOIN

INNER JOIN differs from the full one in that it displays only those records that could be connected according to a given condition.

For example:

FROM
Directory. Counterparties AS Clients

INNER JOIN
Directory. Banks AS Banks

BY
Clients.Name = Banks.Name

This query will return only rows where the bank and counterparty have the same name.

Associations

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

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

How to use INDEX BY

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

CHOOSE
CurrenciesCurrencySliceLast.Currency AS Currency,
CurrenciesCurrencySliceLast.Course
PUT Currency Rates
FROM
DataRegister.Currency Rates.SliceLast(&Period,) AS Currency RatesSliceLast
INDEX BY
Currency
;
CHOOSE
PricesNomenclature.Nomenclature,
PricesNomenclature.Price,
PricesNomenclatures.Currency,
RatesCurrency.Course
FROM
RegisterInformation.PricesNomenclature.SliceLast(&Period,
Item B (&Nomenclature) AND PriceType = &PriceType) AS Item Prices
LEFT JOIN Currencies Rates AS Currencies Rates
Software PricesNomenclature.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.

There are the following functions:

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

Example #1:

CHOOSE
Realization of Goods, Services, Goods.Nomenclature,
SUM(Sale of Goods, Services, Goods. Quantity) AS Quantity,
SUM(Sale of Goods, Services, Goods. Amount) AS Sum
FROM

GROUP BY
Realization of Goods, Services, Goods. Nomenclature

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

Example #2

CHOOSE
Banks.Code,
NUMBER(DIFFERENT Banks.Reference) AS Number ofDuplicates
FROM
Directory. Banks AS Banks
GROUP BY
Banks.Code

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

Results

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

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

CHOOSE

FROM
Document.Sale of GoodsServices.Goods AS Realization of GoodsServicesGoods
SORT BY

RESULTS
SUM(Number),
SUM(Amount)
BY
Nomenclature

The query will result in the following hierarchical :

General results

If you need to get totals for all "totals", use the "TOTAL" operator.

CHOOSE
Realization of Goods, Services, Goods. Nomenclature AS Nomenclature,
Realization Goods Services Goods. Reference AS Document,
Sales of Goods, Services, Goods. Quantity AS Quantity,
Realization of Goods of Services Goods. Amount AS Amount
FROM
Document.Sale of GoodsServices.Goods AS Realization of GoodsServicesGoods
SORT BY
Realization of Goods and Services Goods. Reference. Date
RESULTS
SUM(Number),
SUM(Amount)
BY
ARE COMMON,
Nomenclature

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

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

ordering

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

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

CHOOSE

FROM
Directory. Nomenclature AS Nomenclature
SORT BY
Name

The query will display a list of names of the nomenclature reference book, sorted alphabetically.

Auto-arranging

The result of an unsorted query is a randomly represented set of rows. The developers of the 1C platform do not guarantee the output of lines in the same sequence when executing the same queries.

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

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

Virtual Tables

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

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

cut first;
slice of the latter.

leftovers;
turnovers;
balances and turnovers.

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

base;
graph data;
actual validity period.

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

CHOOSE
GoodsIn WarehousesRemainsAnd Turnovers.Nomenclature,
GoodsIn WarehousesRemainsAnd Turnovers.Quantity
GoodsIn WarehousesRemainsAndTurnovers.QuantityTurnover,
GoodsIn WarehousesRemainsAnd Turnovers.QuantityIncoming,
GoodsIn WarehousesRemainsAnd Turnovers.QuantityConsumption
GoodsIn WarehousesRemainders and Turnovers. Quantity
FROM
Accumulation Register. Goods In Warehouses. Remains And Turnovers AS Goods In Warehouses Remains And Turnovers

Such a query allows you to quickly get a large amount of data.

Virtual Table Options

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

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

An example of using such parameters:

Accumulation Register.GoodsInWarehouses.RemainsAndTurnovers(&StartPeriod, &EndPeriod, Month, Movements ANDPeriodBorders, Nomenclature = &NecessaryNomenclature)

Algorithm for virtual tables

For example, the most used virtual table of the "Remainders" type 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 nearest by 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 Builder- a tool built into the 1C Enterprise system, which greatly facilitates the development of database queries.

The query builder has a fairly simple, intuitive interface. Nevertheless, let's consider the use of the query constructor in more detail.

The query text constructor is launched by the context menu (right-click) in the right place in the program code.

Description of the 1C query constructor

Let's consider each tab of the designer in more detail. The exception is the Builder tab, this is a topic for a separate discussion.

Tables and fields tab

This tab specifies the data source and fields to display in the report. As a matter of fact here constructions SELECT. FROM are described.

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

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

Links tab

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

Grouping tab

On this tab, the system allows you to group and summarize the desired fields of the table result. The use of the GROUP BY, SUM, MINIMUM, AVERAGE, MAXIMUM, NUMBER, NUMBER OF DIFFERENT structures is described.

Conditions tab

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

Advanced tab

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

grouping Selecting Records:

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

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 Lock received data for later modification. It allows you to enable the ability to set a data lock, which ensures the safety of data from the moment they are read to modified (relevant only for the Automatic lock mode, construction FOR CHANGE).

Joins/Aliases tab

On this tab of the query designer, you can set the ability to join different tables and aliases (the AS construct). Tables are listed on the left side. If you set the flags in front of the table, the JOIN construction will be used, otherwise - JOIN ALL (differences between the two methods). On the right side, field correspondences in different tables are indicated; if a correspondence is not specified, the query will return NULL.

Order tab

Here you specify the sort order of values (ORDER BY) - descending (DESC) or ascending (ASC).

There is also an interesting flag - Auto-arranging(in the query - AUTOORDER). 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 Batch tab

You can create new ones on the Query Design tab and also use it as a navigation. In the text of the request, the packets are separated by the symbol “;” (semicolon).

Query button in query builder

There is a Request button in the lower left corner of the query builder, with which you can view the text of the query at any time:

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

Using the Query Console

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

Let's take a closer look at this tool.

Download request console 1C

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

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

Description of the 1C query console

Let's start our consideration of 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 an accuracy of milliseconds, this allows you to compare different designs in terms of performance.

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

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

Runrequest– simple execution and getting results
Execute package- allows you to view all intermediate requests in a batch of requests
Viewing temporary tables- allows you to see the results that temporary queries return in a table

Request parameters:

Allows you to set the current parameters for the request.

In the query parameters window, the following is interesting:

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

Set a parameter as a list of values very simple, it is enough to click on the value clear button (cross) when choosing a parameter value, the system will prompt you to select the data type, where you need to select “Value list”:

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

Here you can specify query autosave options and query execution options.

The request text is entered in the console request field. This can be done with a simple query test set or by calling a special tool - the query builder.

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 cleaning or adding line break characters (“|”) to the request or getting the request code in this convenient form:

Request = New Request;
Query.Text = ”
|CHOOSE
| Currencies.Link
| FROM
| Handbook. Currencies AS Currencies”;
QueryResult = Query.Execute();

The lower field of the query console displays the query result field, 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.

Query Optimization

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

Selections in a virtual table using the WHERE construct

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

RIGHT:

CHOOSE

FROM
Accumulation register.Mutual settlements withDepositors ofOrganizations.Balance(
,
Organization = &Organization
AND Individual = &Individual) HOW Mutual settlements withDepositorsOrganizationsBalances

WRONG:

CHOOSE
Mutual settlements withDepositorsOrganizationsBalances.AmountBalance
FROM
Accumulation Register. Mutual Settlements with Depositors of Organizations. Balances(,)
WHERE
Mutual settlements withDepositorsOrganizationsBalances.Organization = &Organization
AND Mutual settlements withDepositorsOrganizationsBalances.Individual = &Individual

Getting the value of a field of a composite type through a dot

When retrieving data of a composite type in a dotted query, the system left-joins 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 field of the register entry - registrar. The registrar has a composite data type, among which are all possible types of documents that can write data to the register.

WRONG:

CHOOSE
RecordSet.Registrator.Date,
RecordSet.Number
FROM
Accumulation Register.GoodsOrganization AS A Set Of Records

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

RIGHT:

CHOOSE
CHOICE
WHEN GoodsOrg.Registrar LINK Document.Sale of GoodsServices
THEN EXPRESS(GoodsOrg.Registrar AS Document.Sale of GoodsServices).Date
WHEN GoodsOrg.Registrar LINK Document.Receipt of GoodsServices
THEN EXPRESS(Goods Org. Registrar AS Document. Receipt of Goods/Services). Date
END AS Date,
GoodsOrg.Quantity
FROM
RegisterAccumulation.GoodsOrganizations AS GoodsOrg

Or the second option - adding such information to the props, for example, in our case - adding a date.

RIGHT:

CHOOSE
GoodsOrganizations.Date,
GoodsOrganizations.Quantity
FROM
RegisterAccumulation.GoodsOrganizations AS GoodsOrganizations

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.

WRONG:

CHOOSE …

LEFT JOIN (
SELECT FROM Register of information.Limits
WHERE …
GROUP BY…
) BY …

RIGHT:

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

CHOOSE …
FROM Document.Realization of GoodsServices
LEFT JOIN Limits
BY …;

Linking Records to Virtual Tables

There are situations when, when connecting a virtual table to others, the system does not work optimally. In this case, to optimize the performance of the query, you can try to place the virtual table in a temporary table, remembering to index the joined fields in the query of the temporary table. This is due to the fact that VTs are often contained in several physical tables of the DBMS, as a result, a subquery is compiled for their selection, and the problem is similar to the previous paragraph.

Using filters on non-indexed fields

One of the most common mistakes when compiling queries is the use of conditions on non-indexed fields, this contradicts query optimization rules. The DBMS cannot perform the query optimally if the query is filtered by non-indexed fields. If a temporary table is taken, it is also necessary to index the connection fields.

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

The index contains all the fields listed in the condition.
These fields are at the very beginning of the index.
These selections go in a row, that is, values that are not participating in the query condition do not “wedged” between them.

If the DBMS does not pick up the correct indexes, then the entire table will be scanned - this will have a very negative impact on performance and can lead to a long lock on the entire recordset.