Express as a number in the 1s query 8.3. Document.Consumable AS D

Incompatible types "EXPRESS"- this message appears when a 1C 8.2 or 8.3 programmer tries to compare two different types.

The Express() function allows you to convert the value of a 1C request field to a specific type. This may be needed in two cases:

1. Get the required number capacity or the required number of characters of a string value. The number of characters in fields of unlimited type must be limited due to certain features.

Get 267 video lessons on 1C for free:

For example:

EXPRESS(Nomenclature.Comment AS STRING(300))
EXPRESS(Nomenclature.Price AS NUMBER(15, 2)) AS Amount

2. Convert a compound type field to a single type field. This may be needed for purposes. If a typed field contains a value of another type, the system will return NULL, so it is always necessary to additionally set a condition in the “WHERE” section, limiting it with the LINK operator.

For example:

CHOOSE
…
EXPRESS(Sales.Registrar AS Document.Implementation)
…
FROM
…
WHERE Sales.Registrar LINK Document.Implementation

How to avoid mistakes

Errors like "Incompatible types EXPRESS..." can occur when the syntax is used incorrectly.

For example, the construction “EXPRESS(“123″ AS NUMBER(5, 2)) AS SUM” is considered erroneous, since the request cannot be converted from one type to another using standard methods.

If you are starting to learn 1C programming, we recommend our free course (don’t forget

In this article we will analyze the possibilities of type conversion in the 1C query language, which are provided by the “Express” function.
Let's look at several options for using this function.
And the first option is rounding numbers.

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

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

Where:
Number— the field that needs to be rounded
LengthNumbers— maximum number length
Accuracy— number rounding accuracy

The parameters both length and precision must be positive integers.
See how this function works in the image below.

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

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

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

Where
Line Length– the maximum length to which the string will be reduced.
Let's rework the query: in the condition we will convert 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 option for its application: when working with fields of a composite type.
Below are two queries that use composite fields. The first one is wrong and the second one is correct.

Those. When you need to get a field of some complex type, always get the value of this field only after casting the type using the Express function. In this case, the documents and reference books will have the following format:

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

Where
TableName— object name in .

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

If you are still “swimming” in the constructions of the query language, and even the simplest queries cause difficulties for you, then I recommend you my course “Queries 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;
Educational material well-structured and easy to learn;
Several dozen lessons;
Useful practical examples;
All lessons are presented in 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 glad if you support my project by transferring any amount:

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

Join my groups.

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

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

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

Disadvantages of the query language in 1C:

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

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

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

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

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

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

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

These keywords can be used together or separately.

FOR CHANGE

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

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

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

WHERE

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

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

SELECTION (CASE)

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

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

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

JOIN

Joins link two tables based on a specific relationship condition.

LEFT/RIGHT CONNECTION

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

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

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

IN THE RIGHT everything is exactly the opposite.

FULL CONNECTION

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

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

INNER JOIN

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

GROUP BY

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

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

CHOOSE
,
MAX(Price.Price) AS Price
FROM

GROUP BY
Prices.Nomenclature.Type of Nomenclature

RESULTS

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

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

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

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

HAVING

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

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

CHOOSE

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

SORT BY

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

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

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

Other 1C query language constructs

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

Query Language Features

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

Query Builder

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

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

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

Request Console

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

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

NULL is nothing more than the absence of a value. Many people confuse it with the value “0” of type number, an empty reference to an object, or an empty string. Because of this misconception, many mistakes arise.

The NULL value will appear if the request refers to a non-existent field, property, or broken link.

Based on SQL, which does not allow normal equality testing for NULL. Below are two ways to check for NULL in 1C 8.3.

The 1C 8.3 query language function ISNULL() has two input parameters:

expression to be tested;
replacement expression.

If the value being tested is NULL, then this function will return the value of the replacement expression. If the value is other than NULL, the expression being tested will be returned.

Below is an example. It selects all item items of the tabular part of the product from the “Receipt of goods and services” document. Using the left join, each item is assigned the last price from the “Item Prices” information register.

In this case, a situation may arise that for some position there may simply not be a price in the register. In this case, the ISNULL function will return us the usual zero. If you do not use it, then when you try to perform arithmetic operations on the “Price” field with a NULL value, we will receive an error.

CHOOSE

ISNULL(Prices.Price, 0) AS CurrentPrice
FROM

WHERE

THERE IS NULL in the SELECT statement

The equivalent of ISNULL() is ISNULL, which is used in the SELECT statement and checks whether the value is NULL. “IS” in this case implies equality and the query in the previous example would look like this:

CHOOSE
Products.Nomenclature AS Product,
CHOICE
WHEN Prices. Price IS NULL
THEN 0
OTHERWISE Prices.Price
END AS CurrentPrice
FROM
Document. Receipt of Goods and Services. Goods AS Goods
LEFT CONNECTION RegisterInformation.PricesNomenclature.SliceLast AS Prices
Software Products.Nomenclature = Prices.Nomenclature
WHERE
Products.Link = &LinkToDocument

Differences between the function ISNULL() and IS NULL

As you can see from the previous examples, in both cases the request returns the same data. The ISNULL() function is a shorthand version of SELECTION WHEN... IS NULL... END, but it is still preferable for the following reasons:

The ISNULL() function optimizes the query. It is read once, so when checking a complex expression, the request will process faster.
The ISNULL() function shortens the construction, making the query more readable.
When executing the ISNULL() function, the replacement expression is reduced to the type of the expression being tested for string types (string length) and numeric types (bit depth).

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

1C Query Language Functions

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

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

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

Usage example:

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

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

Usage example:

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

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

Syntax: VALUE(<Имя>)

Usage example:

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

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

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

Usage example:

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

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

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

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

Usage example:

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

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

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

Usage example:

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

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

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

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

Usage example:

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

Constructs in the 1C query language

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

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

Usage example:

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

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

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

Usage example:

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

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

Usage example:

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

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

Row pattern options:

% - a sequence containing any number of arbitrary characters.

One arbitrary character.

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

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

Usage example:

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

Syntax: ALLOWED is written after the keyword SELECT

Usage example:

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

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

Syntax: VARIOUS is written after the keyword SELECT

Usage example:

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

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

Usage example:

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

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

Syntax: FIRST<число>

Usage example:

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

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

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

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

Usage example:

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