1c express query language

So, let's begin. A request is a special object in 1C 8.2, which is used to generate and execute queries to database tables in the system. To execute a query, you need to compose a query text that describes which tables will be used as query data sources, which fields to select, which sorts and groupings to apply, etc. You can read more about queries in the book "1C 8.2 Developer's Guide". The 1C 8.2 query language is very similar in syntax to other SQL database query languages, but there are differences. Among the main advantages of the built-in query language, it is worth noting the dereferencing of fields, the presence of virtual tables, convenient work with totals, and untyped fields in queries. Of the disadvantages - you cannot use a query as an output field, you cannot use stored procedures, you cannot convert a string to a number.

1. To increase the readability of the query and reduce the number of query parameters, you can apply in the query access to predefined configuration data using the literal VALUE (VALUE REPRESENTATION). As a representation of values, the value of enumerations, predefined data of reference books, plans of calculation types, plans of types of characteristics, charts of accounts, empty references, values of waypoints, values of system enumerations (for example, AccumulationMotionType,AccountType) can be used.

WHERE City = VALUE(Directory.Cities.Moscow)

WHERE City = VALUE(Reference.City.EmptyReference)

WHEREItemType = VALUE(Enumeration.ProductTypes.Service)

WHEREMovementType = VALUE(MovementTypeAccumulation.Income)

WHERERoutePoint =

VALUE(BusinessProcess.Consent.RoutePoint.Consent)

A parenthesized expression always starts with a singular word (Directory, Enumeration, etc.) that matches the type of the predefined value.

2.Auto-ordering in a query can slow down the process a lot. If sorting is not needed, it is better not to use it at all. In many cases it is more efficient to write sorting with the keyword SORT BY.

3. Care must be taken not to create an ambiguous field when using aliases. Otherwise, the system will not understand which object should be accessed.

An example of a query with an ambiguous field:

CHOOSE

Remaining Goods Remaining. Quantity Remaining

Directory. Nomenclature AS Nomenclature

LEFT JOIN

Software Remains of Goods Remains.

It is necessary to correct the table alias, for example, as follows: “Directory.Nomenclature AS Nomenclature1”, and “Nomenclature.Reference”, respectively, correct it to “Nomenclature1.Reference”.

4.Sometimes it is useful to get the representation of referenced fields with a keyword PERFORMANCE along with a link so that there is no re-access to the database. This is useful when displaying the result of a query in a table.

CHOOSE

REPRESENTATION(Document.Contractor) AS Receiver,

REPRESENTATION(Document.Base)

Document.Invoice AS Document

5.Usage in request EXPRESS(Field AS Type) allows you to remove unnecessary tables from the connection with the field of the composite data type. This will speed up the execution of the request.

Example (the registrar is a field with a compound type for the physical table of the accumulation register of the Remains of Goods, the Date and Number of the documents of the Receipt of Goods are selected in the request, while accessing the details of the document Date and Number through the Registrar, there is no multiple connection of the register table with the tables of documents that are registrars for the Remains of Goods register ):

CHOOSE DIFFERENT
EXPRESS(Remains of Goods.Registrar AS Document.Receipt of Goods).Number AS INCOME NUMBER,

EXPRESS(Remains of Goods.Registrar AS Document.Receipt of Goods).Date AS DATE OF RECEIPT

FROM
Accumulation Register.Remains of Goods AS Remains of Goods
WHERE
(EXPRESS(Remains of Goods.Registrar AS Document.Incoming Goods) IS NOT NULL)

6. When there are users in the 1C configuration whose rights are limited to certain configuration objects, the keyword must be used in a request to such objects ALLOWED so that the query completes without error (Select Allowed...)

7. When joining tables containing nested tables (for example, a Document with a tabular section), the keyword is useful EMPTYTABLE when, for example, one of the documents does not have a tabular section.

UNITE ALL

FROM Document.Invoice

