1s choice when then example.

Despite all the shortcomings, text field search is still one of the most popular. We can meet string data types everywhere - names, account numbers, addresses, and other information can be stored in this format. In queries in the built-in 1C language, for the convenience of developers, a special operator "LIKE" is used. This is one of the most used commands, so without a thorough knowledge of its syntax and capabilities, it will be difficult for a programmer to work.

Using the "LIKE" operator

Before you put any operator into practice, you need to clearly understand its purpose, places of application and syntax. The purpose of using "LIKE" in the 1C query is to check for satisfaction of the condition presented as a template. The return value is a boolean, true or false, indicating whether the specified condition is met. The LIKE operator can be used in several places in a query:

In the block of conditions indicated by the keyword "WHERE";
In the construction Choice When Then Else End;
Directly in the selection fields, as a result of field comparison.

The syntax of the check is always the same and consists of 3 links. On the left is the text value that is being checked, then the “LIKE” operator itself, and on the right is the template that is being checked. For quick and convenient templating, there are special symbols that make development easier:

"%" is a sequence of any characters of arbitrary length. Used to search for individual words or numbers in a string;
"_" is any single character. Designed to indicate the presence of one character;
"[...]" is a sequence of characters to compare with a sign in the string. With the help of such a pattern, a match is checked for any of the characters listed in brackets. You can also specify a range of numbers or letters ([a-g], );
“[^…]” is the opposite of the previous pattern. The difference between the character specified in the string and those listed in brackets is checked.

To better assimilate and understand the principles of creating correct templates, let's look at some examples that are often encountered in the life of developers. The first one is when we need to select from the nomenclature reference book all positions in the names of which the word "CUTTER" occurs. In this case, we need to use LIKE in the query conditions:

SELECT Nomenclature.Name AS Name FROM Directory.Nomenclature AS Nomenclature WHERE Nomenclature.Name LIKE "%CUTTER%"

If we remove both "%" characters, then the query will show the nomenclature, in which the name completely matches the one indicated in quotes. If we leave the pattern "CUTTER%" or "%CUTTER", then the result will be a list of nomenclature ending or starting, respectively, with a given combination of characters.

Let's analyze a problem that can confuse novice programmers who do not know the query syntax. Let's say you need to find all the nomenclature, in the name of which there is a symbol "%". Especially for cases when you need to search for reserved characters, there is a "special character" operator. As a special character, you can use #, \, /, ~ and other characters, after which any reserved characters will simply denote a character.

SELECT Nomenclature.Name AS Name FROM Directory.Nomenclature AS Nomenclature WHERE Nomenclature.Name LIKE "%#%" SPECIAL CHARACTER "#"

If you need to use a parameter in the search, then the variable in the query with the LIKE parameter is used by adding. Remember that the parameter must be a string type or you will need to convert it to a string in the request. This is a rather complicated operation and it is better to exclude it in advance.

SELECT Nomenclature.Name AS Name FROM Lookup.Nomenclature AS Nomenclature WHERE Nomenclature.Name LIKE "%" + &name + "%"

The LIKE function is applicable in all versions of the platform, starting from 8, and due to its applicability, 1C developers will not want to change it. Of course, text search always depends on the accuracy of entering the name, but it still remains one of the most common. In this regard, professional 1C developers need to study the use of LIKE with all its nuances.

I decided to contribute and describe those features of the language that were not considered in the above articles. The article is aimed at beginner developers.

1. Construction "FROM".

In order to get data from the database, it is not necessary to use the "FROM" construct.
Example: We need to select all information about banks from the bank directory.
Request:

CHOOSE Directory.Banks.*

Selects all fields from the Banks directory. And is similar to the query:

SELECT Banks.* FROM Directory. Banks AS Banks

2. Order data by reference field

When we need to order query data by primitive types: "String", "Number", "Date", etc., then everything is solved by using the "ORDER BY" construct, if you need to order data by a reference field? The reference field is a link, a unique identifier, i.e. Roughly speaking, a certain arbitrary set of characters and the usual ordering may not produce the expected result. For ordering reference fields, the "AUTOORDER" construct is used. To do this, you must first order the data directly by the reference type using the "ORDER BY" construct, and then the "AUTOORDER" construct.

