При разработке отчетов на базе Системы компоновки данных (СКД) программисты часто сталкиваются с ситуацией, когда один и тот же запрос в «Консоли запросов» выдает корректные данные, а в СКД возвращает пустой результат. Особенно часто это проявляется при работе с виртуальными таблицами регистра бухгалтерии и использовании сложных условий в их параметрах. Рассмотрим подробнее, почему так происходит и как решить эту проблему.
Проанализируем типичный запрос к виртуальной таблице ХозрасчетныйОборотыДтКт, который вызывает затруднения. В данном случае условие накладывается на счет кредита непосредственно внутри параметров виртуальной таблицы:
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ХозрасчетныйОборотыДтКт.ПериодГод, ГОД) КАК Период,
ХозрасчетныйОборотыДтКт.СуммаОборот КАК Сумма,
ХозрасчетныйОборотыДтКт.СубконтоДт2 КАК Склад,
ХозрасчетныйОборотыДтКт.СчетКт КАК СчетКт
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(
&НачалоПериода,
&КонецПериода,
Авто,
СчетДт В ИЕРАРХИИ (&Счет43_02),
&СубконтоСчета43_02,
НЕ СчетКт В ИЕРАРХИИ (&Счет43_02),
,
СубконтоДт2 В (&Склады)
) КАК ХозрасчетныйОборотыДтКт
Проблема кроется в условии НЕ СчетКт В ИЕРАРХИИ (&Счет43_02). Как только оно исключается из запроса или заменяется на более простое, данные начинают выбираться. Разберемся, какие механизмы платформы 1С приводят к такому поведению.
Первое, что нам необходимо понять: СКД никогда не выполняет запрос в том виде, в котором мы его написали в конструкторе. Перед отправкой на СУБД происходит процесс Query Transformation (трансформация запроса). Движок компоновки перестраивает текст, чтобы внедрить пользовательские настройки, отборы и параметры.
Когда мы используем оператор В ИЕРАРХИИ, платформа генерирует сложный SQL-код, включающий соединения с временными таблицами, содержащими структуру иерархии плана счетов. Отрицание НЕ перед таким условием внутри параметров виртуальной таблицы часто сбивает оптимизатор СКД с толку. В результате результирующий SQL-запрос может содержать логическую ошибку (например, WHERE 1=0), которая полностью отсекает все записи еще на уровне СУБД.
Для корректной работы условия В ИЕРАРХИИ критически важно, чтобы тип передаваемого значения в параметре &Счет43_02 строго соответствовал типу ПланСчетовСсылка.Хозрасчетный. Если счет не является предопределенным и был создан программно, у него могут быть особенности в структуре ссылочной целостности.
При использовании оператора = платформа выполняет прямое сравнение ссылок. Однако при использовании В ИЕРАРХИИ система строит дерево потомков. Если в иерархии счета есть зацикливания или битые ссылки, механизм поиска может вернуть пустой массив идентификаторов, что при инверсии через НЕ приведет к непредсказуемому результату в СКД.
Чтобы выяснить истинную причину, нам недостаточно обычной консоли запросов. Нам нужно увидеть исполняемый запрос. Для этого выполним следующие шаги:
WHERE. Если мы видим там странные условия или пустые временные таблицы в соединениях, значит, СКД некорректно трансформировала иерархическое условие.Если нам достоверно известно, что исключаемый счет не имеет подчиненных счетов (субсчетов), или нам нужно исключить только конкретный узел, самым надежным решением будет замена иерархического сравнения на оператор равенства.
Заменим НЕ СчетКт В ИЕРАРХИИ (&Счет43_02) на:
НЕ СчетКт = &Счет43_02
Это упрощает работу оптимизатора СКД. Поскольку оператор равенства является базовым, он не требует построения таблиц иерархии и корректно обрабатывается при трансформации запроса. Как показывает практика, в большинстве случаев этого достаточно, чтобы отчет заработал.
Если использование иерархии критично (нам нужно исключить счет со всеми его субсчетами), попробуем вынести условие из параметров виртуальной таблицы в общую секцию ГДЕ.
Для этого сначала убедимся, что поле СчетКт выбрано в списке полей. Схема запроса будет выглядеть так:
ВЫБРАТЬ
...
ХозрасчетныйОборотыДтКт.СчетКт КАК СчетКт
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(&НачалоПериода, &КонецПериода, ...) КАК ХозрасчетныйОборотыДтКт
ГДЕ
НЕ ХозрасчетныйОборотыДтКт.СчетКт В ИЕРАРХИИ (&Счет43_02)
Важное замечание: При использовании этого метода необходимо проверить настройки флага «Автозаполнение» на закладке «Наборы данных». Если флаг включен, СКД может попытаться самостоятельно наложить дополнительные фильтры, что снова приведет к конфликту. В некоторых случаях может потребоваться ручная настройка полей в схеме компоновки.
Проанализируем еще несколько моментов, которые могут влиять на результат:
&Счет43_02 не заполнен (имеет значение ПустаяСсылка), СКД при обработке В ИЕРАРХИИ может оптимизировать запрос до состояния «Ложь», считая, что в пустой ссылке не может быть иерархии. Всегда проверяйте заполненность параметров перед выполнением компоновки.СчетКт правильная роль в СКД. Для виртуальных таблиц бухгалтерских регистров важно, чтобы поля счетов были помечены соответствующим образом, иначе механизм иерархии в настройках отчета может работать некорректно.Таким образом, мы выяснили, что основная причина пустого результата — некорректная интерпретация сложного логического условия НЕ ... В ИЕРАРХИИ внутри параметров виртуальной таблицы в процессе трансформации запроса СКД. Использование прямого равенства или вынос фильтра в секцию ГДЕ являются наиболее эффективными методами обхода данной проблемы.