При активной разработке в среде «1С:Предприятие» программисты часто сталкиваются со специфическими синтаксическими ограничениями языка запросов. Одной из классических ошибок при проектировании сложных выборок является сообщение: «В запросе, формирующем временную таблицу, не могут выбираться вложенные таблицы». Эта ошибка ставит в тупик начинающих разработчиков, так как визуально запрос может казаться абсолютно корректным.
В этой статье мы подробно разберем природу возникновения данной ошибки, проанализируем технические особенности работы СУБД с временными таблицами, изучим стандарты разработки фирмы «1С» и рассмотрим несколько эффективных способов решения проблемы на конкретных примерах кода.
Для начала важно разграничить два созвучных, но принципиально разных понятия в языке запросов 1С:
ИЗ или ГДЕ другого запроса. Например: ВЫБРАТЬ ... ИЗ (ВЫБРАТЬ ... ИЗ Таблица) КАК ВложенныйЗапрос.Ссылка.Товары.(Номенклатура, Количество) или попытка неявного обращения к реквизитам табличной части через шапку документа: Документ.ЧекККМВозврат.Товары.Количество.Когда мы пишем запрос, выбирающий данные из шапки документа и пытаемся получить поля его табличной части через конструкцию обращения через точку (например, ЧекККМВозврат.Товары.Количество), платформа 1С воспринимает это как попытку выбрать всю вложенную таблицу целиком. В результате формируется не плоский двумерный массив строк, а сложная иерархическая структура, где в одной строке документа содержится целая вложенная таблица со строками табличной части.
Почему это нельзя поместить во временную таблицу?
Временные таблицы в 1С физически создаются на стороне СУБД (MS SQL Server, PostgreSQL, Oracle и др.) в системной базе данных (например, во временной базе tempdb в MS SQL). Реляционные базы данных работают исключительно с плоскими двумерными таблицами (состоящими из строк и колонок простых типов данных: числа, строки, даты, булево). СУБД физически не умеют хранить и обрабатывать иерархические структуры (таблицы внутри ячеек других таблиц).
Поскольку платформа 1С не может транслировать выборку вложенной таблицы в стандартные плоские типы данных СУБД для записи во временную таблицу, на уровне платформы срабатывает жесткое синтаксическое ограничение, и выполнение запроса прерывается с ошибкой.
---
Проанализируем исходный некорректный код, который привел к ошибке в сообщении автора темы:
ВЫБРАТЬ
вз_Возвраты.Партнер КАК Клиент,
вз_Возвраты.Ссылка КАК ДокументВозврата,
вз_Возвраты.СуммаДокумента КАК СуммаДокумента,
вз_Возвраты.Количество КАК ВозвращеноТоваров
ПОМЕСТИТЬ вт_ДокВозвратов
ИЗ
(ВЫБРАТЬ
ЧекККМВозврат.Ссылка КАК Ссылка,
ЧекККМВозврат.Партнер КАК Партнер,
ЧекККМВозврат.СуммаДокумента КАК СуммаДокумента,
ЧекККМВозврат.Дата КАК Дата,
ЧекККМВозврат.Товары.Количество КАК Количество // Ошибка здесь!
ИЗ
Документ.ЧекККМВозврат КАК ЧекККМВозврат
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВозвратТоваровОтКлиента.Ссылка,
ВозвратТоваровОтКлиента.Партнер,
ВозвратТоваровОтКлиента.СуммаДокумента,
ВозвратТоваровОтКлиента.Дата,
ВозвратТоваровОтКлиента.Товары.Количество // Ошибка здесь!
ИЗ
Документ.ВозвратТоваровОтКлиента КАК ВозвратТоваровОтКлиента) КАК вз_Возвраты
В данном запросе разработчик попытался обратиться к табличной части Товары через точку от таблицы шапки документа: ЧекККМВозврат.Товары.Количество. Платформа распознала это как выборку вложенной таблицы, что и вызвало ошибку при попытке записать результат в ПОМЕСТИТЬ вт_ДокВозвратов.
Чтобы исправить эту проблему, мы должны обращаться к табличным частям документов напрямую как к независимым, самостоятельным плоским таблицам базы данных. Перепишем запрос правильно:
ВЫБРАТЬ
вз_Возвраты.Партнер КАК Клиент,
вз_Возвраты.Ссылка КАК ДокументВозврата,
вз_Возвраты.СуммаДокумента КАК СуммаДокумента,
вз_Возвраты.Количество КАК ВозвращеноТоваров
ПОМЕСТИТЬ вт_ДокВозвратов
ИЗ
(ВЫБРАТЬ
ЧВ.Ссылка КАК Ссылка,
ЧВ.Ссылка.Партнер КАК Партнер,
ЧВ.Ссылка.СуммаДокумента КАК СуммаДокумента,
ЧВ.Ссылка.Дата КАК Дата,
ЧВ.Количество КАК Количество,
ЧВ.Номенклатура КАК Номенклатура
ИЗ
Документ.ЧекККМВозврат.Товары КАК ЧВ // Обращаемся напрямую к ТЧ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВК.Ссылка,
ВК.Ссылка.Партнер,
ВК.Ссылка.СуммаДокумента,
ВК.Ссылка.Дата,
ВК.Количество,
ВК.Номенклатура
ИЗ
Документ.ВозвратТоваровОтКлиента.Товары КАК ВК) КАК вз_Возвраты // Обращаемся напрямую к ТЧ
ГДЕ
вз_Возвраты.Ссылка.Проведен
И (вз_Возвраты.Дата >= &ДатаНачала И вз_Возвраты.Дата <= &ДатаКонца
ИЛИ &ДатаКонца = ДАТАВРЕМЯ(1,1,1));
Обратите внимание на ключевое изменение: вместо источника Документ.ЧекККМВозврат мы использовали источник Документ.ЧекККМВозврат.Товары. Теперь запрос выбирает плоские строки табличной части, где ссылка на документ-владелец получается через реквизит Ссылка (например, ЧВ.Ссылка). Такой запрос успешно помещает данные во временную таблицу, так как в нем больше нет вложенных таблиц.
---
Несмотря на то, что Способ 1 решает сиюминутную проблему и устраняет ошибку, с точки зрения стандартов разработки фирмы «1С» и производительности СУБД он не является оптимальным.
Почему не рекомендуется использовать вложенные запросы?
Когда мы соединяем таблицы или накладываем условия на вложенный запрос (подзапрос), оптимизатор СУБД (например, MS SQL Server) не может заранее точно оценить количество строк (кардинальность), которое вернет этот подзапрос. Из-за отсутствия точной статистики СУБД может построить неэффективный план выполнения запроса (например, выбрать тяжелое сканирование таблиц Index Scan вместо быстрого поиска по индексу Index Seek). Это приводит к колоссальным потерям производительности на больших базах данных.
Фирма «1С» настоятельно рекомендует заменять сложные вложенные запросы на использование временных таблиц. Перепишем нашу задачу по всем правилам оптимизации. Разделим алгоритм на два шага:
Рассмотрим оптимизированный вариант запроса:
// ШАГ 1: Собираем только ссылки на документы, удовлетворяющие условиям фильтрации
ВЫБРАТЬ
ЧекККМВозврат.Ссылка КАК ДокументВозврата,
ЧекККМВозврат.Партнер КАК Клиент,
ЧекККМВозврат.СуммаДокумента КАК СуммаДокумента
ПОМЕСТИТЬ ВТ_Возвраты
ИЗ
Документ.ЧекККМВозврат КАК ЧекККМВозврат
ГДЕ
ЧекККМВозврат.Проведен
И (ЧекККМВозврат.Дата МЕЖДУ &ДатаНачала И &ДатаКонца
ИЛИ &ДатаКонца = ДАТАВРЕМЯ(1, 1, 1))
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВозвратТоваровОтКлиента.Ссылка,
ВозвратТоваровОтКлиента.Партнер,
ВозвратТоваровОтКлиента.СуммаДокумента
ИЗ
Документ.ВозвратТоваровОтКлиента КАК ВозвратТоваровОтКлиента
ГДЕ
ВозвратТоваровОтКлиента.Проведен
И (ВозвратТоваровОтКлиента.Дата МЕЖДУ &ДатаНачала И &ДатаКонца
ИЛИ &ДатаКонца = ДАТАВРЕМЯ(1, 1, 1))
;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Соединяем отобранные документы с их табличными частями
ВЫБРАТЬ
ВТ_Возвраты.Клиент КАК Клиент,
ВТ_Возвраты.ДокументВозврата КАК ДокументВозврата,
ВТ_Возвраты.СуммаДокумента КАК СуммаДокумента,
ВЫБОР
КОГДА НЕ ЧекККМВозвратТовары.Количество ЕСТЬ NULL
ТОГДА ЧекККМВозвратТовары.Количество
КОГДА НЕ ВозвратТоваровОтКлиентаТовары.Количество ЕСТЬ NULL
ТОГДА ВозвратТоваровОтКлиентаТовары.Количество
ИНАЧЕ 0
КОНЕЦ КАК ВозвращеноТоваров
ПОМЕСТИТЬ ВТ_ДокВозвратов
ИЗ
ВТ_Возвраты КАК ВТ_Возвраты
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЧекККМВозврат.Товары КАК ЧекККМВозвратТовары
ПО ВТ_Возвраты.ДокументВозврата = ЧекККМВозвратТовары.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ВозвратТоваровОтКлиента.Товары КАК ВозвратТоваровОтКлиентаТовары
ПО ВТ_Возвраты.ДокументВозврата = ВозвратТоваровОтКлиентаТовары.Ссылка
;
Преимущества этого подхода:
ВТ_Возвраты. Это позволяет построить идеальный план выполнения для левого соединения.ВТ_Возвраты.---
Выясним, какие еще методы существуют в платформе «1С:Предприятие» для работы с табличными частями, когда стандартные запросы по какой-то причине неудобны или неприменимы.
Бывают ситуации, когда документ еще не записан в базу данных (например, пользователь только что заполнил табличную часть на форме и нажал кнопку расчета), но нам уже нужно обработать эти данные запросом. База данных ничего не знает об этих строках.
В этом случае мы можем выгрузить табличную часть в объект ТаблицаЗначений на сервере и передать ее напрямую в запрос как параметр. Платформа автоматически создаст временную таблицу на стороне СУБД и заполнит ее нашими данными.
Посмотрим на пример реализации данного механизма:
// Выгружаем табличную часть "Товары" из реквизита формы в Таблицу Значений
ТЗ_Товары = Объект.Товары.Выгрузить();
Запрос = Новый Запрос;
// Обязательно инициализируем Менеджер временных таблиц!
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
// Передаем Таблицу Значений в качестве параметра
Запрос.УстановитьПараметр("ВнешняяТЗ", ТЗ_Товары);
// В тексте запроса описываем типы колонок и помещаем ТЗ во временную таблицу
Запрос.Текст =
"ВЫБРАТЬ
| ВнешняяТЗ.Номенклатура КАК Номенклатура,
| ВнешняяТЗ.Количество КАК Количество
|ПОМЕСТИТЬ вт_Товары
|ИЗ
| &ВнешняяТЗ КАК ВнешняяТЗ
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| вт_Товары.Номенклатура,
| СУММА(вт_Товары.Количество) КАК ОбщееКоличество
|ИЗ
| вт_Товары КАК вт_Товары
|ГРУППИРОВАТЬ ПО
| вт_Товары.Номенклатура";
РезультатЗапроса = Запрос.Выполнить();
Этот способ позволяет гибко сочетать объектную технику работы с данными на клиенте/сервере и всю мощь реляционного языка запросов 1С.
Если вы разрабатываете сложный отчет на СКД, вам не обязательно объединять разнородные документы с их табличными частями в одном гигантском запросе с кучей временных таблиц.
Правильным решением будет создание двух (или более) независимых наборов данных - запросов:
После этого на вкладке «Связи наборов данных» в схеме компоновки данных вы можете связать эти наборы по полю Ссылка (или ДокументВозврата). Процессор компоновки данных 1С самостоятельно сформирует оптимальные точечные запросы к СУБД, эффективно выберет данные и соединит их в итоговом отчете, полностью исключив возникновение ошибок с вложенными таблицами.
---
Чтобы ваши запросы всегда работали быстро и без сбоев, придерживайтесь следующих правил:
Документ.Товары.Номенклатура). Всегда выбирайте табличную часть как самостоятельную таблицу в секции ИЗ.ПОМЕСТИТЬ ВТ_ИмяТаблицы.ИНДЕКСИРОВАТЬ ПО.tempdb вашей СУБД.