Как найти null в запросе

Содержание:

1.       Тип значения Null

2.       Методы Есть Null и ЕстьNull 

1.    Тип значения Null

Тип значения Null – это переменная или поле с отсутствующим значением. Если реквизит объекта имеет один определенный тип, например тип «Справочник.Договоры», то незаполненное значение будет равно пустой ссылке в запросе «Справочник.Договоры.ПустаяСсылка». Если реквизит имеет мультитип, то незаполненное значение данного реквизита будет равно значению «Неопределено», так как используемые в этом реквизите типы известны, но неизвестно, какой из пустых типов нужно использовать в данном случае. Для примера, в документе «ЗаказКлиента» реквизит «ДокументОснование» может быть и «ЗаданиеТорговомуПредставителю» и «КоммерческоеПредложениеКлиенту». В случаях же, когда вообще неизвестно, какие типы мы хотим получить, значением свойства является Null. В качестве примера, в котором в запросе есть Null, можно рассмотреть левое соединение таблиц, когда в присоединяемой таблице отсутствуют значения, удовлетворяющие условию соединения.

Пример кода: присоединение к таблице контрагентов таблицы договоров

Рис. 1 Левое соединение таблиц

Если у контрагента отсутствует договор, то при выполнении запроса получим следующие значения:

Как видим в запросе в 1С есть Null и это отображается пустой строкой.

В запросе сравнение любого значения с типом значения Null дает результат Ложь. Даже условие Null = Null является ложным.  

2.    Методы Есть Null и ЕстьNull

Для правильной обработки данных ситуаций созданы такие методы, как:

·         Есть Null – это выражение условия, проверяющее, является ли выбранное значение типом значения Null. При проверке следует применять его. Сравнение вида (ДоговорКонтрагента.Ссылка = Null) не даст корректного результата.

Пример кода:

Рис. 2 Пример кода для проверки типа значения Null

Для корректной работы запроса обязательно следует отслеживать места, где есть Null в запросе 1С, и заменять данное значение на какой-то определенный тип.

·         ЕстьNull
Этот метод позволяет менять значение Null на нужное нам значение.

Пример кода для запроса в 1С с ЕстьNull

Рис. 3 Код для ЕстьNull в запросе

Отсутствующую строку с помощью метода ЕстьNull в 1С можно заменить на пустую строку, отсутствующую цифру на ноль, ссылку на пустую ссылку, дату на пустую дату. Например ЕстьNull(“ДоговорыКонтрагентов.Номер”, “”), ЕстьNull(“ДоговорыКонтрагентов.СуммаДоговора”, 0), ЕстьNull(“ДоговорыКонтрагентов.Партнер”, Значение(Справочник.Партнеры.ПустаяСсылка)), ЕстьNull(“ДоговорыКонтрагентов.Дата”, ДАТАВРЕМЯ(1,1,1,0,0,0)).

Тип значения Null также может уже содержаться в используемом в запросе реквизите, попав туда в результате некорректной записи программным кодом, смене типа реквизита в конфигураторе либо при неправильном обмене данными между базами. В этих случаях тоже можно воспользоваться выражением ЕстьNull в запросе, но лучше исправлять такие реквизиты.

Специалист компании «Кодерлайн»

Александр Суворов

NULL – отсутствующие значения.
Не путать с нулевым значением! NULL – это не число, не равно пробелу, пустой ссылке, Неопределено.

NULL – типообразующее значение, т.е. есть тип NULL и единственное значение этого типа.

NULL значения появляются в запросе в следующих ситуациях:
а) Внешнее соединение, при котором не было найдено соответствующей записи в другой таблице (при левом – во второй, при правом – в первой, при полном – в обоих)
б) Обращение к реквизитам элементов для группы и наоборот.
в) NULL в списке полей выборки (ВЫБРАТЬ)
г) Обращение к реквизитам для битой ссылки

ЕСТЬ NULL используется в операторе ВЫБРАТЬ (как бы проверя, что значение это есть пустое ( Значение ЕСТЬ NULL )):

Код 1C v 8.х

 	

ВЫБОР 

 КОГДА Значение ЕСТЬ NULL ТОГДА РезультатЕслиNULL 

 ИНАЧЕ Значение 

КОНЕЦ 	

   

еще пример:

Код 1C v 8.х

  ВЫБРАТЬ

    СправочникНоменклатуры.Наименование,

    ВЫБОР КОГДА УчетНоменклатурыОстатки.КоличествоОстаток ЕСТЬ NULL ТОГДА 0 

    ИНАЧЕ УчетНоменклатурыОстатки.КоличествоОстаток КАК КоличествоОстаток

ИЗ

    Справочник.Номенклатура КАК СправочникНоменклатуры

        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.УчетНоменклатуры.Остатки КАК УчетНоменклатурыОстатки

        ПО УчетНоменклатурыОстатки.Номенклатура = СправочникНоменклатуры.Ссылка

ГДЕ

    СправочникНоменклатуры.ЭтоГруппа = ЛОЖЬ    

Функция ЕСТЬNULL (значение, РезультатЕслиNULL) возвращает значение своего первого параметра, в случае если он не равен NULL, и значение второго параметра в противном случае
Является свернутым ВЫБОР…КОНЕЦ, но ЕСТЬNULL предпочтительнее.

Код 1C v 8.х

 

ВЫБРАТЬ

	ЕСТЬNULL(Справочник.Номенклатура.Артикул, "---") КАК Артикул,

	Справочник.Номенклатура.Представление КАК Номенклатура

   

еще пример:

Код 1C v 8.х

 

ВЫБРАТЬ

    СправочникНоменклатуры.Наименование,

    ЕСТЬNULL(УчетНоменклатурыОстатки.КоличествоОстаток, 0) КАК КоличествоОстаток

ИЗ

    Справочник.Номенклатура КАК СправочникНоменклатуры

        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.УчетНоменклатуры.Остатки КАК УчетНоменклатурыОстатки

        ПО УчетНоменклатурыОстатки.Номенклатура = СправочникНоменклатуры.Ссылка

ГДЕ

    СправочникНоменклатуры.ЭтоГруппа = ЛОЖЬ    

В данном примере получаются все элементы справочника номенклатуры, после чего, для каждой номенклатуры из регистра накопления получаются текущие остатки. Т.к. для номенклатуры, по которой отсутствуют остатки, виртуальная таблица остатков не запись вернет, то в результате соединения в поле «УчетНоменклатурыОстатки.КоличествоОстаток» будут значения NULL для номенклатуры, по которой не было остатков. Для того чтобы вместо значения NULL в результате запроса присутствовало значение 0, мы использовали функцию ЕСТЬNULL(), которая осуществит желаемую замену.

ЕСТЬNULL отличается от ВЫБОР по следующим причинам:
а) При ЕСТЬNULL лучше читается запрос (проще)
б) При ЕСТЬNULL, если проверяется сложное выражение, то работает быстрее, поскольку вычисляется один раз
в) При ЕСТЬNULL выражение замены приводится к типу проверяемого выражения, если оно имеет тип Строка (длина) или Число (разрядность).

Нельзя проверять значения на NULL обычным равенством, потому что в SQL действует трехзначная логика – Истина, Ложь, NULL, и результатом такого сравнения будет UNKNOWN, что в 1С 8.0 аналогично ЛОЖЬ.
NULL <> 0, поэтому при левых внешних соединениях спр. Номенклатура с таблицами остатков, цен, Контрагентов со взаиморасчетами при отсутствии таких записей там будет NULL, который не равен 0. Лучшее решение – ЕСТЬNULL

NULL – это не что иное, как отсутствие значения. Многие путают его со значением «0» типа число, пустой ссылкой на какой-либо объект или же с пустой строкой. Из-за этого заблуждения возникает много ошибок.

Значение NULL будет появляться в том случае, если в запросе будет обращение к несуществующему полю, свойству или к битой ссылке.

Язык запросов в 1С основан на SQL, который не позволяет проверять на значение NULL обычным равенством. Ниже описаны два способа проверки на NULL в 1С 8.3.

Функция ЕСТЬNULL()

Функция языка запросов 1С 8.3 ЕСТЬNULL() имеет два входных параметра:

• проверяемое выражение;
• выражение замены.

Если проверяемое значение будет NULL, то эта функция вернет значение выражения замены. Если же значение будет отлично от NULL, то вернется само проверяемое выражение.

Ниже рассмотрен пример. В нем выбираются все номенклатурные позиции табличной части товара из документа «Поступление товаров и услуг». При помощи левого соединения каждой номенклатуре проставляется последняя цена из регистра сведений «Цены номенклатуры».