8. When working with joins of tables containing one row each, it may be necessary to glue the rows of tables (at the same time, both tables do not have such a field by which they could be joined). This can be achieved by using the " FULL JOIN Table By TRUE". If the tables have more than one row, then the result will be the number of rows equal to the product of the number of rows in both tables. If there are O rows in one table, then the number of rows in the resulting table will be equal to the number of rows in the second table. Also, to join such tables, you can use the Cartesian product of tables, in which the resulting table will contain all combinations of rows from both tables. We must remember that if one of the tables has 0 rows, then the Cartesian product will also be 0, so a full join will be better. In general, instead of a full connection ON TRUE you can use any other type of join, but in this case it is also possible that the resulting table will have 0 rows, even if one of the tables has a non-zero number of rows. In the case of a full join, this situation will occur only in one case, if the number of rows in both tables is 0. If you know that there is exactly at least one row in the table, then you can use LEFT JOIN with another table with condition ON TRUE.

An example (albeit contrived, for a Full connection):

CHOOSE

K.Contractor

Enum.Gender AS Gender

FULL JOIN (Select First 1 D.Contractor FROM Document.Sale of Goods HOW TO Order By D.Point In Time) HOW TO

ON (TRUE)

9. In order to get unique records for some field, it is more correct to use a keyword instead of grouping VARIOUS in the query, because such a construction is much clearer and the keyword GROUP BY has a wider application and is often used if it is additionally necessary to calculate aggregate functions by groupings. In some cases, it is necessary to display a limited number of lines. To do this, in the description of the request in the description of the request, you must specify the keyword FIRST and after it - the required number of lines.

Example for FIRST:

Select First 5

Directory. Nomenclature. Name,

Directory.Nomenclature.PurchasePrice

Sort by

Directory.Nomenclature.PurchasingPrice Desc

Example for VARIOUS:

Select Various

Document.Expenditure.Counterparty

10.Aggregate functions in a query can be used without the keyword GROUP. In this case, all results will be grouped into one line.

Choose

Amount(Invoice.Amount) As Amount

Document.Invoice.Composition As Invoice

11.In queries in the selection fields, you can freely access the details of the selection fields. This feature is called dereferencing selection fields. If the data source is a nested table (tabular part of the document), then in the selection fields you can also refer to the fields of the main table (for example, through the Link field, access the field of the main table Counterparty)

CHOOSE

Receipt of Goods and Services Goods. Quantity AS Quantity,
Receipt of Goods and Services Goods. Link. Counterparty
FROM

WHERE
Receipt of Goods and Services Goods. Link = & Link

There is one peculiarity of using field dereferencing if there are groupings in the request. In any queries with groupings in the lists of query fields, you can freely access the details of the grouping fields.

CHOOSE

Receipt of Goods and Services Goods. Nomenclature,

Receipt of Goods and Services Goods.Nomenclature.Code,

SUM(Incoming Goods & Services Goods. Quantity) AS Quantity,

Document. Receipt of Goods and Services. Goods AS Receipt of Goods and Services Goods

GROUP BY

Receipt of Goods and Services Goods. Nomenclature,

The 1C help says that if there is grouping, only grouping fields and aggregate functions by selection fields can participate in the query selection fields. There is one exception when aggregate functions are applied to the fields of a nested table. In this case, in the list of selection fields, it is possible to refer to the fields of the top-level table, without grouping the results by these fields.

CHOOSE

Receipt of Goods and Services. Goods. (SUM (Quantity), Nomenclature),

Receipt of Goods and Services. Counterparty

Document. Receipt of Goods and Services AS Receipt of Goods and Services

GROUP BY

Receipt of Goods and Services. Goods. (Nomenclature)

12. Sometimes, instead of specifying a field in a grouping, it is useful to include a parameter in the query selection fields:

CHOOSE
DocGoods.Nomenclature,
&Contractor,
&Period,
SUM(DocProducts.Quantity * DocProducts.K) AS Quantity,
SUM(DocProducts.Sum) AS Sum
FROM
Document.Receipt.Goods AS DocGoods
WHERE
DocProducts.Link = &Link

GROUP BY
DocProducts.Nomenclature

And then set the parameter in the request body:

Query.SetParameter("&Account", SelectAccount);

Query.SetParameter("&Period", Date);

13. In universal queries, parameters can be used in the description of query data sources, in conditions WHERE, in table join conditions and virtual table settings. There are two techniques for creating generic queries:

A) using the string concatenation mechanism, adding variables to the query text;

OrderType = ?(SOMEVARIABLE,"","DESC");

