How to get the Type (type) of a Document in a request? Determine the document type in the 1s request 8.2.

The 1C query language is one of the main differences between versions 7.7 and 8. One of the most important points in learning 1C programming is the query language. In 1C 8.3, queries are the most powerful and effective tool for obtaining data. The query language allows you to obtain information from the database in a convenient way.

The syntax itself is very much reminiscent of classic T-SQL, except that in 1C, using the query language, you can only receive data using the Select construct. The language also supports more complex constructs, for example, (request within a request). Queries in 1C 8 can be written in both Cyrillic and Latin.

In this article I will try to talk about the main keywords in the 1C query language:

choose
allowed
various
express
first
for change
meaning
value type (and REFERENCE operator)
choice
group by
having
ISNULL
Yes NULL
connections - right, left, internal, full.

As well as some small tricks of the 1C language, using which you can optimally construct the request text.

To debug queries in the 1C 8.2 system, a special tool is provided - the query console. You can see the description and download it using the link -.

Let's look at the most important and interesting operators of the 1C query language.

SELECT

In the 1C Enterprise 8 query language, any query begins with a keyword CHOOSE. In the 1C language there are no UPDATE, DELETE, CREATE TABLE, INSERT constructs; these manipulations are performed in object technology. Its purpose is to read data only.

For example:

CHOOSE
Current Directory.Name
FROM
Directory.Nomenclature AS Current Directory

The query will return a table with item names.

Near the structure CHOOSE you can find keywords FOR CHANGE, ALLOWED, VARIOUS, FIRST …

ALLOWED— selects only records from the table that the current user has rights to.

VARIOUS— means that the result will not contain duplicate lines.

SELECTION (CASE)

Very often this design is underestimated by programmers. An example of its use:

Current Directory.Name,

WHEN Current Directory.Service THEN

"Service"

END HOW TO VIEWNomenclature

Directory.Nomenclature AS Current Directory

The example will return a text value in the “Item Type” field - “Product” or “Service”.

WHERE

The design of the 1C query language, which allows you to impose selection on the received data. Please note that the system receives all data from the server, and only then it is selected based on this parameter.

CHOOSE
Directory.Name
FROM
Current Directory.Nomenclature AS Current Directory
WHERE CurrentDirectory.Service = TRUE

In the example, we select records for which the value of the “Service” attribute is set to “True”. In this example, we could get by with the following condition:

"WHERE IS THE SERVICE"

Essentially, we are selecting rows where the expression after the keyword is equal to "True".

You can use direct conditions in expressions:

WHERE Code = "005215"

Using the “VALUE()” operator in the conditions, use access to predefined elements and enumerations in a 1C request:

WHERE Item Type = Value(Enumeration.Item Types.Product)

Time values can be specified as follows:

WHERE Receipt Date > DATETIME(2012,01,01):

Most often, conditions are specified as parameters passed to the request:

Get 267 video lessons on 1C for free:

WHERE NomenclatureGroup= &NomenclatureGroup

A condition can be imposed on the attribute type if it is of a composite type:

If you need to limit selection from a list of values or an array, you can do the following:

WHERE is the Accumulation Register. Registrar B (&List of Documents for Selection)

The condition can also be complex, consisting of several conditions:

WHERE Receipt Date > DATETIME(2012,01,01) AND NomenclatureGroup= &NomenclatureGroup AND NOT Service

GROUP BY

Design of the 1C 8.2 query language used to group the result.

For example:

CHOOSE
Receipt of Goods and Services Goods. Goods,
SUM(Receipt of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Receipt of GoodsServicesGoods.Amount) AS Amount
FROM
Document. Receipt of Goods and Services. Goods HOW Receipt of Goods and Services Goods

GROUP BY
Receipt of GoodsServicesGoods.Goods

This request will summarize all receipts by amount and quantity by item.

Besides the keyword SUM You can use other aggregate functions: QUANTITY, NUMBER OF DIFFERENT, MAXIMUM, MINIMUM, AVERAGE.

HAVING

A design that is often forgotten, but it is very important and useful. It allows you to specify selection in the form of an aggregate function, this cannot be done in the design WHERE.

Example of using HAVING in a 1C request:

CHOOSE
Receipt of Goods and Services Goods. Goods,
SUM(Receipt of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Receipt of GoodsServicesGoods.Amount) AS Amount
FROM
Document. Receipt of Goods and Services. Goods HOW Receipt of Goods and Services Goods

