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