Разбирая вопросы на форумах 1С, мы часто сталкиваемся с проблемой, когда SQL-запрос, прекрасно работающий в обычной консоли запросов, вызывает ошибку при помещении его в Систему Компоновки Данных (СКД) 1С. Одна из таких ситуаций возникает при использовании ключевых слов РАЗЛИЧНЫЕ (DISTINCT) и УПОРЯДОЧИТЬ ПО (ORDER BY), особенно если поле, по которому происходит упорядочивание, не включено в итоговую выборку данных.
Рассмотрим типовую ситуацию: у нас есть запрос, который выбирает уникальные записи и сортирует их по определенному полю. В консоли запросов 1С этот запрос выполняется без проблем. Однако, как только мы переносим его в набор данных СКД, система выдает ошибку, сообщающую, что "В запросе с предложением РАЗЛИЧНЫЕ все поля, по которым происходит упорядочивание, должны присутствовать в списке выборки". Это происходит, даже если мы явно добавили поле сортировки в список выбираемых полей во временной таблице. Давайте вместе выясним причину такого поведения и предложим эффективные решения.
Вот пример исходного запроса, который вызывает проблему:
ВЫБРАТЬ РАЗЛИЧНЫЕ ПЕРВЫЕ 20
РегистрСведений1.Период КАК Период,
РегистрСведений1.СопроводительныйЛист,
РегистрСведений1.Счетчик,
РегистрСведений1.ИДОперации
ПОМЕСТИТЬ ВТ
ИЗ
РегистрСведений.РегистрСведений1.СрезПоследних КАК РегистрСведений1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РегистрСведений2 КАК РегистрСведений2
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РегистрСведений3 КАК РегистрСведений3
ПО РегистрСведений2.Счетчик = РегистрСведений3.Счетчик
ПО РегистрСведений1.Счетчик = РегистрСведений2.Счетчик
УПОРЯДОЧИТЬ ПО
РегистрСведений1.Период
Ошибка "В запросе с предложением РАЗЛИЧНЫЕ все поля, по которым происходит упорядочивание, должны присутствовать в списке выборки" указывает на то, что оптимизатор СКД по какой-то причине "не видит" поле Период в финальной выборке, даже если оно присутствует в тексте запроса, помещенного в набор данных СКД.
Основная причина такого поведения кроется в особенностях работы оптимизатора СКД. Система Компоновки Данных имеет собственный механизм для анализа и модификации исходного текста запроса. Этот оптимизатор стремится повысить производительность и адаптировать запрос под текущие настройки отчета (отборы, группировки, порядок). В процессе этой оптимизации СКД может "решить", что поле, используемое только для упорядочивания во временной таблице или на промежуточном шаге, но не задействованное в явной выборке или группировках конечного отчета, не является необходимым. Как следствие, оно может быть исключено из итогового запроса, который фактически выполняется в базе данных.
Это особенно актуально, когда вы используете пакет запросов, где первый запрос помещает данные во временную таблицу (ПОМЕСТИТЬ ВТ), а второй запрос выбирает данные из этой временной таблицы для отображения в отчете СКД. Если в первом запросе поле Период используется для упорядочивания, но во втором запросе (который является "видимым" для СКД) оно не выбрано, СКД может его отбросить.
Давайте рассмотрим несколько проверенных способов, которые помогут нам решить эту проблему и заставить СКД корректно обрабатывать запросы с РАЗЛИЧНЫЕ и УПОРЯДОЧИТЬ ПО.
Самый простой и часто помогающий способ, подтвержденный опытом других разработчиков, – это включить поле, по которому происходит упорядочивание, в основную выборку набора данных СКД. Это необходимо сделать не только в запросе, который формирует временную таблицу (если она используется), но и в запросе, который выбирает данные из этой временной таблицы для дальнейшей обработки СКД. Это служит сигналом для оптимизатора СКД, что поле "используется" и не должно быть исключено из конечного запроса.
Например, если ваш набор данных СКД состоит из двух пакетов запросов, как в исходном примере, то вам нужно убедиться, что поле Период присутствует в выборке обоих пакетов:
// ПЕРВЫЙ ПАКЕТ: Формирование временной таблицы
ВЫБРАТЬ РАЗЛИЧНЫЕ ПЕРВЫЕ 20
РегистрСведений1.Период КАК Период, // <-- Явно включено
РегистрСведений1.СопроводительныйЛист,
РегистрСведений1.Счетчик,
РегистрСведений1.ИДОперации
ПОМЕСТИТЬ ВТ
ИЗ
РегистрСведений.РегистрСведений1.СрезПоследних КАК РегистрСведений1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РегистрСведений2 КАК РегистрСведений2
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РегистрСведений3 КАК РегистрСведений3
ПО РегистрСведений2.Счетчик = РегистрСведений3.Счетчик
ПО РегистрСведений1.Счетчик = РегистрСведений2.Счетчик
УПОРЯДОЧИТЬ ПО
РегистрСведений1.Период;
// ВТОРОЙ ПАКЕТ: Выборка данных из временной таблицы для СКД
ВЫБРАТЬ
ВТ.Период, // <-- Обязательно добавляем поле сортировки из ВТ
ВТ.СопроводительныйЛист,
ВТ.Счетчик,
ВТ.ИДОперации
ИЗ
ВТ КАК ВТ
Именно добавление поля ВТ.Период во второй пакет запроса в СКД, как правило, решает проблему. Это связано с тем, что СКД строит свой итоговый запрос на основе того, что выбрано во втором (или последнем) пакете запроса в наборе данных.
СКД по умолчанию старается автоматически заполнить настройки полей, параметров и отборов. Иногда это может приводить к нежелательным оптимизациям. Мы можем получить полный контроль над тем, какие поля выбираются, как они упорядочиваются и как используются, отключив опцию "Автозаполнение" на закладке "Наборы данных" в схеме компоновки данных. После этого вам придется вручную указать все необходимые поля в закладке "Поля" и настроить их роли.
Если мы отключим автозаполнение, то при работе с полями набора данных, СКД не будет самостоятельно исключать поля, основываясь на своем внутреннем алгоритме оптимизации. Это дает нам возможность явно указать все поля, которые должны присутствовать в итоговом запросе, даже если они используются лишь для внутренней сортировки.
Чтобы точно понять, какие изменения внесла СКД в наш запрос и почему возникла ошибка, нам необходимо просмотреть текст результирующего запроса, который формирует СКД. Это является критически важным шагом в процессе отладки сложных отчетов.
Для этого существуют различные инструменты:
Анализируя этот итоговый запрос, мы сможем увидеть, было ли поле Период исключено СКД, или возникла другая, более глубокая проблема с синтаксисом или логикой запроса.
Если поле не предполагается к выводу в самом отчете, но его наличие в запросе критически важно (например, для упорядочивания или отбора), мы можем установить для него признак "Обязательное" в настройках поля набора данных СКД. Это можно сделать на вкладке "Наборы данных" –> "Поля". Выбираем нужное поле и в его свойствах устанавливаем флаг "Обязательное". Этот признак сигнализирует оптимизатору СКД, что данное поле ни при каких обстоятельствах не должно быть исключено из итогового запроса, независимо от настроек отчета или его использования в выводе.
Это является хорошей практикой для полей, которые участвуют в логике запроса (отбор, сортировка, связи), но не отображаются пользователю напрямую.
Важно понимать, что упорядочивание в СКД может осуществляться как на уровне языка запросов (если вы явно используете УПОРЯДОЧИТЬ ПО), так и средствами самой системы компоновки данных. Каждая группировка в отчете СКД может иметь собственное упорядочивание, и если в глобальном упорядочивании или упорядочивании конкретной группировки не указаны поля, СКД будет использовать поля группировки по умолчанию. Если поле является ссылочным, СКД может упорядочивать по полям, по которым следует упорядочивать связанную таблицу.
Если мы хотим, чтобы упорядочивание, заданное в запросе, было безусловно сохранено, поле сортировки должно присутствовать в выборке и быть доступно СКД для использования в дальнейших настройках.
Иногда СКД может некорректно определять типы данных полей, особенно тех, что приходят из временных таблиц. Это может привести к ошибкам или некорректной работе отбора и сортировки. Для того чтобы СКД правильно "поняла" типы данных полей, особенно при использовании менеджера временных таблиц, желательно явно указывать типы с помощью функций ВЫРАЗИТЬ или ЕСТЬNULL в тексте запроса:
ВЫБРАТЬ РАЗЛИЧНЫЕ ПЕРВЫЕ 20
ВЫРАЗИТЬ(РегистрСведений1.Период КАК ДАТА) КАК Период, // Явно указываем тип ДАТА
ВЫРАЗИТЬ(РегистрСведений1.СопроводительныйЛист КАК СТРОКА(100)) КАК СопроводительныйЛист,
ЕСТЬNULL(РегистрСведений1.Счетчик, 0) КАК Счетчик, // Указываем тип и значение по умолчанию
РегистрСведений1.ИДОперации
ПОМЕСТИТЬ ВТ
// ... остальная часть запроса
Такое явное указание помогает СКД корректно интерпретировать поля и предотвращает потенциальные ошибки, связанные с несовместимостью типов.
Таким образом, расхождение в работе запроса в консоли и СКД часто возникает из-за "интерпретатора" СКД, который оптимизирует запрос. Использование явных указаний для полей (включение в выборку, указание типов, установка флага "Обязательное") и анализ результирующего запроса СКД являются ключевыми для решения таких проблем. Надеемся, что эти рекомендации помогут вам успешно решать подобные задачи при работе с Системой Компоновки Данных.