Таблица значений в запросе 1С: как передать ТЗ и работать с временной таблицей

Почему нельзя передать таблицу значений прямо в &Параметр

Первое, обо что спотыкаются: попытка использовать таблицу значений в запросе 1С как обычный параметр — выбрать из неё в секции ИЗ:

// ТАК НЕЛЬЗЯ: из &ТЗ в секции ИЗ выбирать запрещено
Запрос.Текст =
    "ВЫБРАТЬ Т.Номенклатура, Т.Количество
    |ИЗ
    |   &ТЗ КАК Т";   // ← ошибка компиляции запроса

Как параметр в условии таблицу значений передавать можно — например, в конструкции В (&Список) движок сам преобразует ТЗ в набор строк. Но именно выбирать из ТЗ, соединять её с другими таблицами и индексировать напрямую нельзя: язык запросов работает только с таблицами базы данных и временными таблицами. Поэтому ТЗ сначала «материализуют» во временную таблицу — и уже с ней делают всё остальное.

Обязательное условие: типы колонок таблицы значений

Чтобы поместить таблицу значений во временную таблицу, платформа должна знать тип каждой колонки — из типа колонки ТЗ формируется тип поля временной таблицы. Если колонка создана без типа (просто Колонки.Добавить("Код")), при выполнении запроса будет ошибка «Тип не может быть выбран в запросе». Поэтому типы задают явно через ОписаниеТипов:

ТЗ = Новый ТаблицаЗначений;
ТЗ.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
ТЗ.Колонки.Добавить("Количество",   Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 3)));
ТЗ.Колонки.Добавить("Комментарий",  Новый ОписаниеТипов("Строка", , Новый КвалификаторыСтроки(150)));

// заполняем строки
НоваяСтрока = ТЗ.Добавить();
НоваяСтрока.Номенклатура = ТоварСсылка;
НоваяСтрока.Количество   = 10;
НоваяСтрока.Комментарий  = "из внешнего файла";

Если ТЗ приходит откуда-то без типов (загружена из файла, собрана универсальным кодом), надёжнее пересобрать её в новую типизированную таблицу и перелить строки через ЗаполнитьЗначенияСвойств(). Пустые ячейки лучше не оставлять NULL: инициализируйте числа нулём, строки — пустой строкой, ссылки — пустой ссылкой, иначе на стороне запроса придётся всюду оборачивать поля в ЕСТЬNULL().

МенеджерВременныхТаблиц: без него временная таблица не создастся

Временная таблица живёт не сама по себе, а внутри МенеджерВременныхТаблиц — объекта, который хранит созданные таблицы и позволяет переиспользовать их между запросами. Перед первым ПОМЕСТИТЬ его нужно назначить свойству Запрос.МенеджерВременныхТаблиц:

МенеджерВТ = Новый МенеджерВременныхТаблиц;

Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;   // ← обязательно до ПОМЕСТИТЬ

Пока менеджер жив, все помещённые в него временные таблицы существуют в оперативной памяти (или во временных таблицах СУБД для больших объёмов) и доступны следующим запросам, которым назначен тот же менеджер. Освобождаются они автоматически при Закрыть() менеджера, при уничтожении объекта или через оператор УНИЧТОЖИТЬ ВТ_Имя в тексте запроса.

Поместить таблицу значений во временную таблицу: ВЫБРАТЬ … ПОМЕСТИТЬ

Собственно передача ТЗ в запрос: параметром &ТЗ передаём таблицу значений, а конструкция ПОМЕСТИТЬ сохраняет результат выборки во временную таблицу. Имена и типы колонок временной таблицы берутся из ТЗ:

МенеджерВТ = Новый МенеджерВременныхТаблиц;

Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
Запрос.Текст =
    "ВЫБРАТЬ
    |   ТЗ.Номенклатура КАК Номенклатура,
    |   ТЗ.Количество   КАК Количество
    |ПОМЕСТИТЬ ВТ_Данные
    |ИЗ
    |   &ТЗ КАК ТЗ";
Запрос.УстановитьПараметр("ТЗ", ТЗ);
Запрос.Выполнить();  // временная таблица ВТ_Данные создана внутри МенеджерВТ

Запрос.УстановитьПараметр("ТЗ", ТЗ);

После Выполнить() результат помещающего запроса пустой (ПОМЕСТИТЬ ничего не возвращает выборкой) — данные ушли во временную таблицу ВТ_Данные. Обратиться к ней можно только из следующего запроса с тем же менеджером или из следующего пакета того же текста.

Индексировать временную таблицу: ИНДЕКСИРОВАТЬ ПО для скорости соединений

Если по колонке временной таблицы будет соединение или отбор, добавьте ИНДЕКСИРОВАТЬ ПО. Без индекса СУБД при соединении сканирует временную таблицу целиком — на тысячах строк это заметно медленнее. Индекс строят прямо в помещающем запросе:

Запрос.Текст =
    "ВЫБРАТЬ
    |   ТЗ.Номенклатура КАК Номенклатура,
    |   ТЗ.Количество   КАК Количество
    |ПОМЕСТИТЬ ВТ_Данные
    |ИЗ
    |   &ТЗ КАК ТЗ
    |
    |ИНДЕКСИРОВАТЬ ПО
    |   Номенклатура";   // ← по колонке будущего соединения

