NULL– missing values.
Not to be confused with zero value! NULL is not a number, does not equal a space, an empty reference, or Undefined.
NULL is a type-forming value, i.e. there is a type NULL and a single value of this type.
NULL values appear in the query in the following situations:
a) An outer join in which no corresponding record was found in another table (if left - in the second, with right - in the first, if full - in both)
b) Accessing the details of elements for a group and vice versa.
c) NULL in the list of selection fields (SELECT)
d) Accessing the details for a broken link
IS NULL used in the SELECT operator (as if checking that the value is empty (Value IS NULL)):
Code 1C v 8.x
CHOICE
WHEN Value IS NULL THEN ResultIfNULL
ELSE Meaning
END
another example:
Code 1C v 8.x SELECT
CHOICE WHEN Accounting ItemsRemaining.QuantityRemaining IS NULL THEN 0
OTHERWISE Accounting for Nomenclature Remaining. Quantity Remaining AS Quantity Remaining
FROM
WHERE
Function ISNULL(value, ResultIfNULL) returns the value of its first parameter if it is not NULL, and the value of the second parameter otherwise
Is collapsed SELECT...END, but ISNULL is preferred.
Code 1C v 8.x
CHOOSE
ISNULL(Directory.Nomenclature.Article, "---") AS Article,
Directory.Nomenclature.Presentation AS Nomenclature
another example:
Code 1C v 8.x
CHOOSE
Directory of Nomenclature. Name,
ISNULL(AccountingItemRemaining.QuantityRemaining, 0) AS QuantityRemaining
FROM
Directory.Nomenclature AS DirectoryNomenclatures
LEFT CONNECTION Register Accumulations. Item Accounting. Balances AS Item Accounting Balances
Software Item AccountingRemains.Nomenclature = Nomenclature Directory.Link
WHERE
Nomenclature Directory.ThisGroup = FALSE
IN in this example all elements of the item directory are obtained, after which, for each item, the current balances are obtained from the accumulation register. Because for an item for which there are no balances, the virtual table of balances will not return a record, then as a result of the connection in the field "Item AccountingRemaining.QuantityRemaining" there will be NULL values for the item for which there were no balances. To ensure that instead of the NULL value, the result of the request contains the value 0, we used the ISNULL() function, which will carry out the desired replacement.
ISNULL differs from CHOICE for the following reasons:
a) If ISNULL, the query is better readable (simpler)
b) If ISNULL, if a complex expression is checked, it works faster since it is calculated once
c) If ISNULL, the replacement expression is converted to the type of the expression being tested if it is of type String (length) or Number (bit depth).
You cannot check values for NULL using the usual equality, because SQL uses three-valued logic - True, False, NULL, and the result of such a comparison will be UNKNOWN, which in 1C 8.0 is similar to FALSE.
NULL<>0, so for left external connections Ref. Nomenclature with tables of balances, prices, Counterparties with mutual settlements, in the absence of such records, there will be NULL, which is not equal to 0. The best solution– ISNULL
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
- to 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 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 TO 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 GoodsGROUP 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 GoodsGROUP BY
Receipt of Goods and Services Goods. goodsSUM(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 as follows: using the TYPE() and VALUETYPE() functions or using logical operator LINK.
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 application 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 InformationGROUP BY
EXPRESS(ContactInfo.Representation AS ROW(150)),
ContactInformation.Object
ISNULL (ISNULL)
Enough useful feature 1C query language, which 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 by certain 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 BanksBY
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 ClientsINNER JOIN
Directory.Banks HOW BanksBY
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!
When working with a query language, sometimes a situation arises when you need to replace a NULL value with some other value. The need for such an operation may arise, for example, when receiving stock balances for the entire product range. This article describes the use of the query language function ISNULL() to solve such problems, and also discusses other solution options.
Function ISNULL
The query language implements the ISNULL() function, the purpose of which is to replace an expression with another expression if the expression had the value NULL. The syntax diagram for this function looks like this:
ISNULL(<Проверяемое выражение>, <Выражение замены>)
This function will return the value of the first parameter if it is not NULL, and the value of the second expression, otherwise.
CHOOSE
ISNULL(AccountingItemRemaining.QuantityRemaining, 0) AS QuantityRemaining
FROM
WHERE
In this example, all elements of the item directory are obtained, after which, for each item, the current balances are obtained from the accumulation register. Because for an item for which there are no balances, the virtual table of balances will not return a record, then as a result of the connection in the field "Item AccountingRemaining.QuantityRemaining" there will be NULL values for the item for which there were no balances. To ensure that instead of the NULL value, the result of the request contains the value 0, we used the ISNULL() function, which will carry out the desired replacement.
Using the SELECT operation
To solve the problem described earlier, you can use the SELECT query language operation. An example of how a request similar to the previous one would look would look like this:
CHOOSE
Directory of Nomenclature. Name,
CHOICE WHEN Accounting ItemsRemaining.QuantityRemaining IS NULL THEN 0
OTHERWISE Accounting for Nomenclature Remaining. Quantity Remaining AS Quantity Remaining
FROM
Directory.Nomenclature AS DirectoryNomenclatures
LEFT CONNECTION Register Accumulations. Item Accounting. Balances AS Item Accounting Balances
Software Item AccountingRemains.Nomenclature = Nomenclature Directory.Link
WHERE
Nomenclature Directory.ThisGroup = FALSE
Result of this request will be identical to the result of the query given in the previous section.
Using the ISNULL() function is preferable to using the SELECT operation for the following reasons: writing using ISNULL() is somewhat more compact, which increases the readability of the query. In addition, in the case when the expression being tested is a complex function, including an aggregate function, the calculation of the ISNULL() function can be performed faster than its counterpart written using the SELECT function.
Features of the ISNULL function
The ISNULL() function, although it is an analogue of the SELECT operation with checking the value for NULL, nevertheless has a difference. The difference is that if the function expression is of a string or numeric type, the replacement expression will be converted to the type of the expression being tested.
So, for example, in the case where the expression being tested is of type String(5), and the replacement expression is of type String(10), the result type will be converted to type String(5). As a result, when the function returns a replacement expression, the value will be truncated to five characters.
With numeric expressions the situation is similar: the value of the replaced expression will be converted to the type of the one being checked, i.e. the expression being replaced may be truncated. If the value cannot be converted, the query language will fail the query. For example, an attempt to convert the number 1000 to the Number(2) type will fail with an error.
NULL- missing values.
Not to be confused with zero value! NULL is not a number, does not equal a space, an empty reference, or Undefined.
NULL is a type-forming value, i.e. there is a type NULL and a single value of this type.
NULL values appear in the query in the following situations:
a) An outer join in which no corresponding record was found in another table (if left - in the second, with right - in the first, if full - in both)
b) Accessing the details of elements for a group and vice versa.
c) NULL in the list of selection fields (SELECT)
d) Accessing the details for a broken link
IS NULL used in the SELECT operator (as if checking that the value is empty (Value IS NULL)):
Code 1C v 8.x
CHOICE
WHEN Value IS NULL THEN ResultIfNULL
ELSE Meaning
END
another example:
Code 1C v 8.x SELECT
CHOICE WHEN Accounting ItemsRemaining.QuantityRemaining IS NULL THEN 0
OTHERWISE Accounting for Nomenclature Remaining. Quantity Remaining AS Quantity Remaining
FROM
WHERE
Function ISNULL(value, ResultIfNULL) returns the value of its first parameter if it is not NULL, and the value of the second parameter otherwise
Is collapsed SELECT...END, but ISNULL is preferred.
Code 1C v 8.x
CHOOSE
ISNULL(Directory.Nomenclature.Article, "---") AS Article,
Directory.Nomenclature.Presentation AS Nomenclature
another example:
Code 1C v 8.x
CHOOSE
Directory of Nomenclature. Name,
ISNULL(AccountingItemRemaining.QuantityRemaining, 0) AS QuantityRemaining
FROM
Directory.Nomenclature AS DirectoryNomenclatures
LEFT CONNECTION Register Accumulations. Item Accounting. Balances AS Item Accounting Balances
Software Item AccountingRemains.Nomenclature = Nomenclature Directory.Link
WHERE
Nomenclature Directory.ThisGroup = FALSE
In this example, all elements of the item directory are obtained, after which, for each item, the current balances are obtained from the accumulation register. Because for an item for which there are no balances, the virtual table of balances will not return a record, then as a result of the connection in the field "Item AccountingRemaining.QuantityRemaining" there will be NULL values for the item for which there were no balances. To ensure that instead of the NULL value, the result of the request contains the value 0, we used the ISNULL() function, which will carry out the desired replacement.
ISNULL differs from CHOICE for the following reasons:
a) If ISNULL, the query is better readable (simpler)
b) If ISNULL, if a complex expression is checked, it works faster since it is calculated once
c) If ISNULL, the replacement expression is converted to the type of the expression being tested if it is of type String (length) or Number (bit depth).
You cannot check values for NULL using the usual equality, because SQL uses three-valued logic - True, False, NULL, and the result of such a comparison will be UNKNOWN, which in 1C 8.0 is similar to FALSE.
NULL<>0, so for left outer joins, refer. Nomenclature with tables of balances, prices, Counterparties with mutual settlements, in the absence of such records there will be NULL, which is not equal to 0. The best solution is ISNULL
Hello.
Continuing on primitive data types, we recently looked at them, and today we’ll talk about type NULL.
The NULL type is a literal that can contain only one value, the value “NULL”.
NULL — Not equal to a null reference, space, or Type Undefined.
Used when working with a database (when joining tables), used to determine a missing value when working with a database.
A NULL value can be obtained by assigning this value to a variable:
Variable =NULL.
Values of the NULL type are formed as a result of joins when an element from one table does not have a corresponding element from another. A value of type NULL has specific features:
- comparing a NULL value with any other argument always returns false;
To determine the value NULL, use the construct IS NULL (is NULL).
To convert the NULL type, use the function ISNULL (isNULL).
In order to cut off fields containing NULL values as a result of a request, the following constructions are used: - is not NULL - not is NULL
Examples
Example of checking a value for NULL
SELECT Directory. Nomenclature. Name, Directory. Nomenclature. PurchasingPrice WHERE Directory. Nomenclature. PurchasePrice Yes NULL
Example of the ISNULL() function
Return value of the function ISNULL(): the value of the first parameter, if the first parameter does not contain a NULL value, otherwise the value of the second parameter. The second parameter will be converted to the type of the first parameter in case the type of the first parameter is a string or a number.
Sincerely, 1C Programmer.
Leave your comments, I'm interested in your opinion.