The type of the value in the request. How to get the Kind (type) of the Document in a request? TYPE in request

Request . Text = "CHOOSE | usStorageUnits.Reference | FROM | Reference.usStorageUnits AS usStorageUnits // Example 1. comparison with an empty boolean value: |WHERE | setStorageUnit.Allow SelectionFromStandbyZone = False // Example 2. but if this boolean is defined, then this is better: // condition on a negative boolean: |WHERE | NOT withStorageUnits.Allow WithdrawalFromStandbyZone // Example 3. selection by condition of an empty field of type "concrete type reference" |WHERE | setStorageUnit.ActiveFilterRegion = VALUE(Catalog.FilterRegion us.NullReference) // Example 3a. selection by condition of an empty field of the type "document of a specific type" |WHERE | OurInformationRegister.Document = VALUE(Document.OurDocument.EmptyReference) // Example 3b. selection by condition of an empty field of the type "documents different types" (composite field) |WHERE | (OurInformationRegister.Document = VALUE(Document.OurDocument1.EmptyReference) | OR OurInformationRegister.Document = VALUE(Document.OurDocument2.EmptyReference) | OR... (etc. - 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 | usStorageUnits.Name > """" // Example 5. if you need to select documents of a specific type, with a composite data type, for example, in the "Executed Tasks" register, the "Task" resource has a composite type, among the values ​​of which the "Selection" document is possible |WHERE | EXPRESS(InformationRegisterJobs to be performed.Job AS Document.Filter) REF Document.Filter // Example 5a. Another similar example when you need to select documents of a specific type | CHOICE | WHEN TO EXPRESS(agDocument Compliance.DocumentBU AS Document.Incoming Goods/Services) REFERENCE Document.Incoming Goods/Services | THEN ""Receipt of Goods of Services"" | WHEN TO EXPRESS(ar Correspondence of Documents.DocumentBU AS Document.Sales of Goods of Services) REFERENCE Document.Sales of Goods of Services | THEN "Realization of Goods and Services" | ELSE """" | END AS KindDocument // Example 6. selection by condition of an undefined value: |WHERE | SavedSettings.User = UNdefined // Example 7. selection by the type of movement "Incoming" of the accumulation register, "Expense" - similarly): |WHERE | RegGoodsRetail.MovementType = VALUE(MovementAccumulationType.Income) // Example 8. How to indicate in the query that it is not necessary to execute the query (for example, you need to programmatically return an empty query result depending on some condition - Query.Text = StrReplace(Query.Text, "WHERE Doc.Link = &DocumentLink" , "WHERE TO LIE");). To do this, just add the "Where is False" condition. By the way, regardless of the amount of data requested in the sample, such a request will be executed instantly. |WHERE TO LIE // Example 9. Checking if the query result contains data: If notRequest.Run().Empty() Then // Example 10. selection by condition of an empty date: |WHERE | tbRows.CancellationDate = DATETIME(1, 1, 1)

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

Login as a student

Sign in as a student to access school content

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 composite type have a special meaning UNDEFINED .

WITH UNDEFINED we also meet for the first time. Meaning UNDEFINED used when it is necessary to use empty value A 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 IS NULL 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 IS NULL 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 IS NULL 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 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 efficient tool for obtaining data. The query language allows you to get information from the database in a convenient way.

The syntax itself is very much like the classic T-SQL, except that in 1C, using the query language, you can only get data using the Select construct. The language also supports more complex constructs, such as (request within a request). Requests in 1C 8 can be made both in Cyrillic and in Latin.

In the 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 REF operator)
• choice
• group by
• having
• IS NULL
• There are NULL
• connections - right, left, internal, full.

As well as small tricks of the 1C language, using which you can optimally build the query 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 at the link -.

Consider the most important and interesting operators of the 1C query language.

SELECT

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

For example:

CHOOSE
CurrentCatalog.Name
FROM
Reference.Nomenclature AS CurrentReference

The query will return a table with item names.

next to the structure CHOOSE keywords can be found FOR CHANGE, ALLOWED, VARIOUS, FIRST …

ALLOWED- selects only records from the table to which the current user has rights.

VARIOUS- means that the result will not include duplicate rows.

SELECTION (CASE)

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

CurrentCatalog.Name,

WHEN CurrentCatalog.Service THEN

"Service"

END AS A KindNomenclature

Reference.Nomenclature AS CurrentReference

The example will return in the field "Nomenclature Type" text value- "Product" or "Service".

WHERE

A 1C query language construct that allows you to apply a selection to the received data. Please note that the system receives all data from the server, and only then they are selected according to this parameter.

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

In the example, we select records that have the value of the attribute "Service" set to "True". IN this example it would be possible to get by with the following condition:

"WHERE Service"

In essence, we select rows whose 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 the reference to predefined elements and enumerations in the 1C query:

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