In this case, for documents, ordering will occur in the order "Date-> Number", for directories - by "Main view". If the ordering is not based on reference fields, then using the "AUTOORDER" construct is not recommended.

In some cases, the "AUTOORDER" construct can slow down the sampling process. Similarly, you can rewrite without auto-arranging for documents:

3. Obtaining a textual representation of the reference type. "PRESENTATION" construction.

When you need to display a reference type field for display, for example, the "Bank" field, which is a link to the element of the "Banks" directory, you need to understand that when this field is displayed, a subquery to the "Banks" directory will be automatically executed to get the directory view. This will slow down the data output. In order to avoid this, it is necessary to use the "REPRESENTATION" construct in the request in order to immediately get the representation of the object and already display it for viewing.

In the data composition system, this mechanism is used by default, but when generating layouts in cells, you should specify the representation of the reference field, and for example, put the link itself in the transcript.

4. Condition for data sampling according to the template.

For example, you need to get mobile phones of employees of the form (8 -123-456-78-912). To do this, you need to put the following condition in the request:

SELECT Employee.Name, Employee.Phone AS Phone FROM Directory.Employees AS Employees WHERE Phone LIKE "_-___-___-__-__"

The character "_" is service and replaces any character.

5. Simultaneous use of totals and groupings.

Totals are often used in conjunction with groupings, in which case the aggregate functions in the totals can be omitted.

CHOOSE Services.Organization AS Organization, Services.Nomenclature AS Nomenclature, AMOUNT(Services.Document Amount) AS Document Amount FROM Document.Services AS Services GROUPS BY Services.Organization, Services.Nomenclature SHIE, Organization, Nomenclature

In this case, the request will return almost the same as this request:

CHOOSE Services. Organization AS Organization, Services. Nomenclature AS Nomenclature, Services. Document Amount AS Document Amount FROM Document.

Only the first query will collapse records with the same nomenclature.

6. Dereferencing fields.

Referencing fields through a dot is called the reference field dereferencing operation. For example Payment.Organization.Administrative Unit. In this case, in the "Organization" reference field of the "Payment" document, it refers to another "Organizations" table, in which the value of the "Administrative Unit" attribute will be received. It is important to understand that when accessing fields through a dot, the platform implicitly creates a subquery and joins these tables.

Request:

Can be represented as:

SELECT Payment.Link, Payment.Organization, Payment.Organization, Organizations. AdministrativeUnit FROM Document.Payment AS Payment LEFT JOIN Directory.Organizations AS Organizations Software Payment.Organization = Organizations.Link

When dereferencing reference fields of a composite type, the framework attempts to create implicit joins to all tables that are part of the field type. In this case, the query will not be optimal. If it is clearly known what type of field is, it is necessary to restrict such fields by type with the construction EXPRESS().

For example, there is an accumulation register "Unallocated payments", where several documents can act as a registrar. In this case, it is incorrect to get the values of the registrar details in this way:

SELECT Unallocated Payments.Registrar.Date, ..... FROM Accumulation Register.Unallocated Payments AS Unallocated Payments

you should limit the type of the composite field logger:

SELECT EXPRESS(Unallocated Payments. Registrar AS Document. Payment). Date, ..... FROM Accumulation Register. Unallocated Payments AS Unallocated Payments

7. Construction "WHERE"

With a left join of two tables, when you impose the "WHERE" condition on the right table, we will get a result similar to the result with an inner join of tables.

Example. It is necessary to select all Clients from the Client Directory and for those clients who have a payment document with the attribute value "Organization" = &Organization, display the "Payment" document, for those who do not, do not display it.

The result of the query will return records only for those customers who had payment by organization in the parameter, and will filter out other customers. Therefore, you must first get all payments for "such and such" organization in a temporary table, and then connect with the "Clients" directory with a left join.

SELECT Payment.Reference AS Payment, Payment.Shareholder AS Client PUT topayments FROM Document.Payment AS Payment WHERE Payment.Department = &Department; /////////////////////////////////////////////////// ////////////////////////////// SELECT Clients.Reference AS Client, ISNULL(topayments.Payment, "") AS Payment FROM Directory .Clients AS Clients LEFT JOIN