В данном случае может возникнуть такая ситуация, что для какой-либо позиции может просто не быть цены в регистре. В таком случае функция ЕСТЬNULL вернет нам привычный ноль. Если ей не воспользоваться, то при попытке произвести арифметические операции над полем «Цена» со значением NULL мы получим ошибку.

ВЫБРАТЬ
Товары.Номенклатура КАК Товар,
ЕСТЬNULL(Цены.Цена, 0) КАК АктуальнаяЦена
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО Товары.Номенклатура = Цены.Номенклатура
ГДЕ
Товары.Ссылка = &СсылкаНаДокумент

ЕСТЬ NULL в операторе ВЫБОР

Аналогом функции ЕСТЬNULL() является «ЕСТЬ NULL», которая используется в операторе ВЫБОР и проверяет, является ли значение NULL. «ЕСТЬ» в данном случае подразумевает равенство и запрос предыдущего примера будет выглядеть следующим образом:

ВЫБРАТЬ
Товары.Номенклатура КАК Товар,
ВЫБОР
КОГДА Цены.Цена ЕСТЬ NULL
ТОГДА 0
ИНАЧЕ Цены.Цена
КОНЕЦ КАК АктуальнаяЦена
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних КАК Цены
ПО Товары.Номенклатура = Цены.Номенклатура
ГДЕ
Товары.Ссылка = &СсылкаНаДокумент

Отличия функции ЕСТЬNULL() от ЕСТЬ NULL

Как вы могли увидеть из предыдущих примеров, в обоих случаях запрос возвращает одни и те же данные. Функция ЕСТЬNULL() является сокращенным вариантом ВЫБОР КОГДА … ЕСТЬ NULL … КОНЕЦ, но она все же будет предпочтительнее по следующим причинам:

1. Функция EСТЬNULL() оптимизирует запрос. Она считывается один раз, поэтому при проверке сложного выражения, запрос отработает быстрее.
2. Функция EСТЬNULL() сокращает конструкцию, за счет чего запрос становится более читабельным.
3. При выполнении функции EСТЬNULL() выражение замены приводится к типу проверяемого выражения для типов строковых типов (к длине строки) и числовых (к разрядности).

Язык запросов 1С 8.3 для начинающих программистов: функции и операторы для работы с типами (ТИПЗНАЧЕНИЯ, ТИП, ССЫЛКА, ЕСТЬNULL, ВЫРАЗИТЬ)

Автор уроков и преподаватель школы: Владимир Милькин

Давайте вспомним, что каждый реквизит (свойство, поле) справочника, документа или любого другого прикладного объекта имеет свой тип. И этот тип мы можем посмотреть в конфигураторе:

В языке запросов существует целый класс функций и операторов для работы с типами реквизитов. Давайте рассмотрим их.

Функция ТИПЗНАЧЕНИЯ

Эта функция принимает один параметр (значение) и возвращает его тип. Для описанного на картинке (выше) реквизита Вкус справочника Еда вернётся следующее:

ВЫБРАТЬ
    Наименование,
    Вкус,
    ТИПЗНАЧЕНИЯ(Вкус)
ИЗ
    Справочник.Еда

Если мы запросим тип поля Наименование, то, как и ожидается, получим Строка:

ВЫБРАТЬ
    Наименование,
    ТИПЗНАЧЕНИЯ(Наименование)
ИЗ
    Справочник.Еда

А теперь давайте рассмотрим реквизит ОтличительныйПризнак у справочника Города:

Вы видите, что этот реквизит может иметь один из нескольких типов: Строка, Справочник.Вкусы, Справочник.Цвета. Такой тип реквизитов называется СОСТАВНЫМ.

Если мы попытаемся заполнить значение такого реквизита в режиме 1С:Предприятие, то система спросит нас, какого типа будет вводимое значение:

И только после нашего выбора позволит ввести значение выбранного типа.

Таким образом, элементы справочника одного вида (Справочник.Города) смогут хранить в одном и том же реквизите (ОтличительныйПризнак) значения разных типов (Строка, Цвета или Вкусы).

Вы можете убедиться в этом сами пощёлкав по элементам справочника Города в режиме 1С:Предприятие. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь.

Здесь значение отличительного признака является элементом справочника Вкусы:

Здесь строкой:

А здесь вообще элементом справочника Цвета:

Вот какие возможности открывает перед нами составной тип данных!