Query.Text = "Select... Order Software Field1 " + OrderingType + "...";

Query.Text = "Select Field1...";

If SOMEVARIABLE = 1 Then

Request.Text = Request.Text + ",Field2 ...";

EndIf;

B) use parameters in various parts of the query (for example, in the data sources section of the query), and then the built-in language method - STREPLACE(). When designing generic queries, it is useful to refer to a property of objects METADATA(), with which you can determine the name of the table for some link (for example, for a document it will be something like this - Link . METADATA().NAME) passed through a parameter to some universal procedure.

Choose

Doc.Nomenclature,

&SomeDocTC AS DocTC

And then set the parameter in the request body

Query.Text = StrReplace(Query.Text, "&SomeDocTCH", "Document."+Link.Metadata().Name+".Goods");

Parameters can be used in query conditions to include an optional condition &Parameter OR NOT SomeProperty:

Query.SetParameter(“&Parameter”, “Account.Name=””Ivanov”””);

With a literal TRUE you can remove certain filters in the query

Query.SetParameter("&Parameter", True);

14.Very useful in the query designer is the table context menu command - " Rename table...", with which you can come up with some generic name for the data source. To create queries to tables of the same type that are similar in structure, it is useful for the second table to copy the query text of the first table, go to the query designer window and select the item in the context menu of the table - Replace table... and select the second table.

15. When working with the creation of nested queries in the conditions or parameters of virtual tables of the query designer, the technique of highlighting a space in brackets is used, then the “Query Builder” item appears in the context menu, and when editing a nested query, the entire query in brackets is selected in the condition.

Subquery example:

Item B (Select Nomenclature...)

16. When designing ACS reports in queries to balance registers, it is more convenient and correct to use the expression as the Period parameter AddKDate(EndPeriod(Period,DAY),SECOND,1), since the virtual balances are obtained at the beginning of the period, not including the last second. Reception +1 second cannot be used with documents: according to the new method of posting documents, register balances must be received for the Period specified by the Boundary object with the document time including (and not on the date of the document +1 second!), but according to the old method of posting - at the time of the document (and not at the date of the document!). When analyzing turnovers or data for a period, it is convenient to add a parameter with the type standard period(in this case it is not necessary to give the last date of the interval at the end of the day). In the standard field "StartPeriod" in the field "Expression" you need to write "&Period.StartDate". And for the standard field "EndPeriod" in the field "Expression" write " &Period.EndDate". A lot of useful information on the query language can be found not in the syntax assistant, but in the full help of the 1C 8.2 configurator (F1 button)

17.Query function Null(it is more convenient to write the English version IsNull) is commonly used to get rid of Null values for numeric query fields. In some cases, for example, a complete join of two tables, the function IsNull(Parameter1,Parameter2) can successfully replace the design CHOICE WHEN... THEN..ELSE....END, when for some field NULL values can be both in the first table and in the second (this construction allows you to get a non-Null value for the field). But we must remember that, unlike the conditional operator CHOICE function Null casts the type of the second argument to the type of the first argument, which must be taken into account if the types of the arguments are different!

IsNull(Reg.Remainder,0)

IsNull(Doc.Item,Doc1.Nomenclature)

18. Conditional construction CHOICE there is an alternative syntax for the simple case of checking for equality against a certain value, but it is undocumented though:

Choice Expression When 1 Then "Highest" When 2 Then "Medium" Else "Lower" End

19.NULL test operator Null(It is recommended to use the English version Is Null). This construction appeared because any comparison operation of two values, at least one of which is Null, is always false. Write Where Name = Null wrong. The negation form of this operator is also interesting Not Null- wrong but right Is Not Null or form Not (Field1 Is Null)- this is a significant difference from all operators used in conjunction with the He operator.

20. Operator form is sometimes useful IN to check for a match with one of the listed values.

...Where is the Product.Description B ("Home Appliances","Computers")

For directories, the operator form can be useful IN hierarchical membership checks.

...Where is the Nomenclature IN THE HIERARCHY (&Group)

Operator IN often used to check if a value is included in the result of a subquery.

In a nested query, you can access the fields of the outer query in a condition.

// Select the names of the products that were present

// in invoices

CHOOSE