You can get around this condition in another way. it is necessary to impose the condition "WHERE" directly in the relationship of the two tables. Example:

SELECT Clients.Reference, Payment.Reference FROM Directory.US_Subscribers AS ST_Subscribers LEFT JOIN Document.Payment AS Payment SOFTWARE (Clients.Reference = Payment.Client AND Payment.Client.Name LIKE "Sugar Bag") GROUP BY Clients.Reference, Payment. Link

8. Joins with Nested and Virtual Tables

Subqueries are often necessary to select data according to some condition. If you then use them in conjunction with other tables, then this can critically slow down the execution of the query.

For example, we need to get the Balance Amount for the current date for some clients.

SELECT UnallocatedPayBalances.Customer, UnallocatedPaymentsRemains.AmountBalance FROM (SELECT Clients.Reference AS Reference FROM Directory.Clients AS Clients WHERE Clients.Ref B(&Clients)) AS NestedQuery LEFT JOIN Accumulation register.UnallocatedPayments.Balance AS UnallocatedPayments s Software NestedRequest.Reference = UnallocatedPaymentsRemains.Client

When executing such a query, the DBMS optimizer is likely to make errors when choosing a plan, which will lead to suboptimal query execution. When joining two tables, the DBMS optimizer chooses an algorithm for joining tables based on the number of records in both tables. In the case of a nested query, it is extremely difficult to determine the number of records that the nested query will return. Therefore, instead of nested queries, you should always use temporary tables. So let's rewrite the query.

SELECT Clients.Link AS Link PUT Clients FROM Directory.Clients AS Clients WHERE
Clients.Link B (&Clients) ; ////////////////////////////////////////////////// ////////////////////// SELECT tClients.Reference, UnallocatedPaymentsBalances.SumBalance, FROM tClients AS tClients LEFT JOIN Accumulation Register.UnallocatedPayments.Balances (, Client IN (SELECT tClients.Reference FROM tClients)) AS UnallocatedPaymentsBalances ON tClients.Reference = UnallocatedPaymentsBalances.Clients

In this case, the optimizer will be able to determine how many records the temporary table tClients uses and will be able to choose the optimal table join algorithm.

Virtual Tables , allow you to get almost ready-made data for most application tasks. (Slice of the First, Slice of the Last, Residuals, Turnovers, Residuals and Turnovers) The keyword here is virtual. These tables are not physical, but are assembled by the system on the fly, i.e. when receiving data from virtual tables, the system collects data from the final tables of registers, composes, groups and issues to the user.

Those. when you join with a virtual table, you join with a subquery. In this case, the DBMS optimizer may also choose a non-optimal join plan. If the query is not formed quickly enough and the query uses joins in virtual tables, then it is recommended to transfer the access to virtual tables to a temporary table, and then to make a join between two temporary tables. Let's rewrite the previous query.

SELECT Clients.Link AS Link PUT Clients FROM Directory.Clients AS Clients INDEX BY Link WHERE
Clients.Link B (&Clients) ; ////////////////////////////////////////////////// ////////////////////////////// SELECT UnallocatedPayments.AmountBalance, UnallocatedPayments.Customer AS Customer PUT the balances FROM Accumulation Register.UnallocatedPayments.Balances(, Client IN (SELECT tClients.Reference FROM tClients)) AS UnallocatedPaymentsBalances; ////////////////////////////////////////////////// ////////////////////////////// SELECT tClients.Reference, thenRemains.SumRemainder AS SumRemainder FROM tClients AS tClients tClients.Reference = tRemainders.Client

9.Checking the result of the query.

The result of the query execution may be empty; to check for empty values, use the construction:

RequestRes = Request.Execute(); If reQuery.Empty() Then Return; EndIf;

Method Empty() should be used before methods Choose() or Unload(), since it takes time to get the collection.

It is not a discovery for anyone that it is highly undesirable to use queries in a cycle. This can critically affect the operation time of a particular function. It is highly desirable to receive all the data in the request and only then process the data in a loop. But sometimes there are cases when it becomes impossible to take the request out of the loop. In this case, for optimization, you can move the creation of the query outside the loop, and substitute the necessary parameters in the loop and execute the query.