Интересно, как поведёт себя функция ТИПЗНАЧЕНИЯ на реквизите ОтличительныйПризнак, имеющий составной тип данных:

ВЫБРАТЬ
    Наименование,
    ОтличительныйПризнак,
    ТИПЗНАЧЕНИЯ(ОтличительныйПризнак)
ИЗ
    Справочник.Города

Это уже очень интересно. Давайте разбираться с каждой строкой в отдельности.

Тип значения отличительного признака для элемента Россия равен NULL. Мы впервые сталкиваемся с этим типом. Значения данного типа используются исключительно для определения отсутствующего значения при работе с базой данных.

Так и есть, ведь элемент Россия является группой, а не обычным элементом справочника Города, поэтому у него отсутствует поле ОтличительныйПризнак. А тип у отсутствующего значения, как мы прочитали выше, всегда равен NULL.

Идём дальше.

Тип значения отличительного признака для Перми равен Вкусы. Так и есть, ведь значение отличительного признака забитое в базе для города Пермь является ссылкой на элемент справочника Вкусы.

Для Красноярска тип признака равен Цвета, потому что значение выбранное в базе является ссылкой на элемент справочника Цвета.

Для Воронежа тип признака равен Строка, потому что значение введенное в базе является обычной строкой.

Индия снова группа, поэтому значение отсутствует. А тип у отсутствующего значения, как мы помним, равен NULL.

Далее всё аналогично, кроме Сан-Паулу. Это не группа, а обычный элемент справочника (город), но тип его значения пустой. Как так?

А дело вот в чём. Если вы зайдёте в элемент справочника Города с наименованием Сан-Паулу, то увидите, что поле ОтличительныйПризнак совершенно никак не заполнено. Оно пустое. А все незаполненные поля составного типа имеют специальное значение НЕОПРЕДЕЛЕНО.

С НЕОПРЕДЕЛЕНО мы также сталкиваемся впервые.

Значение НЕОПРЕДЕЛЕНО применяется, когда необходимо использовать пустое значение, не принадлежащее ни к одному другому типу. Это как раз наша ситуация.

А тип для значения, которое не принадлежит ни к одному из типов, как вы уже наверное догадались отсутствует.

Функция ТИП

Она принимает всего один параметр — имя примитивного типа (СТРОКА, ЧИСЛО, ДАТА, БУЛЕВО), либо имя таблицы, тип ссылки которой нужно получить.

Результатом данной конструкции будет значение типа Тип для указанного типа.

Звучит туманно, не правда ли?

Давайте рассмотрим применение данной конструкции и всё сразу станет на свои места.

Пусть нам требуется отобрать все записи справочника Города, у которых составной реквизит ОтличительныйПризнак имеет значение типа СТРОКА:

ВЫБРАТЬ
    Наименование,
    ОтличительныйПризнак,
    ТИПЗНАЧЕНИЯ(ОтличительныйПризнак)
ИЗ
    Справочник.Города
ГДЕ
    ТИПЗНАЧЕНИЯ(ОтличительныйПризнак) = ТИП(СТРОКА)

Теперь давайте отберём все записи, у которых значения реквизита ОтличительныйПризнак являются ссылками на элементы справочника Цвета (таблица Справочник.Цвета):

ВЫБРАТЬ
    Наименование,
    ОтличительныйПризнак,
    ТИПЗНАЧЕНИЯ(ОтличительныйПризнак)
ИЗ
    Справочник.Города
ГДЕ
    ТИПЗНАЧЕНИЯ(ОтличительныйПризнак) = ТИП(Справочник.Цвета)

Отступление

Как вы помните, некоторые элементы справочника Города не имеют реквизита ОтличительныйПризнак. Функция ТИПЗНАЧЕНИЯ для таких элементов выдаёт NULL.

Как можно сделать отбор таких элементов в запросе? Для этого предусмотрен специальный логический оператор ЕСТЬ NULL (не путать с функцией ЕСТЬNULL, которую мы рассмотрим ниже). Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь.

Вот пример его использования:

ВЫБРАТЬ
    Наименование,
    ОтличительныйПризнак,
    ТИПЗНАЧЕНИЯ(ОтличительныйПризнак)
ИЗ
    Справочник.Города
ГДЕ
    ОтличительныйПризнак ЕСТЬ NULL

Отлично.