Индексируют именно те поля, по которым идёт СОЕДИНЕНИЕ или условие ГДЕ. Индексировать всё подряд смысла нет — построение индекса тоже стоит времени. Ориентир простой: временная таблица небольшая и используется один раз — индекс не нужен; таблица крупная и соединяется с регистром — индекс по ключу соединения обязателен.

Соединить временную таблицу с регистрами и справочниками

Главный смысл всей конструкции — соединить данные из кода с данными базы. После того как ВТ_Данные создана, следующий запрос с тем же менеджером обращается к ней как к обычной таблице и джойнит с регистром остатков:

ЗапросИтог = Новый Запрос;
ЗапросИтог.МенеджерВременныхТаблиц = МенеджерВТ;   // тот же менеджер!
ЗапросИтог.Текст =
    "ВЫБРАТЬ
    |   ВТ_Данные.Номенклатура КАК Номенклатура,
    |   ВТ_Данные.Количество   КАК Заказано,
    |   ЕСТЬNULL(Остатки.КоличествоОстаток, 0) КАК Остаток
    |ИЗ
    |   ВТ_Данные КАК ВТ_Данные
    |   ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки() КАК Остатки
    |   ПО ВТ_Данные.Номенклатура = Остатки.Номенклатура";
Выборка = ЗапросИтог.Выполнить().Выбрать();
Пока Выборка.Следующий() Цикл
    Сообщить(Выборка.Номенклатура.Наименование + ": заказано "
        + Выборка.Заказано + ", остаток " + Выборка.Остаток);
КонецЦикла;

ЛЕВОЕ СОЕДИНЕНИЕ сохраняет все строки временной таблицы, даже если по номенклатуре нет остатка — тогда ЕСТЬNULL() подставит ноль. Так одним запросом обрабатывается весь массив из ТЗ, без обращения к базе в цикле по каждой строке.

Пакетный запрос: ПОМЕСТИТЬ и выборка в одном тексте

Разбивать на два объекта Запрос необязательно. Оба шага можно записать одним пакетным запросом — пакеты разделяются символом ;. Менеджер временных таблиц всё равно нужен, потому что между пакетами живёт временная таблица:

Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
Запрос.Текст =
    "ВЫБРАТЬ
    |   ТЗ.Номенклатура КАК Номенклатура,
    |   ТЗ.Количество   КАК Количество
    |ПОМЕСТИТЬ ВТ_Данные
    |ИЗ
    |   &ТЗ КАК ТЗ
    |ИНДЕКСИРОВАТЬ ПО
    |   Номенклатура
    |;
    |ВЫБРАТЬ
    |   ВТ_Данные.Номенклатура КАК Номенклатура,
    |   ЕСТЬNULL(Остатки.КоличествоОстаток, 0) КАК Остаток
    |ИЗ
    |   ВТ_Данные КАК ВТ_Данные
    |   ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки() КАК Остатки
    |   ПО ВТ_Данные.Номенклатура = Остатки.Номенклатура";
Запрос.УстановитьПараметр("ТЗ", ТЗ);

// ВыполнитьПакет вернёт массив результатов по числу пакетов;
// нужный нам — последний
РезультатыПакета = Запрос.ВыполнитьПакет();
Выборка = РезультатыПакета[РезультатыПакета.ВГраница()].Выбрать();
Пока Выборка.Следующий() Цикл
    Сообщить(Выборка.Номенклатура.Наименование + ": " + Выборка.Остаток);
КонецЦикла;

Выполнить() у пакетного запроса вернёт результат последнего пакета, а ВыполнитьПакет() — массив результатов всех пакетов (удобно для отладки). Первый пакет (ПОМЕСТИТЬ) даёт пустой результат, значимая выборка — во втором.

Частые ошибки при работе с таблицей значений в запросе 1С

• Не заданы типы колонок ТЗ. Классическая ошибка «Тип не может быть выбран в запросе». Каждую колонку создавайте с ОписаниеТипов; если ТЗ пришла без типов — пересоберите её в типизированную и перелейте строки через ЗаполнитьЗначенияСвойств().
• Забыли МенеджерВременныхТаблиц. Без назначенного Запрос.МенеджерВременныхТаблиц оператор ПОМЕСТИТЬ для временной таблицы, которую нужно использовать дальше, работать не будет — второй запрос «не увидит» ВТ_Данные.
• Разные менеджеры у запросов. Помещающий и читающий запросы должны использовать один и тот же экземпляр МенеджерВременныхТаблиц. Новый менеджер во втором запросе не увидит созданную ранее временную таблицу.
• Нет индекса на большой таблице. Соединение крупной временной таблицы с регистром без ИНДЕКСИРОВАТЬ ПО ключу соединения — типичная причина «запрос стал тормозить».
• Попытка выбрать прямо из &ТЗ в секции ИЗ. Так нельзя: сначала ПОМЕСТИТЬ во временную таблицу, а из ТЗ-параметра можно только сравнивать через В (&ТЗ).

Частые вопросы про таблицу значений в запросе 1С