GROUP BY
Receipt of Goods and Services Goods. goods

SUM(Receipt of GoodsServicesGoods.Quantity) > 5

So we will select the number of products that arrived more than 5 pieces.

MEANING()

For example:

WHERE Bank = Value(Directory.Banks.EmptyLink)

WHERE Nomenclature Type = Value(Directory.Nomenclature Types.Product)

WHERE Item Type = Value(Enumeration.Item Types.Service)

TYPE in request

The data type can be checked by using the TYPE() and VALUETYPE() functions or using the logical REFERENCE operator.

EXPRESS()

The Express operator in 1C queries is used to convert data types.

Syntax: EXPRESS(<Выражение>HOW<Тип значения>)

Using it, you can convert string values to date or reference values to string data, and so on.

In practical applications, the Express() operator is very often used to convert fields of unlimited length, because fields of unlimited length cannot be selected, grouped, etc. If such fields are not converted, you will receive an error You cannot compare fields of unlimited length and fields of incompatible types.

CHOOSE
ContactInformation.Object,
EXPRESS(ContactInfo.View AS ROW(150)) AS View
FROM
Register of Information. Contact Information HOW Contact Information

GROUP BY
EXPRESS(ContactInfo.View AS ROW(150)),
ContactInformation.Object

ISNULL (ISNULL)

Quite a useful function of the 1C query language that checks the value in the record, and if it is equal NULL, This allows you to replace it with your own value. Most often used when obtaining virtual tables of balances and turnover in order to hide NULL and put a clear 0 (zero).

ISNULL(Pre-Month Taxes.AppliedFSS Benefit, 0)

Such a function of the 1C query language ISNULL will return zero if there is no value, which will avoid an error.

JOIN

There are 4 types of connections: LEFT, RIGHT, COMPLETE, INTERNAL.

LEFT and RIGHT CONNECTION

Joins are used to link two tables based on a specific condition. Feature when LEFT JOIN is that we take the first specified table in its entirety and conditionally bind the second table. The fields of the second table that could not be bound by condition are filled with the value NULL.

An example of a left join in a 1C request:

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

RIGHT JOIN in 1C 8.3 language absolutely similar LEFT connection, with the exception of one difference: in RIGHT OF CONNECTION The "main" 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 and connects only those that it can connect by condition.

For example:

FULL CONNECTION
Directory.Banks HOW Banks

BY

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

INNER JOIN

INNER JOIN differs from full 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 HOW Banks

BY
Clients.Name = Banks.Name

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

Conclusion

This is only a small part of the syntax from the 1C 8 query language; in the future I will try to consider some points in more detail, show and much more!

Request . Text = "CHOOSE | StorageUnits.Link |FROM | Directory.usStorageUnits HOW touseStorageUnits // Example 1: comparison with an empty boolean value: |WHERE | StorageUnits.AllowSelectionFromReserveZone = False // Example 2. but if this Boolean is defined, then it’s better like this: // condition for a negative Boolean: |WHERE | NOT Storage Units. Allow Selection From Reserve Zone // Example 3. selection based on the condition of an empty field that has the type “directory of a specific type” |WHERE | StorageUnits.ActiveSelectionArea = VALUE(Directory.SelectionArea.EmptyLink) // Example 3a. selection based on the condition of an empty field having the type “document of a specific type” |WHERE | OurInformationRegister.Document = VALUE(Document.OurDocument.EmptyLink) // Example 3b. selection based on the condition of an empty field having the type “documents of different types” ( composite field) |WHERE | (OurInformationRegister.Document = VALUE(Document.OurDocument1.EmptyLink) | OR OurInformationRegister.Document = VALUE(Document.OurDocument2.EmptyLink) | OR... (etc. - we sequentially list the conditions for all possible types of this composite field) ) // Example 4. or vice versa, if you need to select a filled value of the "string" type, then the condition will help: |WHERE | Storage Unit.Name > """" // Example 5. if you need to select documents of a specific type, with a composite data type, for example, in the "RunningTasks" register, the "Task" resource has a composite type, among the values of which the document "Selection" is possible |WHERE | EXPRESS(Information RegisterExecutedTasks.Task AS Document.Selection) LINK Document.Selection // Example 5a. Another similar example when you need to select documents of a specific type | CHOICE | WHEN TO EXPRESS (ag Correspondence of Documents. DocumentBU AS Document. Receipt of Goods and Services) LINK Document. Receipt of Goods and Services | THEN ""Receipt of Goods and Services"" | WHEN TO EXPRESS (ag Correspondence of Documents. DocumentBU AS Document. Sales of Goods and Services) LINK Document. Sales of Goods and Services | THEN ""Sales of Goods and Services"" | ELSE """" | END AS Document View // Example 6. selection by condition of an undefined value: |WHERE | SavedSettings.User = UNDEFINED // Example 7. selection by type of movement "Incoming" of the accumulation register, "Expense" - similarly): |WHERE | RegProductsInRetail.MovementType = VALUE(MovementTypeAccumulation.Incoming) // Example 8. How to indicate in a request that it is not necessary to execute the request (for example, you need to programmatically, depending on some condition, return an empty request result - Request.Text = StrReplace(Request.Text, "WHERE Doc.Link = &DocumentLink" , "WHERE IS THE LIE");). To do this, just add the condition “Where is False”. By the way, regardless of the volume of data requested in the sample, such a request will be executed instantly. |WHERE IS THE LIE // Example 9. Checking that the query result contains data: If notRequest.Execute().Empty() Then // Example 10. selection based on an empty date: |WHERE | tbStrings.CancellationDate = DATETIME(1, 1, 1)