Products.Name

Directory. Nomenclature AS Goods

(CHOOSE

Delivery noteComposition.Nomenclature

Document.Invoice.Composition AS InvoiceContent

InvoiceContent.Nomenclature = Goods.Reference)

Operation IN can be used with arrays, lists of values, tables of values, nested queries. In this case, it is possible to reduce the conditions

Syntax for subquery

(expression1, expression2,...,expressionN) To (Select expression1, expression2,...,expressionN ...)

Syntax for value table

(expression1, expression2,...,expressionN) In (&TK), where the first N columns are used in the table of TK values

20. There's a joke on the internet about how the query builder keeps doing LEFT joining tables (and swapping them), no matter how we specify RIGHT:

1C: The enterprise likes "to the left".

21. It is convenient to debug complex queries in the query console. There are many of them on the Internet. After debugging a query, it can be copied and there is a wonderful button in the query constructor " Request”, where you can paste it in the same form and save it (previously it was only possible to copy it in the configurator and format the request using a line break character). In the window that opens when you click the "Request" button, you can edit the request and view the result of execution, which is quite convenient.

22. When designing ACS reports, you need to remember that if you need to provide filtering by a certain field, it is not necessary to add a parameter to the request text. The Query Builder has a tab " Data layout”, where you can add parameters to the conditions. In addition, at the ACS report level there is a conditions tab where you can add arbitrary conditions and save them in quick settings. In this case, the conditions will be universal (equality, inequality, membership, inclusion in the list, etc.).

23. When working with documents, it may be necessary to add sorting by a virtual table field MOMENT OF TIME, but that's bad luck - in nested queries, sorting by this field does not work correctly. Dancing with tambourines helps: sorting by virtual field MOMENT OF TIME is replaced by two sorts: by date and by reference. You can also solve the problem through a temporary table by transferring the nested query to a separate query. For many releases, this feature or bug has not been fixed.

An example of an incorrectly working query that receives the last posted document for the specified counterparty (or rather, the tabular part of the document):

CHOOSE

ConsumablesGoods.LineNumber,

Consumables Goods. Goods,

Consumable Goods. Quantity,

Consumable Goods. Price,

ConsumablesGoods.Amount

Document.Expenditure AS D

Possible solutions:

A) Replace with SORT BY on

ORDER BY D.DATE DESC

B) You can move the nested query to a temporary table:

Document.Expenditure AS D

AND D.Contractor = &Contractor

////////////////////////////////////////////////////////////////////////////////

CHOOSE

ConsumablesGoods.LineNumber,

Consumables Goods. Goods,

Consumable Goods. Quantity,

Consumable Goods. Price,

ConsumablesGoods.Amount

Document.Consumable.Goods AS ConsumableGoods

C) You can refer to the main table of the document, and only then to the tabular part

SELECT FIRST 1

line number,

Product,

Quantity,

Price,

Sum

Document.Expenditure AS Expenditure

Consumable.Contractor = &Contractor

And Consumable. Carried out

SORT BY

Consumable. Moment of Time DECREASING

24. When referring to the main table of the document (reference book), you can refer to the data of the subordinate table (tabular section) in the condition. This possibility is called dereferencing tabular fields. As an example of a task, we can cite the task of searching for documents containing a certain product in the tabular section.

The advantage of this query over the query on the nested table Receipt.Products is that if there are duplicates in documents, the query result will return only unique documents without using the keyword VARIOUS.

Compare:

This place is probably everything. It is clear that there are still many questions in the query language that I have not covered. To write the article, I used the information that I received after completing the basic course 1C 8.2 spec8.ru, as well as from the book "1C 8.2 Developer's Guide" and the Internet.

The query language is one of the fundamental mechanisms of 1C 8.3 for developers. With the help of queries, you can quickly get 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 (turning one or more dots to object attributes);
work with the results is very convenient;
the ability to create virtual tables;
the request can be written both in English and in Russian;
the ability to block data to avoid deadlocks.

Disadvantages of the query language in 1C:

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

Consider our mini tutorial on the basic constructions of the 1C query language.

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

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

CHOOSE
Nomenclature.Name AS NameNomenclature
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 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 the latest documents by date.
ALLOWED. This design allows you to select from the database only those records that are available to the current user. If this keyword is used, the user will receive an error message if they try to query records they do not have access to.

