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