Time values ​​can be specified as follows:

WHERE DateIncoming > DATETIME(2012,01,01):

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

Get 267 1C video lessons for free:

WHERE ItemGroup= &ItemGroup

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

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

WHERE Accumulation Register.Registrar B (&List of DocumentsToSelection)

The condition can be complex, consisting of several conditions:

WHERE DateIncoming > DATETIME(2012,01,01) AND ItemGroup= &ItemGroup AND NOT Service

GROUP BY

1C 8.2 query language construct used to group the result.

For example:

CHOOSE
Receipt of Goods, Services, Goods. Goods,
SUM(Incoming Goods, Services, Goods. Quantity) AS Quantity,
SUM(Incoming Goods, Services, Goods. Amount) AS Sum
FROM
Document.Receipt of GoodsServices.Goods AS Receipt of GoodsServicesGoods

GROUP BY
Receipt of Goods, Services, Goods. Goods

This query will sum up all receipts by amount and quantity in the context of the item.

Apart from the keyword SUM other aggregate functions can be used: QUANTITY, NUMBER OF DIFFERENT, MAXIMUM, MINIMUM, AVERAGE.

HAVING

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

An example of using HAVING in a 1C request:

CHOOSE
Receipt of Goods, Services, Goods. Goods,
SUM(Incoming Goods, Services, Goods. Quantity) AS Quantity,
SUM(Incoming Goods, Services, Goods. Amount) AS Sum
FROM
Document.Receipt of GoodsServices.Goods AS Receipt of GoodsServicesGoods

GROUP BY
Receipt of GoodsServicesGoods.goods

SUM(Incoming Goods, Services, Goods. Quantity) > 5

So we will select the number of goods, which received more than 5 pieces.

MEANING()

For example:

WHERE Bank = Value(Catalog.Banks.EmptyReference)

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 as follows: 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<Тип значения>)

With it, you can convert string values ​​to dates or reference values ​​to string data, and so on.

In practical use, 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 get an error You cannot compare fields of unlimited length and fields of incompatible types.

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

GROUP BY
EXPRESS(ContactInformation.Representation AS A STRING(150)),
ContactInformation.Object

ISNULL (ISNULL)

Enough useful feature query language 1C, which checks the value in the record, and if it is equal NULL, it allows you to replace it with your own value. Most often used when receiving virtual tables balances and turnovers to hide NULL and put a clear 0 (zero).

ISNULL(TaxesPrevMonth.FSS Exemption Applied, 0)

Such a function of the 1C query language IS NULL in the absence of a value will return zero, which will avoid an error.

JOIN

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

LEFT and RIGHT JOIN

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

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" will be met. If the condition is not met, the Bank field will be set to NULL.

RIGHT JOIN in 1C 8.3 absolutely similar LEFT join, except for one difference: RIGHT JOIN the "master" table is the second, not the first.

FULL CONNECTION

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

For example:

FULL CONNECTION
Directory. Banks AS Banks

BY

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

INNER JOIN

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

For example:

FROM
Directory. Counterparties AS Clients

INNER JOIN
Directory. Banks AS Banks

BY
Clients.Name = Banks.Name

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

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!

  43
 NULL - missing values. Not to be confused with null! NULL is not a number, not equal to a space, null reference, undefined. NULL is a type-forming value, i.e. there is a NULL type and a single value of this type. NULL...   26
 To form and execute queries to database tables in the 1C platform, a special object of the Request programming language is used. This object is created by calling the New Request construct. Convenient request...   18
 The article provides useful tricks when working with 1C v.8.2 queries, as well as information that is not so well known about the query language. I don't try to give Full description query language, but I want to focus only on ...   13
 LIKE - An operator to check if a string is similar to a pattern. Analogue of LIKE in SQL. The LIKE operator allows you to compare the value of the expression specified to the left of it with the template string specified to the right. Expression value...

1C allows you to significantly simplify bookkeeping or simply manage any organization, whether it is a small shop or a large enterprise. The program is major system on database management . 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 a value in a request in 1C, as well as what they are, in general, and how to distinguish between them.

Value types

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

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

Examination

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

VALUETYPE(Value) = TYPE(String)

In the example above, we have defined, using the command, simplest form. Another request validation example:

Documentation

There are quite a few types themselves, users can get by with both the initial ones and enter their own in order to improve information base. Here are some of them.

• DocumentLink. It is applied for convenient storage links to various objects within other parts of the system.
• DocumentObject - editing documents.
• DocumentSelection - sorting objects from the database.

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

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

This is just a part of them. Any object can only be something from this list. boolean - special parameter, which 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 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 a Chinese letter. To understand what is at stake and use 1C effectively, you need to know the basics of programming. In general, it will be quite easy to check the type in a request in the 1C program, in comparison with other actions.