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