1C allows you to significantly simplify accounting or simply manage any organization, be it a small store or a large enterprise. The program is a large database management system. In order not to get confused in all this, you need to be able to perform various simple actions and understand the essence. Next, you will understand how to check the type of value in a request in 1C, as well as what they generally are, as well as how to distinguish between them.

Value types

1C: The enterprise has included a special function in new versions (platform 8.2). Using it, you can add any special parameters to any names or elements of the data system. This was done to make the system easier to edit and add new elements to. This function is called “Value Type”.

In fact, this is one of the basic concepts that most programming languages contain. Using it, you can classify various data. For example: dates, numbers, strings, links. These are just basic classifications. There may be many more of them. Let's say, if you enter information about cities into your system, you can use: continent, country, region, etc.

Examination

You can check a particular field using a query language. Or rather its functions: TYPE VALUE. That is, if we want to find out what type of information the cell of interest contains, we must use the command.

VALUE TYPE(Value) = TYPE(String)

In the example above, we defined the simplest type using the command. Another example of request validation:

Documentation

There are quite a few types; users can make do with the initial ones or enter their own to improve the information base. Here are some of them.

DocumentLink. It is used for conveniently storing references to various objects within other parts of the system.
DocumentObject - editing documents.
DocumentSelection - sorting through objects from the database.

In addition, there are special terms that characterize any data:

form;
line;
boolean;
number;
date of;
array;
checkbox;
picture.

These are just some of them. Any object can only be something from this list. Boolean is a special parameter that takes two values: true or false. There are also special tags that allow you to adjust the request: when, where, how, otherwise, etc. They set the program’s behavior algorithm. 1C is distinguished by the fact that these words here, like everything else, can be entered in Russian.

It is important to understand that all this will be perceived by beginners and non-professionals as Chinese literacy. To understand what we are talking about and effectively use 1C, you need to know the basics of programming. In general, checking the type in a request in the 1C program will be quite easy in comparison with other actions.

43
NULL – missing values. 26
Not to be confused with zero value! NULL is not a number, does not equal a space, an empty reference, or Undefined. 18
The article provides useful techniques when working with 1C v.8.2 queries, as well as information that is not so well known about the query language. I am not trying to give a complete description of the query language, but want to dwell only on... 13
LIKE - Operator for checking a string for similarity to a pattern. Analogue of LIKE in SQL.

The SIMILAR operator allows you to compare the value of the expression specified to the left of it with the pattern string specified to the right. The meaning of the expression...

Attention! This is an introductory version of the lesson, the materials of which may be incomplete.

Log in as a student to access school materials

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

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

In the query language, there is a whole class of functions and operators for working with types of details. Let's look at them.

VALUE TYPE function This function takes one parameter (value) and returns its type. For the props described in the picture (above) Taste directory Food

the following will be returned: Now let's look at the props Distinctive Feature at the directory:

Cities You see that this attribute can be one of several types:, Line, Directory.Tastes Directory.Colors

. This type of details is called COMPOSITE.