These keywords can be used all together or separately.

FOR CHANGE

This clause locks data to avoid conflicts. Locked data will not be read from another connection until the end of the transaction. In this clause, you can specify specific tables that you want to lock. Otherwise, all will be blocked. The design is relevant only for the automatic blocking mode.

Most often, the "FOR CHANGE" clause is used when receiving balances. Indeed, when several users work in the program at the same time, while one receives the balances, the other can change them. In this case, the resulting balance 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 have to wait.

CHOOSE
Mutual settlements. Employee,
Mutual settlements. Amount Mutual settlements Balance
FROM
Accumulation Register. Mutual Settlements WITH Employees. Balances AS Mutual Settlements
FOR CHANGE

WHERE

The construction is necessary for imposing any selection on the unloaded data. In some cases of obtaining data from registers, it is more reasonable to prescribe selection conditions in the parameters of virtual tables. When using "WHERE", all records are obtained first, and only then the selection is applied, which significantly slows down the query.

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

SELECTION (CASE)

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

In the example below, the "Additional Field" will contain text depending on whether the document is posted or not:

CHOOSE
AdmissionT&U.Link,
CHOICE
WHEN
THEN "Document posted!"
ELSE "Document not posted..."
END AS AdditionalField
FROM
Document.Receipt of GoodsServices AS ReceiptT&C

JOIN

Joins link two tables by a certain link condition.

LEFT/RIGHT JOIN

The essence of the LEFT join is that the first specified table is taken completely and the second one is attached to it by the condition of the connection. 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, you need to get item items from the documents "Receipt of goods and services" and prices from the information register "Item prices". In this case, if the price of 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 of T&U. Nomenclature,
Prices.Price
FROM
Document.Receipt of GoodsServices.Goods AS ReceiptT&C
INNER JOIN
ON Receipt of Q&A.Nomenclature = Prices.Nomenclature

In RIGHT, everything is exactly the opposite.

FULL CONNECTION

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

When using the full join in the previous example, all item items from the Goods and Services Receipt document and all the latest prices from the Item Prices register will be selected. The values of not found records, both in the first and in the second table, will be 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, then the query will not display it at all. As a result, only those item items from the Goods and Services Receipt document will be selected for which there are entries in the Item Prices information register, 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 feature (grouping fields). Grouping fields can only be displayed using aggregate functions.

The result of the next query will be a list of item types with their maximum prices.

CHOOSE
,
MAX(Price.Price) AS Price
FROM

GROUP BY
Prices.Nomenclature.TypeNomenclature

RESULTS

Unlike grouping, when using totals, all records are displayed and total rows are already 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 summing up, it is not necessary to use aggregate functions.

Consider 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
RegisterInformation.PricesNomenclature.SliceLast AS Prices
RESULTS
MAXIMUM(Price)
BY
Type Nomenclature

HAVING

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

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

CHOOSE

MAX(Price.Price) AS Price
FROM
RegisterInformation.PricesNomenclature.SliceLast AS Prices
GROUP BY
Prices.Nomenclature.TypeNomenclature
HAVING
MAX(Prices.Price) > 1000

SORT BY

The "ORDER BY" operator sorts the query result. To ensure that records are output 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 AS Employees
SORT BY
Name
AUTO ORDER

Other constructions of the 1C query language