This will save the system from parsing the request in a loop.

11. Construction "HAVING".

A construction that is quite rare in queries. Allows you to impose conditions on the values of aggregate functions (SUM, MINIMUM, AVERAGE, etc.). For example, you need to select only those customers whose payment amount in September was more than 13,000 rubles. If you use the "WHERE" condition, you will first have to create a temporary table or a nested query, group records there by the amount of payment and then impose a condition. The "HAVING" construction will help to avoid this.

SELECT Payment.Customer, AMOUNT(Payment.Amount) AS Amount FROM Document.Payment AS Payment WHERE MONTH(Payment.Date) = 9 GROUP BY Payment.Customer HAVING AMOUNT(Payment.Amount) > 13000

In the constructor, all you need to do is go to the "Conditions" tab, add a new condition and check the "Custom" box. Then just write Amount(Payment.Amount) > 13000

12. Null value

I will not describe here the principles of three-valued logic in the database, there are many articles on this topic. Just a glimpse of how NULL may affect the result of the query. The NULL value is not really a value, and the fact that the value is not defined is unknown. Therefore, any operation on NULL returns NULL, be it addition, subtraction, division, or comparison. The NULL value cannot be compared to the NULL value because we don't know what to compare. Those. both of these comparisons: NULL = NULL, NULL<>NULL is not True or False, that is unknown.

Let's look at an example.

For those customers who have no payments, we need to display the "Attribute" field with the value "No payments". And we know for sure that we have such clients. And in order to reflect the essence of what I wrote above, let's do it this way.

SELECT "No payments" AS Attribute, NULL AS Document PUT to payments; ////////////////////////////////////////////////// /////////////////////////////// SELECT Clients.Link AS Client, Payment.Link AS Payment PUT tClientPayment FROM Directory.Clients AS Clients LEFT JOIN Document.Payment AS Payment Software Clients.Link = Payment.Shareholder; ////////////////////////////////////////////////// /////////////////////////////// SELECT tClientPayment.Customer FROM tClientPay AS tClientPayment INTERNAL JOIN topayments AS topayments BY tClientPayment.Payment = topayments. Document

Pay attention to the second temporary table tCustomerPayment. With the left join, I select all clients and all payments for these clients. For those customers who do not have payments, the "Payment" field will be NULL . Following the logic, in the first temporary table "topayments" I designated 2 fields, one of them is NULL, the second is the line "Has no payments". In the third table, I join the "tClientPayment" and "tPayment" tables by the "Payment" and "Document" fields with an inner join. We know that in the first table the "Document" field is NULL, and in the second table those who do not have payments in the "Payment" field are also NULL. What will return us such a connection? And it won't return anything. Because the comparison NULL = NULL does not evaluate to True.

In order for the query to return the expected result to us, we rewrite it:

SELECT "No payments" AS Sign, VALUE(Document. Payment. Empty Reference) AS Document PUT to Payments; ////////////////////////////////////////////////// /////////////////////////////// SELECT Clients.Reference AS Client, ISNULL(Payment.Reference, VALUE(Document.Payment.EmptyReference )) HOW TO PUT tClientPayment FROM Directory.Clients AS Clients LEFT JOIN Document.Payment AS Payment ON Clients.Reference = Payment.Shareholder; ////////////////////////////////////////////////// /////////////////////////////// SELECT tClientPayment.Customer FROM tClientPay AS tClientPayment INTERNAL JOIN topayments AS topayments BY tClientPayment.Payment = topayments. Document

Now, in the second temporary table, we have indicated that if the "Payment" field is NULL, then this field = an empty reference to the payment document. In the First Table, we also replaced NULL with a null reference. Now non-NULL fields are involved in the connection and the query will return the expected result.

All requests contained in the article reflect situations that I would like to consider and nothing more. ABOUT nor can they be crazy or not optimal, the main thing is to reflect the essence of the example.

13. Undocumented design feature "CHOICE WHEN...THEN....END".

In the case when it is necessary to describe the "Conditions" construct in the request, then we use the standard syntax:

SELECT CHOICE WHEN Users.Name = "Vasya Pupkin" THEN "Our favorite employee" ELSE "We don't know this" END AS Field1 FROM Directory.Users AS Users

But what if, for example, we need to get the name of the month in the query? Writing a huge construct in a query is ugly and time consuming, so this form of notation above can help us out:

Choice of a month (settlement of_graphicborne assessment. Periodrass account) when 1 then “January” when 2 then “February” when 3 then “March” when 4 then “April” when 5 then “May” when 6 then “June” when 7 is “July” then “July” 8 THEN "August" WHEN 9 THEN "September" WHEN 10 THEN "October" WHEN 11 THEN "November" WHEN 12 THEN "December" END AS A MONTH

Now the design looks not so cumbersome and is easily perceived.

14. Batch query execution.

In order not to produce requests, you can create one large request, break it into packages and work with it already.
For example, I need to get fields from the "Users" directory: "Date of Birth" and available roles for each user. to unload it into different tabular parts on the form. Of course, you can do this in one query, then you have to iterate over the records or collapse, or you can do this:

SELECT Users.Link AS Name, Users.Date of Birth, Users.Role ENTER Users FROM Directory.Users AS Users; ////////////////////////////////////////////////// /////////////////////////////// SELECT tuUsers.Name, tuUsers.Date of Birth FROM tuUsers AS tuUsers GROUP BY tuUsers.Name, tuUsers. Date of Birth; ////////////////////////////////////////////////// /////////////////////////////// SELECT wUsers.Name, wUsers.Role FROM wUsers AS wUsers GROUP BY wUsers.Name, wUsers. Date of Birth

tPackage = Request.ExecutePackage();

TP_BirthDate = tPackage.Unload();
TP_Roles = tPackage.Unload();

As we can see, the query can be executed in a batch and work with the result as an array. In some cases, very convenient.

15. Conditions in a batch request

For example, we have a batch request, where we first get the fields: "Name, Date of Birth, Code" from the "Users" directory and want to get records with a condition on these fields from the "Individuals" directory.

SELECT Users.Individual.Name AS Name, Users.Individual.Date of Birth AS Date of Birth, Users.Individual.Code AS Code PUT in Users FROM Directory.Users AS Users; ////////////////////////////////////////////////// ////////////////////////////// SELECT Individuals.Link AS Individual FROM Directory.Individuals AS Individuals

You can apply conditions like this:

WHERE Individuals.Code At (SELECT TueUsers.Code FROM TuUsers) AND Individuals.Name At (SELECT TueUsers.Code FROM TuUUsers) AND Individuals.Date of Birth At (SELECT TueUsers.Date of Birth FROM TuUsers)

And it's possible like this:

WHERE (Individuals.Code, Individuals.Name, Individuals.Date of Birth) AT (SELECT TueUsers.Code, TueUsers.Name, TueUsers.Date of Birth FROM TueUsers)

And be sure to follow the rules.

16. Call Query Builder for "Condition" in Batch Query

When you need to impose a condition, as in the example above, you can forget how this or that field is called in the virtual table.
For example, you need to impose a condition on the "Date of Birth" field, and in the virtual table this field is called "Date of Birth of the Debtor", and if you forgot the name, you will have to exit editing the condition without saving and look at the field name. To avoid this, you can use the following trick.

It is necessary to put brackets after the Construction "B" and leave an empty space (space) between the brackets, select this place and call the query constructor. The constructor will have access to all batch query tables. Reception works both on virtual tables of registers and for the "Conditions" tab. In the latter case, it is necessary to check the "A (arbitrary condition)" checkbox and enter the "F4" editing mode.

The queries are often made up on the go and they just serve to display the "tricks" I've been considering.

I wanted to consider the use of indexes in queries, but it is a painfully extensive topic. I'll put it in a separate article, or later add it here.

upd1. Paragraphs 11,12
upd2. Items 13,14,15,16

Used Books:
Query language "1C:Enterprise 8" - E.Yu. Khrustalev
Professional development in the 1C:Enterprise 8 system.