If we try to fill in the value of such a detail in 1C:Enterprise mode, the system will ask us what type of value will be entered:

And only after our choice will it allow us to enter the value of the selected type. Thus, directory elements of the same type ( Directory.Cities Now let's look at the props) will be able to store in the same attribute (

) values of different types (String, Colors or Flavors). at the directory You can see this for yourself by clicking on the elements of the directory

in 1C:Enterprise mode. You are reading a trial version of the lesson, full lessons are available. Here the distinguishing feature value is a directory element:

Flavors

Here's the line: And here is generally an element of the reference book:

Colors

These are the possibilities a composite data type opens up for us! I wonder how the function will behave TYPE VALUES on the props DistinctiveElement

, having a composite data type:

This is already very interesting. Let's look at each line individually. 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.

This is true, because the Russia element is a group, and not an ordinary directory element at the directory, so it has no field Now let's look at the props. And the type of a missing value, as we read above, is always equal to NULL.

The type of value of the distinctive feature for Perm is equal to Here the distinguishing feature value is a directory element. This is true, because the value of the distinctive feature entered in the database for the city of Perm is a link to the directory element Here the distinguishing feature value is a directory element.

For Krasnoyarsk, the type of attribute is equal to And here is generally an element of the reference book, because the value selected in the database is a link to a directory element And here is generally an element of the reference book.

For Voronezh, the type of attribute is equal to You see that this attribute can be one of several types:, because the value entered in the database is a regular string.

India is a group again, so there is no significance. And the type of the missing value, as we remember, is equal to NULL.

Here's the thing. If you go to the directory element at the directory with name Sao Paulo, then you will see that the field Now let's look at the props absolutely not filled in at all. It's empty. A all empty fields of a composite type have a special meaning UNDEFINED .

WITH UNDEFINED we are also encountering for the first time. Meaning UNDEFINED used when it is necessary to use an empty value that does not belong to any other type. This is exactly our situation. And the 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 link type you want to get.

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

Sounds vague, doesn't it?

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

Suppose we need to select all directory entries at the directory, which have composite props Now let's look at the props has a value of type LINE:

Now let's select all records that have attribute values Now let's look at the props are links to directory elements And here is generally an element of the reference book(table Directory.Tastes):

Retreat

As you remember, some elements of the directory at the directory don't have props Now let's look at the props. Function I wonder how the function will behave for such elements it produces NULL.

How can you select such elements in a query? A special logical operator is provided for this IS NULL(not to be confused with the function ISNULL, which we will look at below). You are reading a trial version of the lesson, full lessons are available.

Here is an example of its use:

Great. But did you notice that there is no element of Sao Paulo, props value type Now let's look at the props whom he also gave out NULL. Why did it happen?

But the thing is that the situation is for groups (Russia, India, Brazil), for which filling out the details Now let's look at the props impossible in principle, since they don’t have it at all, differs from the situation for the Sao Paulo element, for which filling in the props is possible, but it is simply not filled in and is equal, as we remember, to a special value UNDEFINED.

To select all records that have the requisite Now let's look at the props present, but not filled, a different construction should be used:

But comparison with UNDEFINED to determine empty (unfilled) attributes will only work for composite types.

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

Logical operator LINK

For example, let's select from the directory at the directory only those records that have the value of a composite attribute Now let's look at the props are a link to a directory element Here the distinguishing feature value is a directory element:

As you remember, we could solve the same problem using I wonder how the function will behave And TYPE:

Function ISNULL

The function is designed to replace a value NULL to a different meaning.

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

For example, props Now let's look at the props for directory groups at the directory:

Function ISNULL will help us output a different value if this value is equal to NULL. You are reading a trial version of the lesson, full lessons are available. Let in this case be the line “There is no such prop!”:

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

EXPRESS function

This function is only for fields that have a composite type. An excellent example of such a field is the property Now let's look at the props for directory elements at the directory.

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

For field Now let's look at the props such valid types are LINE, Directory.Tastes And Line.

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

Let's list all field values Now let's look at the props to type Reference.Colors:

As a result, all element values that were of type Directory.Colors, remained filled and were converted to the specified type. All values of other types ( LINE, Line) 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 available. But the type to which the cast is being made must be included in the list of types for this composite field, otherwise the system will throw an error.

Take the test

Start test

1. Choose the most correct statement

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

3. To determine the type of attribute value, use the function

4. Empty details of a composite type are important