При работе со списками документов, справочниками или регистрами в 1С:Предприятии 8 часто возникает необходимость отсечь записи, в которых не заполнены те или иные реквизиты. Например, при формировании отчета по путевым листам нам может потребоваться исключить из выборки строки, где не указан водитель.
На первый взгляд задача кажется тривиальной, однако в платформе 1С понятие «пустоты» может принимать разные формы в зависимости от типа данных реквизита. Это может быть пустая ссылка справочника, пустое строковое значение, нулевое число, неопределенный тип данных или системный NULL. В этой статье мы подробно разберем все сценарии фильтрации пустых полей в запросах 1С, изучим особенности работы с составными типами данных, а также рассмотрим решение проблемы «битых ссылок».
Рассмотрим классическую ситуацию, описанную автором темы. Имеется таблица (например, таблица путевых листов или документов перевозок), в которой есть реквизит Водитель, имеющий ссылочный тип СправочникСсылка.Водители. Нам необходимо составить запрос таким образом, чтобы строки с незаполненными водителями не попадали в результат.
Для решения этой задачи воспользуемся системной конструкцией ЗНАЧЕНИЕ(), которая позволяет обращаться к предопределенным элементам и пустым ссылкам метаданных напрямую в теле запроса.
Проанализируем пример запроса, решающий эту задачу:
ВЫБРАТЬ
ПутевыеЛисты.Ссылка КАК ДокументСсылка,
ПутевыеЛисты.Водитель КАК Водитель,
ПутевыеЛисты.Автомобиль КАК Автомобиль
ИЗ
Документ.ПутевойЛист КАК ПутевыеЛисты
ГДЕ
НЕ ПутевыеЛисты.Водитель = ЗНАЧЕНИЕ(Справочник.Водители.ПустаяСсылка)
Разберем, как работает это условие. Конструкция ЗНАЧЕНИЕ(Справочник.Водители.ПустаяСсылка) возвращает специальный пустой идентификатор (состоящий из одних нулей), который записывается в базу данных 1С, когда пользователь очищает поле на форме или оставляет его незаполненным. С помощью оператора НЕ и операции сравнения = мы отсекаем все записи, где значение реквизита совпадает с этой пустой ссылкой.
Очень часто начинающие разработчики путают понятия «пустая ссылка» и значение NULL. Это принципиально разные сущности в СУБД и платформе 1С:
NULL, например, при выполнении левого или правого соединения таблиц, когда для записи из левой таблицы не нашлось подходящей строки в правой таблице.Давайте разберем ситуацию на практическом примере. Представим, что мы соединяем таблицу автомобилей со сведениями об их техосмотре:
ВЫБРАТЬ
Автомобили.Ссылка КАК Автомобиль,
ЕСТЬNULL(Техосмотры.НомерЛиста, "Нет техосмотра") КАК СтатусТехосмотра
ИЗ
Справочник.Автомобили КАК Автомобили
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Техосмотры КАК Техосмотры
ПО Автомобили.Ссылка = Техосмотры.Автомобиль
ГДЕ
Техосмотры.Автомобиль ЕСТЬ NULL
В данном случае условие ГДЕ Техосмотры.Автомобиль ЕСТЬ NULL отберет только те автомобили, по которым в регистре сведений вообще нет записей. Проверить это значение через обычное сравнение (=) или конструкцию ЗНАЧЕНИЕ() невозможно, так как NULL не равен ничему, даже самому себе. Для работы с ним используется специальный оператор ЕСТЬ NULL или противоположный ему ЕСТЬ НЕ NULL.
В процессе эксплуатации базы данных пользователи могут физически удалить элемент справочника (например, элемент справочника водителей), на который уже ссылаются другие документы. В таком случае в базе данных возникает так называемая «битая ссылка». В интерфейсе программы она отображается как <Объект не найден>.... Чтобы избежать появления таких ошибок при очистке информационной базы, можно использовать обработку для выборочного удаления организаций с автоматическим контролем ссылочной целостности.
Опасность битой ссылки заключается в следующем: для сопоставления объектов в разных базах, когда стандартные ссылки повреждены или отсутствуют, можно использовать обработку для переноса данных между похожими конфигурациями 1С с поиском по произвольным полям.
Пустая() вернет для нее значение Ложь, так как ее уникальный идентификатор заполнен и не равен пустому UID.ЗНАЧЕНИЕ(Справочник.Водители.ПустаяСсылка) в запросе также вернет Ложь. Строка с несуществующим водителем попадет в выборку, хотя фактически водителя в системе больше нет.Выясним, как изящно решить эту проблему в запросе. Для того чтобы отсечь битые ссылки, мы можем обратиться «через точку» к любому реквизиту связанной таблицы (например, к полю Ссылка или к Наименование) и проверить результат на NULL. Если запись в связанной таблице физически удалена, СУБД при попытке разыменования ссылки вернет NULL:
ВЫБРАТЬ
ПутевыеЛисты.Ссылка КАК ДокументСсылка,
ПутевыеЛисты.Водитель КАК Водитель
ИЗ
Документ.ПутевойЛист КАК ПутевыеЛисты
ГДЕ
НЕ ПутевыеЛисты.Водитель.Ссылка ЕСТЬ NULL
И НЕ ПутевыеЛисты.Водитель = ЗНАЧЕНИЕ(Справочник.Водители.ПустаяСсылка)
Такая комбинированная проверка гарантирует, что в выборку попадут только реально существующие в информационной базе элементы справочника Водители.
Рассмотрим более сложную ситуацию. Предположим, что реквизит Водитель имеет составной тип данных и может содержать в себе как ссылку на справочник Водители, так и на справочник ФизическиеЛица. Если мы попытаемся использовать стандартную проверку через ЗНАЧЕНИЕ(), нам придется писать сложные ветвления для каждого типа отдельно, а также обрабатывать состояние Неопределено.
Существует красивый и лаконичный способ отфильтровать любые пустые и битые значения составного поля в один прием:
ВЫБРАТЬ
МояТаблица.Ссылка КАК Ссылка,
МояТаблица.Водитель КАК Водитель
ИЗ
Документ.ПутевойЛист КАК МояТаблица
ГДЕ
НЕ МояТаблица.Водитель.Ссылка ЕСТЬ NULL
Давайте разберем, почему эта конструкция работает так эффективно. При обращении к полю Ссылка через точку от реквизита составного типа платформа выполняет неявное левое соединение со всеми таблицами, входящими в этот тип. Если значение реквизита является пустой ссылкой любого типа, значением Неопределено или битой ссылкой, результатом обращения к виртуальному полю Ссылка всегда будет NULL. Таким образом, условие НЕ МояТаблица.Водитель.Ссылка ЕСТЬ NULL разом убирает из выборки все виды «пустоты».
Важное предупреждение по производительности: Несмотря на свое изящество, данный метод создает дополнительную нагрузку на сервер СУБД. На больших таблицах (сотни тысяч или миллионы строк) неявное соединение со множеством таблиц составного типа может привести к сильному падению скорости выполнения запроса. Если вам заранее известен тип данных, который вы хотите проверить, крайне рекомендуется предварительно использовать функцию ВЫРАЗИТЬ():
ВЫБРАТЬ
МояТаблица.Ссылка КАК Ссылка,
МояТаблица.Водитель КАК Водитель
ИЗ
Документ.ПутевойЛист КАК МояТаблица
ГДЕ
НЕ ВЫРАЗИТЬ(МояТаблица.Водитель КАК Справочник.Водители) = ЗНАЧЕНИЕ(Справочник.Водители.ПустаяСсылка)
В зависимости от типа данных поля в 1С, критерии «пустоты» определяются по-разному. Давайте систематизируем эти знания, чтобы вы могли использовать их как готовую шпаргалку при написании кода:
ГДЕ Поле Ссылка <> ЗНАЧЕНИЕ(Справочник.Контрагенты.ПустаяСсылка)
ГДЕ СтроковоеПоле <> ""
ДАТАВРЕМЯ(1, 1, 1) или ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0).
ГДЕ ПолеДата <> ДАТАВРЕМЯ(1, 1, 1)
ГДЕ ЧисловоеПоле <> 0
ИСТИНА, либо ЛОЖЬ. По умолчанию система инициализирует их как ЛОЖЬ.
НЕОПРЕДЕЛЕНО.
ГДЕ ПолеСоставногоТипа <> НЕОПРЕДЕЛЕНО
ЕСТЬ NULL.
ГДЕ ПолеТаблицы ЕСТЬ NULL
Начинающие разработчики часто путают оператор ЕСТЬ NULL и встроенную функцию ЕСТЬNULL(). Несмотря на схожесть написания, они выполняют абсолютно разные задачи и используются в разных частях запроса.
Проанализируем разницу подробнее:
ЕСТЬ NULL (пишется раздельно)
Применяется исключительно в секциях условий запроса (после ключевых слов ГДЕ, ИМЕЮЩИЕ или в условиях соединения ПО). Он предназначен для фильтрации и возвращает логическое значение ИСТИНА или ЛОЖЬ.
Пример использования:
ГДЕ
ОстаткиТоваров.КоличествоОстаток ЕСТЬ NULL
ЕСТЬNULL(Поле, ЗначениеЗамены) (пишется слитно)
Используется в секции выборки полей (после ключевого слова ВЫБРАТЬ) для обработки полученного результата. Эта функция проверяет значение первого параметра, и если оно оказывается равным NULL, заменяет его на значение второго параметра (например, на число 0, пустую строку или пустую дату).
Пример использования:
ВЫБРАТЬ
Товары.Наименование КАК Товар,
ЕСТЬNULL(ОстаткиТоваров.КоличествоОстаток, 0) КАК Остаток
В этом примере, если остатков по товару на складе не обнаружится (в результате левого соединения вернется NULL), в итоговую выборку вместо пустого поля подставится понятное числовое значение 0, с которым в дальнейшем можно будет производить математические операции.
Таким образом, мы детально разобрали все нюансы работы с пустыми значениями в запросах 1С. Понимание разницы между пустой ссылкой, значением NULL и типом Неопределено позволит вам писать эффективные, отказоустойчивые и быстрые запросы даже к самым сложным структурам данных. Для отладки и проверки подобных запросов можно использовать инженерную консоль запросов для 1С.