NULL is nothing more than the absence of a value. Many confuse it with the value "0" of the number type, an empty reference to some object, or with an empty string. This misconception causes many errors.

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

Based on SQL, which does not allow you to test for NULL with regular equality. Below are two ways to check for NULL in 1C 8.3.

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

the expression to be checked;
replacement expression.

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

Below is an example. It selects all stock items of the tabular part of the goods from the document "Receipt of goods and services". With the help of a left join, each item is affixed with the last price from the information register "Item prices".

In this case, a situation may arise that for some position there may simply be no 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 get an error.

CHOOSE

ISNULL(Prices.Price, 0) AS ActualPrice
FROM

WHERE

THERE IS NULL in the SELECT statement

The analogue of the function ISNULL() is "IS NULL", which is used in the SELECT statement and checks if the value is NULL. "IS" in this case implies equality, and the query of the previous example would look like this:

CHOOSE
Products.Nomenclature AS Product,
CHOICE
WHEN Prices.Price IS NULL
THEN 0
ELSE Prices.Price
END AS ActualPrice
FROM
Document.Incoming GoodsServices.Goods AS Goods
LEFT JOIN
PO Goods.Nomenclature = Prices.Nomenclature
WHERE
Goods.Link = &LinkToDocument

Differences between the ISNULL() function and the IS NULL function

As you can see from the previous examples, the query returns the same data in both cases. The ISNULL() function is shorthand for SELECT WHEN...IS NULL...END, but it is still preferred for the following reasons:

The ESTNULL() function optimizes the query. It is read once, so when checking a complex expression, the query will run faster.
The ESTNULL() function shortens the construct, making the query more readable.
When the ESTNULL() function is executed, the replacement expression is converted to the type of the expression being checked for the types of string types (to the length of the string) and numeric types (to the bitness).

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
permitted
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 the 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 a text value in the "Nomenclature Type" field - "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, the following condition could be dispensed with:

"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 the selection in the form of an aggregate function, this cannot be done in the construction 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)

A rather useful function of the 1C query language, which checks the value in the record, and if it is equal to NULL, it allows you to replace it with your own value. Most often used when obtaining virtual tables of 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!

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

Query Language Functions 1s

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

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

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

Usage example:

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

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

Usage example:

Query.Text = "SELECT | DATE DIFFERENCE(DATETIME(2015, 4, 17), DATETIME(2015, 2, 1), DAY) | AS Number of Days";

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

Syntax: VALUE(<Имя>)

Usage example:

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

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

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

Usage example:

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

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

Syntax: EXPRESS(FieldName AS TypeName)

Usage example:

Still there is a variant of use of the EXPRESS function in fields of the mixed types where such meet? The simplest example is the "Registrar" for any register. So why would we need to qualify the type in the registrar? Let's consider the situation when we select the "Number" field from the registrar, from which table will the number be selected? Correct answer of all! Therefore, in order for our query to work quickly, we must specify an explicit type using the EXPRESS function

Usage example:

Query.Text = "CHOOSE | EXPRESS(Nomenclature.Comment AS String(300)) AS Comment, | EXPRESS(Nomenclature.Sum AS Number(15,2)) AS Sum |FROM | Reference.Nomenclature AS Nomenclature";

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

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

Usage example:

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

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

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

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

Usage example:

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

Constructions in the query language 1s

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

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

Usage example:

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

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

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

Usage example:

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

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

Usage example:

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

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

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

Line template options:

% - a sequence containing any number of arbitrary characters.

One arbitrary character.

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

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

Usage example:

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

Syntax: ALLOWED is written after the keyword SELECT

Usage example:

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

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

Syntax: DIFFERENT is written after the keyword SELECT

Usage example:

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

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

Usage example:

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

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

Syntax: FIRST<число>

Usage example:

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

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

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

Usage example:

9. Structure ORDER BY- sorts data by a specific field. If the field is a link, then when setting the flag AUTO ORDER will be sorted according to the link representation, if the flag is off, then the links are sorted by the seniority of the link address in memory.

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

Usage example:

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

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

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

Usage example:

Groupings that were formed during the execution of the request are circled in the figure, the top one refers to the GENERAL section, and the second to the ContractContractorTypeContract field.