UNITE- the results of two queries in one.
UNITE ALL– similar to JOIN, but without grouping identical rows.
EMPTY TABLE- sometimes used when joining queries to specify an empty nested table.
PUT- 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 by the specified number of characters.
YEAR…SECOND allow you to get the selected value of the numeric type. The input parameter is a date.
BEGINNING OF THE PERIOD AND END OF THE PERIOD are used when working with dates. The period type (DAY, MONTH, YEAR, etc.) is specified as an additional parameter.
ADDDATE allows you to add or subtract from the date the specified time of a certain type (SECOND, MINUTE, DAY, etc.).
DATE DIFFERENCE determines the difference between two dates, specifying the type of output value (DAY, YEAR, MONTH, etc.).
ISNULL replaces the missing value with the specified expression.
PRESENTATION and PRESENTATIONLINKS get the string representation of the specified field. They are used for any values and only reference values, respectively.
TYPE, VALUE TYPE are used to determine the type of the input parameter.
LINK is a logical comparison operator for the attribute value type.
EXPRESS is used to convert the value to the desired type.
DATE TIME gets a value of type "Date" from numeric values (Year, Month, Day, Hour, Minute, Second).
MEANING in a 1C request, it is used to specify predefined values \u200b\u200b- directories, enumerations, plans for types of characteristics. Usage example: " Where LegalIndividual = Value(Enumeration.LegalIndividual.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 to be selected and their sources.
"Links" - describes the conditions for the CONNECTION construct.
"Grouping" - contains a description of the constructions of groupings and summarized fields by them.
"Conditions" - is responsible for the selection of data in the request.
"Advanced" - additional query parameters, such as the keywords of the "SELECT" command, etc.
“Joins / Aliases” - the possibilities of joining tables are indicated and aliases are set (the “HOW” construct).
"Order" - is responsible for sorting the result of queries.
"Totals" - similar to the "Grouping" tab, but is used for the "TOTALS" construction.

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.

Query Console

To quickly view the result of a query in the "Enterprise" mode, or to debug complex queries, use . The query text is written in it, parameters are set, and its result is shown.

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

Attention! Here is a trial version of the lesson, the materials of which may not be complete.

1C 8.3 query language for beginner programmers: functions and operators for working with types (VALUE TYPE, TYPE, REFERENCE, IS NULL, EXPRESS)

Let's remember that each attribute (property, field) of a reference book, document or any other application object has its own type . And we can see this type in the configurator:

The query language has a whole class of functions and operators for working with attribute types. Let's take a look at them.

VALUETYPE function

This function takes one parameter (value) and returns its type. For the props described in the picture (above) Taste directory Food will return the following:

Now let's take a look at the props. Distinguishing Feature at the directory Cities:

You can see that this prop can be one of several types: Line, Directory. Tastes, Reference.Colors. This type of details is called COMPOSITE.

If we try to fill in the value of such an attribute in 1C:Enterprise mode, the system will ask us what type the input value will be:

And only after our choice will allow you to enter the value of the selected type.

Thus, directory elements of the same type ( Directory.Cities) will be able to store in the same attribute ( Distinguishing Feature) values of different types (String, Colors or Flavors).

You can verify this for yourself by clicking on the elements of the directory Cities in 1C:Enterprise mode. You are reading a trial version of the lesson, full lessons are located.

Here the feature value is a reference element Tastes:

Here the line:

And here, in general, an element of the directory Colors:

These are the possibilities that a composite data type opens up for us!

I wonder how the function will behave VALUETYPE on props DistinctiveElement, which has a composite data type:

This is already very interesting. Let's deal with each line separately.

The feature value type for the Russia element is NULL. This is the first time we have encountered this type. Values of this type are used solely to determine the missing value when working with the database.

It is, because the element Russia is a group, and not an ordinary element of the directory Cities, so it has no field Distinguishing Feature. And the type of the missing value, as we read above, is always equal to NULL.

The value type of the distinguishing feature for Perm is Tastes. So it is, because the value of the distinguishing feature scored in the database for the city of Perm is a link to the directory element Tastes.

For Krasnoyarsk, the feature type is equal to Colors, because the value selected in the database is a reference to a dictionary element Colors.

For Voronezh, the feature type is equal to Line, because the value entered in the database is a normal string.

India is a group again, so the meaning is missing. And the type of the missing value, as we remember, is NULL.

And here's the thing. If you go to the directory element Cities with the name Sao Paulo, you will see that the field Distinguishing Feature completely unfilled. It is empty. A all empty fields of a composite type have a special meaning UNDEFINED .

WITH UNDEFINED we also meet for the first time. Meaning UNDEFINED used when you want to use an empty value that does not belong to any other type. This is just our situation. A value type UNDEFINED, as you probably already guessed, is equal to NULL.

Function TYPE

It takes only one parameter - the name of the primitive type ( LINE, NUMBER, DATE, BOOLEAN), or the name of the table whose reference type you want to get.

The result of this construction will be a value of type Type for the specified type.

