There is null in the request. Using the function isnull() TYPE in a query

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 type NULL 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 if the type of the first parameter is a string or a number.

// Get the amount by the quantity field. If there are no records, get 0 SELECT ISNULL(SUM(Quantity), 0 ) AS Quantity FROM Document. Expense Inc. Compound

Sincerely, 1C Programmer.
Leave your comments, I'm interested in your opinion.

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

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

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

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

expression to be tested;
replacement expression.

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

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

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

CHOOSE

ISNULL(Prices.Price, 0) AS CurrentPrice
FROM

WHERE

THERE IS NULL in the SELECT statement

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

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

Differences between the function ISNULL() and IS NULL

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

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

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

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 prop 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 selection will it allow us 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 ( Distinctive Feature) values of different types (String, Colors or Flavors).

You can see 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 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.

The type of value of the distinctive feature for the element Russia is equal to 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 Cities, so it has no field Distinctive Feature. 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 Flavors. 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 Flavors.

For Krasnoyarsk, the type of attribute is equal to Colors, because the value selected in the database is a link to a directory element Colors.

For Voronezh, the type of attribute is equal to Line, 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 Cities with name Sao Paulo, then you will see that the field Distinctive Feature 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 Cities, which have composite props Distinctive Feature has a value of type LINE:

Now let's select all records that have attribute values Distinctive Feature are links to directory elements Colors(table Directory.Colors):

Retreat

As you remember, some elements of the directory Cities don't have props Distinctive Feature. Function TYPE VALUES 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 Distinctive Feature which he also gave out NULL. Why did it happen?

But the thing is that the situation for groups (Russia, India, Brazil), for which filling out the details Distinctive Feature 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 Distinctive Feature 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 Cities only those records that have the value of a composite attribute Distinctive Feature are a link to a directory element Flavors:

As you remember, we could solve the same problem using TYPE VALUES 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 Distinctive Feature for directory groups Cities:

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 Distinctive Feature for directory elements Cities.

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

For field Distinctive Feature such valid types are LINE, Directory.Colors And Directory.Tastes.

Sometimes there is a need to cast the values of a composite field to a specific type.

Let's list all field values Distinctive Feature 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, 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 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

In the process of developing reports on SKD in 1C Enterprise, the task of displaying zeros in empty report fields often arises. The fact is that, if the necessary data is not in the database, the query returns not the number 0, but a special value NULL, which then must be converted into a number. For example, if we write

and the information register is empty, then the result of the request in the “Price” field will be NULL values. And if, according to the conditions of the task, we need to display zeros in the report, then we need to compose the query as follows:

Do you have a question or need help from a consultant?

But imagine that we are using a connection of 2 data sets in an access control system, and the result of one of them turns out to be empty. In this case, the ISNULL construction in the request text of this set will not help us, because it will return an empty selection. There will be no NULL values that can be converted to 0, there will be nothing there at all. NULL appears only after the data sets are connected, and the layout system is responsible for this. The remaining solution is to use calculated fields. In our example, we will have to create a calculated field “Price” and enter the expression ISNULL(Price,0) into its expression. By the time the calculated field expressions fire, the queries have already been joined, so we will already have a "Price" field, and it will contain NULL. By creating a calculated field with the same name, we are essentially overriding the field that the datasets returned to us.

When working with queries, any programmer has to interact with empty values in one way or another. What do we mean by empty value?

A null value is either no value or the default value for the data type. With primitive types, everything is quite simple: the default value is some initial value that serves as a starting point.

Types of Null Values

Let's look at the types of empty values that may be encountered or required in a query.

For the Number type, the empty value is zero – 0.
For the String type – an empty string – “”.
For the Date type – January 1st of the first year – 01/01/0001 00:00:00. It is from this date that time is counted in 1C.*
For the Boolean type, the default value is technically False, but logically both values of the type are padded. Therefore, deciding whether an empty value is False or not is based on the logic of a specific algorithm.

*Be careful, outside 1C there are different date counting systems with different starting points.

The missing value fully corresponds only to the type Null. This type contains only one value, which indicates no value.

Similar type Undefined also contains only one value, but Undefined does not mean the absence of data, but only the impossibility of determining the default value for the type. Undefined is the default value for composite types, including those not explicitly defined. For example, a value in a new row of a value table in a column for which the type is not explicitly defined.

Co reference types there is much less uncertainty. All reference types provide a null value. An empty value is the same reference indicating the data type, but without a unique identifier for the specific value. Thanks to this, we can treat an empty link as if it were a regular one and apply all the methods provided by the platform to it, working with it as with a full-fledged value.

Working with Null Values in a Query

Whether you need to explicitly enter a null value into a query result or compare existing values to a null value, you need to know how to describe null values in your query.

Types Number, String, Boolean are described in the request as in the built-in language:

SELECT 0 AS ExampleTypeNumber, "Hello world" AS ExampleTypeString, True AS ExampleTypeBoolean

Undefined, being essentially a primitive type, is described similarly:

Select Batch.Period From Accumulation Register.Batch As Batch Where Remains.DocumentBatch = Undefined

Empty reference values are a little more difficult to define. All reference objects have a predefined service value of EmptyRef. Thanks to this, it is possible to select an empty link in a single way - through the Value function:

Select Value(Directory.Nomenclature.EmptyLink) How to Empty Nomenclature

The possibilities for working with Null values are somewhat richer. Like other primitive types, Null is described in the same way as in the built-in language. In addition, there is a special operator Is Null and a function IsNull.

The Is Null operator allows you to create a logical expression that compares the selected value with the Null value.
The IsNull function returns the first argument if it is not Null, and the second argument otherwise.

Expressions that define empty values can be used in any query sections that support expressions. For example, you can add an empty link to the Select section or a Null check to the Condition.

Practical examples

Using the Value function

Select Products.Link As Nomenclature, Products.Link = Value(Directory.Nomenclature.EmptyLink) Like ThisLinkEmpty From TueProducts As TueProducts

Using the Is Null operator

Select Products.Link As Nomenclature, Products.Link Is Null Like This LinkEmpty From TueProducts As TueProducts

Null on left or full join

Checking for Null

The example demonstrates a common practical situation when, with a left join, there is no match for the first table in the second. In this case, all fields of the second table will be Null.

Select TueProducts.Link As Nomenclature, Remains.QuantityRemaining As Quantity, Remains.QuantityRemaining Is Null As NoRemaining From TueProducts as TueProducts Left Connection RegisterAccumulations.ProductsInWarehouses.Remains As Remains By TueProducts.Link = Remains.Nomenclature

Handling Null Values

Modification of the previous query to demonstrate a common technique for obtaining some default values to replace missing ones. In this example, using the IsNull function, the missing remainder value is replaced with a logically correct 0.

Select TueProducts.Link As Nomenclature, IsNull (Remaining.QuantityRemaining, 0) As Quantity From TueProducts as TueProducts Left Connection RegisterAccumulations.ProductsInWarehouses.Remains As Remains By TueProducts.Link = Remains.Nomenclature

In this article, we looked at various types of empty values and their properties, studied ways to define different types of empty values in queries, and in the practical part we were convinced of the ease of application of the material discussed.