Но есть и такие элементы (Сан-Паулу), у которых реквизит ОтличительныйПризнак (составного типа) просто не заполнен и равен специальному значению НЕОПРЕДЕЛЕНО.

Чтобы отобрать такие записи следует использовать другую конструкцию:

ВЫБРАТЬ
    Наименование,
    ОтличительныйПризнак,
    ТИПЗНАЧЕНИЯ(ОтличительныйПризнак)
ИЗ
    Справочник.Города
ГДЕ
    ОтличительныйПризнак = НЕОПРЕДЕЛЕНО

Но сравнение с НЕОПРЕДЕЛЕНО для определения пустых (не заполненных) реквизитов будет работать только для составных типов.

Кстати, у логического оператора ЕСТЬ NULL форма отрицания выглядит следующим образом:

ВЫБРАТЬ
    Наименование,
    ОтличительныйПризнак
ИЗ
    Справочник.Города
ГДЕ
    ОтличительныйПризнак ЕСТЬ НЕ NULL

Логический оператор ССЫЛКА

Оператор ССЫЛКА позволяет проверить, является ли значение выражения, указанного слева от него, ссылкой на таблицу, указанную справа.

К примеру, давайте выберем из справочника Города только те записи, у которых значение составного реквизита ОтличительныйПризнак являются ссылкой на элемент справочника Вкусы:

ВЫБРАТЬ
    Наименование,
    ОтличительныйПризнак,
    ТИПЗНАЧЕНИЯ(ОтличительныйПризнак)
ИЗ
    Справочник.Города
ГДЕ
    ОтличительныйПризнак ССЫЛКА Справочник.Вкусы

Как вы помните, эту же задачу мы могли бы решить используя ТИПЗНАЧЕНИЯ и ТИП:

ВЫБРАТЬ
    Наименование,
    ОтличительныйПризнак,
    ТИПЗНАЧЕНИЯ(ОтличительныйПризнак)
ИЗ
    Справочник.Города
ГДЕ
    ТИПЗНАЧЕНИЯ(ОтличительныйПризнак) = ТИП(Справочник.Вкусы)

Функция ЕСТЬNULL

Функция предназначена для замены значения NULL на другое значение.

Мы помним, что значение NULL возвращается в том случае, если запрашиваемый реквизит (поле, свойство) не существует.

Как например, реквизит ОтличительныйПризнак для групп справочника Города:

ВЫБРАТЬ
    Наименование,
    ОтличительныйПризнак
ИЗ
    Справочник.Города

Функция ЕСТЬNULL поможет нам вывести другое значение в том случае, если это значение равно NULL. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь. Пусть в данном случае это будет строка «Такого реквизита нет!»:

ВЫБРАТЬ
    Наименование,
    ЕСТЬNULL(ОтличительныйПризнак, "Такого реквизита нет!")
ИЗ
    Справочник.Города

Получается, что если первый параметр функции ЕСТЬNULL не равен NULL, то возвращается он. Если же он равен NULL, то возвращается второй параметр.

Функция ВЫРАЗИТЬ

Эта функция предназначена только для полей, имеющих составной тип. Отличным примером такого поля является свойство ОтличительныйПризнак у элементов справочника Города.

Как мы помним, составные поля могут быть одного из нескольких типов, указанных в конфигураторе.

Для поля ОтличительныйПризнак такими допустимыми типами являются СТРОКА, Справочник.Цвета и Справочник.Вкусы.

Иногда возникает необходимость привести значения составного поля к какому-либо определенному типу.

Давайте приведём все значения поля ОтличительныйПризнак к типу Справочник.Цвета:

ВЫБРАТЬ
    Наименование,
    ВЫРАЗИТЬ(ОтличительныйПризнак КАК Справочник.Цвета)
ИЗ
    Справочник.Города

В результате, все значения элементов, которые имели тип Справочник.Цвета, остались заполненными и оказались приведенными к указанному типу. Все значения других типов (СТРОКА, Справочник.Вкусы) теперь стали равны NULL. В этом состоит особенность приведения типа при помощи функции ВЫРАЗИТЬ.

Приводить тип можно или к примитивному типу (БУЛЕВО, ЧИСЛО, СТРОКА, ДАТА) или к ссылочному типу. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь. Но тип, к которому делается приведение, обязательно должен входить в список типов для данного составного поля, иначе система выдаст ошибку.

Пройдите тест