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