Sounds hazy, doesn't it?

Let's look at the application of this design and everything will immediately fall into place.

Suppose we want to select all directory entries Cities, which have a composite attribute Distinguishing Feature has a value of type LINE:

Now let's select all records that have attribute values Distinguishing Feature are references to reference elements Colors(table Reference.Colors):

Retreat

As you remember, some elements of the directory Cities do not have props Distinguishing Feature. Function VALUETYPE for such elements gives NULL.

How it is possible to make selection of such elements in request? There is a special logical operator for this. IS NULL(not to be confused with the function ISNULL which we will discuss below). You are reading a trial version of the lesson, full lessons are located.

Here is an example of its use:

Great. But you noticed that there is no Sao Paulo element here, props value type Distinguishing Feature which was also issued NULL. Why did it happen?

And the thing is that the situation for groups (Russia, India, Brazil), for which filling in the details Distinguishing Feature impossible in principle, since they do not have it at all, differs from the situation for the Sao Paulo element, for which filling in the attribute is possible, but it is simply not filled in and, as we remember, is equal to the special value UNDEFINED.

To select all records that have props Distinguishing Feature present but not filled, another construct should be used:

But comparing with UNDETERMINATED to define empty (not populated) props will only work for composite types.

By the way, the logical operator IS NULL negation form looks like this:

Boolean operator REFERENCE

For example, let's choose from the directory Cities only those records that have the value of a composite attribute Distinguishing Feature are a reference to a reference element Tastes:

As you remember, we could solve the same problem using VALUETYPE And TYPE:

Function ISNULL

The function is intended to replace the value NULL to another value.

We remember that the value NULL is returned if the requested attribute (field, property) does not exist.

Like props Distinguishing Feature for directory groups Cities:

Function ISNULL will help us output another value if this value is equal to NULL. You are reading a trial version of the lesson, full lessons are located. Let in this case it will be the line "There is no such attribute!":

It turns out that if the first parameter of the function ISNULL not equal NULL, then it returns. If it is NULL, then the second parameter is returned.

EXPRESS function

This function is only for fields that have a composite type. A great example of such a field is the property Distinguishing Feature for directory elements Cities.

As we remember, composite fields can be one of several types specified in the configurator.

For field Distinguishing Feature such valid types are LINE, Reference.Colors And Directory. Tastes.

Sometimes it becomes necessary to cast the values of a composite field to a specific type.

Let's list all field values Distinguishing Feature to type Reference.Colors:

As a result, all element values that had the type Reference.Colors, remained filled and turned out to be reduced to the specified type. All values of other types ( LINE, Directory. Tastes) are now equal NULL. This is the peculiarity of type casting using the function EXPRESS.

You can cast a type either to a primitive type ( BOOLEAN, NUMBER, LINE, DATE) or to a reference type. You are reading a trial version of the lesson, full lessons are located. But the type to which the cast is made must be included in the list of types for this composite field, otherwise the system will generate an error.

Take the test

Start test

1. Choose the most correct statement

2. Attributes that can take values of one of several types are called

3. To determine the type of attribute value, the function is suitable

4. Unfilled composite type attributes matter

The EXPRESS function in the 1C 8 query language is interpreted by many as a type converter, but it is not intended for these purposes at all. Details under the cut...

So, many people mistakenly believe that they can convert a field with the type Line in a field with type Number or a link to a string. In fact, the EXPRESS operator can transform:

primitive type settings;
a composite type field in a single type field;

Let's take a closer look at these situations...

Converting Primitive Type Settings

Let's consider a situation where we want to group data by a line of an unrestricted type, for example, such a line is often a comment in documents. In this case, we will not be able to group by comment, we will get an error. Therefore, we need to convert a string of unlimited length into a string with a limited length, and then group. Example, let's count the number of documents with the same comments:

CHOOSE
EXPRESS(Incoming.Comment AS STRING(300)) AS Comment,
QUANTITY(Incoming.Reference) AS Reference
FROM
Document. Entrance AS Entrance

GROUP BY
EXPRESS(Incoming.Comment AS A STRING(300))

Another situation is when calculations are used in the query, at the output we can get a number with a large number of decimal places (1100.001568794) . In order not to process this number after the query is executed, it can immediately be truncated to the desired length, but it is important to understand that the number is truncated, not rounded. Example:

