home > Internet > 1s where is equal to value or null. Using the function isnull()

1s where is equal to value or null. Using the function isnull()

NULL– missing values.
Not to be confused with zero value! 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 values appear in a query in the following situations:
a) An outer join, in which no corresponding record was found in another table (if left - in the second, if right - in the first, if full - in both)
b) Accessing the attributes 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 statement (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
THE END

another example:
Code 1C v 8.x SELECT

CHOICE WHEN AccountingNomenclatureRemains.QuantityRemainder IS NULL THEN 0
ELSE AccountingNomenclatureRemains.QuantityRemaining AS QuantityRemaining
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 in otherwise
Is collapsed SELECT…END, but ISNULL is preferred.
Code 1C v 8.x
CHOOSE
ISNULL(Catalog.Nomenclature.Article, "---") AS Article,
Directory.Nomenclature.Representation AS Nomenclature

another example:
Code 1C v 8.x
CHOOSE
Directory of Nomenclature. Name,
ISNULL(AccountingNomenclatureRemainders.QuantityRemaining, 0) AS QuantityRemaining
FROM
Directory. Nomenclature AS Directory of Nomenclature
LEFT JOIN
Software AccountingNomenclatureRemains.Nomenclature = DirectoryNomenclature.Link
WHERE
ReferenceNomenclature.ThisGroup = FALSE
AT this example all elements of the nomenclature reference book are obtained, after which, for each nomenclature, 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 "Accounting for Items Remains. Quantity of Remains" there will be NULL values for the item for which there were no balances. In order to have the value 0 instead of NULL in the result of the query, we used the ISNULL () function, which will perform the desired replacement.

ISNULL differs from SELECT for the following reasons:
a) When IS NULL, the query is better readable (simpler)
b) When ISNULL, if a complex expression is being checked, then it works faster, since it is evaluated once
c) When IS NULL, the replacement expression is converted to the type of the expression being checked if it has the type String (length) or Number (length).

You cannot check values for NULL by ordinary equality, because SQL has three-valued logic - True, False, NULL, and the result of such a comparison will be UNKNOWN, which is similar to FALSE in 1C 8.0.
NULL<>0, so for left outer joins, sp. 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 NULL

27.06.2017

NULL, ISNULL() and IS NULL in 1C queries

What is NULL

NULL as a result of the request, means the absence of a value (it is not an empty value, not null, not a null reference).
For example, as a result of joining query tables, no value was found in one of the tables when linking by join fields. Or the request refers to a non-existent attribute (property).

NULL is not equal to NULL !

If you want to impose a selection (condition) with a check for NULL in a 1C query, then a construction like "WHERE VT.Field1 = NULL" will always return FALSE! To check, you should use a special operator

THERE IS NULL in the 1C request

To test a value (or to work with values) for NULL, you must use the following construction "WHERE VT.Field1 IS NULL" Or another example, use in the construction CHOICE"CHOICE | WHEN VT.Field1 IS NULL | THEN" It is worth noting that the function of checking a field for NULL is one of the most resource-intensive in 1C queries. Therefore, if you want to use it in a query condition, consider whether it is possible to replace such a check inner connection(a type of join in queries, in which only records that are present in both tables remain in the selection).

ISNULL() function in 1C query

Function ISNULL() allows you to replace the missing value in the request field with the specified value (standard value, stub).
For example, if there is no information about the cost of the goods, then we indicate it equal to 0 (zero) "CHOOSE | HAVE NULL(T.Price, 0) AS Price | FROM Remaining Goods AS VT"

Conclusion

Write queries correctly and optimally. Because a bad request is a brake and shit code)

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.

IS NULL used in the SELECT statement (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
THE END

another example:
Code 1C v 8.x SELECT
CHOICE WHEN AccountingNomenclatureRemains.QuantityRemainder IS NULL THEN 0
ELSE AccountingNomenclatureRemains.QuantityRemaining AS QuantityRemaining
FROM

WHERE

Function ISNULL (value, ResultIFNULL) returns the value of its first parameter if it is non-null, and the value of its second parameter otherwise
Is collapsed SELECT…END, but ISNULL is preferred.
Code 1C v 8.x
CHOOSE
ISNULL(Catalog.Nomenclature.Article, "---") AS Article,
Directory.Nomenclature.Representation AS Nomenclature

another example:
Code 1C v 8.x
CHOOSE
Directory of Nomenclature. Name,
ISNULL(AccountingNomenclatureRemainders.QuantityRemaining, 0) AS QuantityRemaining
FROM
Directory. Nomenclature AS Directory of Nomenclature
LEFT JOIN
Software AccountingNomenclatureRemains.Nomenclature = DirectoryNomenclature.Link
WHERE
ReferenceNomenclature.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 "Accounting for Items Remains. Quantity of Remains" there will be NULL values for the item for which there were no balances. In order to have the value 0 instead of NULL in the result of the query, we used the ISNULL () function, which will perform the desired replacement.

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 normal 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 produce arithmetic operations over 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 function ISNULL() 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 bit depth).

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

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 whole class 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 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 read trial version lessons, full-fledged lessons are.

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. BUT all empty fields of a composite type have a special meaning UNDEFINED .

FROM UNDEFINED we also meet for the first time. Meaning UNDEFINED used when you want to use an empty value 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, THE 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 ISNULL 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:

Excellent. 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 don’t have it at all, differs from the situation for the São 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 OF:

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 ISNULL 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 ISNULL 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, THE 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.