CHOOSE
Sales.Product,
EXPRESS(Sales.Quantity * Sales.Price AS NUMBER(15, 2)) AS Sum
FROM

Converting a Composite Type to a Single Type

The register register often has a composite type, to convert it to a single type use the construction EXPRESS however, if at the sampling stage you try to convert the implementation document into an incoming document, then the request will definitely crash with an error, so you should check the link type before converting. Here is such rubbish))) Why do you need all this, you ask. I answer, this is one of the moments of implicit query optimization to the detriment of the brevity of writing. Let's take a look at this moment with an example.

Let's say you set out to get the number of each registrar from PH Sales. write a query:

CHOOSE DIFFERENT
Sales.Registrar.Number
FROM
Accumulation Register.Sales AS Sales

Actually, nothing could be easier. That's just 1C at the execution stage converts this request without any connections into a request with as many left connections as we have possible registrars. Those. if 20 documents are written to this register, then we get a SQL query with 20 left joins. Why is this happening? Because the built-in 1C optimizer does not process fields received through a dot very well, in this case it is the Number attribute. These are the pies, if we often want to receive the document number, then it is most reasonable to include it in the register details or use the EXPRESS operator, but at the expense of brevity:

CHOOSE DIFFERENT
Sales.Registrar.Number,
CHOICE
WHEN Sales.Registrar LINK Document.Expenditure
THEN EXPRESS(Sales.Registrar AS Document.Expenses)
ELSE CHOICE
WHEN Sales.Registrar LINK Document.Implementation
THEN EXPRESS(Sales.Registrar AS Document.Sales)
END
...
END AS A Number
FROM
Accumulation Register.Sales AS Sales

Now, one particular table will participate in the left join.

In the general case, you should carefully refer to data through a dot, because. 1C in this case uses a left join in the SQL query, which can significantly affect performance. This is one of the points of optimization.

In this article, we will analyze the possibilities of type casting in the 1C query language that the "Express" function provides.
Let's look at a few options for using this feature.
And the first option is rounding numbers.

To do this, use the Express function in the following format:

Express(<Число>as Number(<ДлинаЧисла>,<Точность>))

Where:
Number— field, the parameter to be rounded
LengthNumbers- maximum number length
Accuracy- number rounding accuracy

The parameters and length and precision must be positive integers.
See how this feature works in the figure below.

The second use case is string casting. Very often, strings of unlimited length are used in configurations, which imposes some restrictions. For example, we cannot compare strings of unlimited length.
In the query below, the FullName field is of type unlimited length string, and this query will not work.

In order for it to work, you need to convert a field of unlimited length into a string with a certain length, this is done using the Express in the following format function:

Express(<Строка>as String(<ДлинаСтроки>)

Where
String Length- the maximum length to which the string will be reduced.
Let's remake the query: in the condition we will bring an unlimited string to a string with a certain length. Then there will be no errors.

Let's consider the last and, I would say, the most important variant of its application: when working with fields of a composite type.
Below are two queries that use composite type fields. The first one is wrong and the second one is correct.

Those. when you need to get a field of some composite type, always get the value of this field only after casting the type using the Express function. In this case, the format for documents and directories will be as follows:

Express(<Документ>like document.<ИмяТаблицы>)
Express(<Справочник>like a directory.< ИмяТаблицы >) .

Where
TableName— the name of the object in .

Always use the express function when working with composite types, this greatly optimizes the query.

If you are still “floating” in the constructions of the query language, and even the simplest queries cause difficulties for you, then I recommend you my course “Requests in 1C from beginner to pro”. Where these and many other issues are discussed in more detail.

What is special about this course:
The course is designed for those who are not familiar with the query language in 1C;
The training material is well-organized and easy to learn;
Several dozen lessons;
Useful practical examples;
All lessons are presented in a clear and simple language.

For my readers, 25% discount coupon: hrW0rl9Nnx

I try to release various interesting free articles and video tutorials as often as possible. Therefore, I will be very happy if you support my project by transferring any amount:

You can transfer any amount directly:
Yandex.Money — 410012882996301
Web Money - R955262494655

Join my groups.