Как исправить ошибку синтаксиса при соединении с виртуальной таблицей остатков в 1С++?

← На главную

При написании прямых запросов к базе данных 1С:Предприятия 7.7 с использованием внешней компоненты 1С++ (1cpp) разработчики часто сталкиваются со сложными синтаксическими ошибками СУБД. Одна из наиболее коварных проблем возникает при попытке связать несколько виртуальных таблиц остатков, когда одна из них не имеет прямой связи по ключевому измерению и требует соединения через промежуточный документ.

В этой статье мы подробно разберем, почему возникает синтаксическая ошибка при соединении с виртуальной таблицей остатков, проанализируем ограничения области видимости в T-SQL и рассмотрим два способа решения проблемы: рабочее обходное решение через UNION ALL и максимально эффективное решение через каскад внешних соединений (LEFT JOIN).

Анализ исходной проблемы и причин синтаксической ошибки

Рассмотрим исходный запрос, который вызвал ошибку СУБД «Неправильный синтаксис около конструкции "Развоз"»:


select
    Заявка.iddoc as [Заявка $Документ.Заявка],
    isnull(Бухгалтерия.РезервТовараОстаток, 0) as Бухгалтерия,
    isnull(Склад.КоличествоОстаток, 0) as Склад,
    isnull(Шайба.ОстатокТовараОстаток, 0) as Шайба,
    isnull(Развоз.КоличествоОстаток, 0) as Развоз
from $Документ.Заявка as Заявка
left join _1sjourn as Журнал on Журнал.iddoc = Заявка.iddoc
left join $РегистрОстатки.Заявки() as Бухгалтерия on Бухгалтерия.ПоСчету = Заявка.iddoc
left join $РегистрОстатки.Ожидания() as Склад on Склад.Заявка = $ВидДокумента36.Заявка + Заявка.iddoc
left join $РегистрОстатки.ОстаткиТоваровОбщ() as Шайба on Шайба.Заявка = Заявка.iddoc
left join $РегистрОстатки.Развоз(,
    inner join $Документ.НакладнаяВнПерем as НВП on НВП.iddoc = ДокументДвижения and $НВП.Заявка = Заявка.iddoc) as Развоз

Проанализируем этот запрос и выясним причины возникновения ошибки. В данном коде присутствуют сразу две критические ошибки:

Отсутствие оператора соединения ON для внешнего JOIN: Оператор left join на строке с виртуальной таблицей Развоз объявляет соединение, но после закрывающей скобки макроса и объявления алиаса as Развоз полностью отсутствует ключевое слово ON с условием связи. SQL-сервер не может сопоставить строки основной выборки со строками таблицы остатков без этого условия.
Нарушение области видимости (Scoping) в T-SQL: Внутри параметров макроса виртуальной таблицы $РегистрОстатки.Развоз автор пытается наложить условие фильтрации: and $НВП.Заявка = Заявка.iddoc. Макросы виртуальных таблиц в 1С++ компилируются во вложенные подзапросы (derived tables). Согласно стандарту SQL, вложенный подзапрос, определяемый внутри секции FROM / JOIN, не имеет доступа к алиасам таблиц, объявленным на том же уровне или в соседних ветках соединения (в данном случае к таблице Заявка). СУБД физически не может разрешить идентификатор Заявка.iddoc на этапе формирования подзапроса остатков.

Стоит заметить, почему у автора работал пример с временной таблицей:


select Развоз.ДокументДвижения
from
   $РегистрОстатки.Развоз(,
      inner join $Документ.НакладнаяВнПерем as НВП on НВП.iddoc = ДокументДвижения and $НВП.Заявка in (select val from #Заявки)) as Развоз

Этот запрос успешно отрабатывает, потому что #Заявки — это реальная временная таблица в базе данных MS SQL Server. Временные таблицы имеют глобальную область видимости в рамках текущей сессии, поэтому вложенный подзапрос макроса 1С++ без проблем считывает из нее данные. Напрямую же сослаться на соседний алиас Заявка во внешнем запросе внутри параметров макроса невозможно.

Решение №1: Обходной путь через объединение таблиц (UNION ALL)

Если нам необходимо собрать остатки из разных регистров по одной и той же сущности (в данном случае по документу «Заявка»), но структура регистров принципиально отличается (где-то измерением выступает сама заявка, а где-то — подчиненный документ движения), мы можем воспользоваться механизмом объединения наборов строк UNION ALL.

Рассмотрим вариант решения, к которому в итоге пришел автор темы:


select
    Бухгалтерия.ПоСчету as [Заявка $Документ.Заявка],
    isnull(Бухгалтерия.РезервТовараОстаток, 0) as Бухгалтерия,
    0 as Склад,
    0 as Шайба,
    0 as Развоз
from $РегистрОстатки.Заявки() as Бухгалтерия
union all
select
    Заявка.iddoc as [Заявка $Документ.Заявка],
    0 as Бухгалтерия,
    isnull(Склад.КоличествоОстаток, 0) as Склад,
    0 as Шайба,
    0 as Развоз
from $РегистрОстатки.Ожидания() as Склад
inner join $Документ.Заявка as Заявка on $ВидДокумента36.Заявка + Заявка.iddoc = Склад.Заявка
union all
select
    Шайба.Заявка as [Заявка $Документ.Заявка],
    0 as Бухгалтерия,
    0 as Склад,
    isnull(Шайба.ОстатокТовараОстаток, 0) as Шайба,
    0 as Развоз
from $РегистрОстатки.ОстаткиТоваровОбщ() as Шайба
union all
select
    $НВП.Заявка as [Заявка $Документ.Заявка],
    0 as Бухгалтерия,
    0 as Склад,
    0 as Шайба,
    isnull(Развоз.КоличествоОстаток, 0) as Развоз
from $РегистрОстатки.Развоз() as Развоз
inner join $Документ.НакладнаяВнПерем as НВП on НВП.iddoc = Развоз.ДокументДвижения

Проанализируем особенности этого решения. Этот подход полностью рабочий и решает проблему несовместимости структуры данных. Мы последовательно получаем выборки из каждого регистра:

Из регистра Заявки получаем остатки напрямую.
Из регистра Ожидания соединяемся с таблицей документов для корректного сопоставления типизированного текстового поля (или составного измерения) с идентификатором заявки.
Из регистра ОстаткиТоваровОбщ получаем остатки напрямую по измерению Заявка.
Из проблемного регистра Развоз мы сначала выгружаем все остатки, а затем связываем их через INNER JOIN с таблицей документов НакладнаяВнПерем, откуда и забираем реквизит Заявка.

Недостатки решения через UNION ALL: Этот метод имеет серьезные проблемы с производительностью на больших базах данных. СУБД вынуждена выполнять четыре независимых сканирования таблиц итогов регистров, строить планы выполнения для каждой ветки отдельно, а затем производить операцию конкатенации результатов. Если в дальнейшем потребуется сгруппировать данные по заявке, придется оборачивать весь этот огромный запрос во внешний SELECT ... GROUP BY, что вызовет повторную сортировку больших объемов данных в базе tempdb.

Решение №2: Оптимальный каскадный LEFT JOIN (Рекомендуется)

Для того чтобы избежать низкой производительности UNION ALL и написать лаконичный, быстрый запрос, нам нужно правильно выстроить цепочку внешних соединений на одном уровне вложения.

Выясним причину проблемы с регистром Развоз: у него нет измерения «Заявка», но есть измерение «ДокументДвижения», ссылающееся на документ НакладнаяВнПерем, у которого, в свою очередь, есть реквизит «Заявка». Нам нужно просто связать эти таблицы последовательно на внешнем уровне запроса.

Рассмотрим спроектированный оптимальный вариант запроса:


select
    Заявка.iddoc as [Заявка $Документ.Заявка],
    isnull(Бухгалтерия.РезервТовараОстаток, 0) as Бухгалтерия,
    isnull(Склад.КоличествоОстаток, 0) as Склад,
    isnull(Шайба.ОстатокТовараОстаток, 0) as Шайба,
    isnull(Развоз.КоличествоОстаток, 0) as Развоз
from $Документ.Заявка as Заявка
left join _1sjourn as Журнал on Журнал.iddoc = Заявка.iddoc
left join $РегистрОстатки.Заявки() as Бухгалтерия on Бухгалтерия.ПоСчету = Заявка.iddoc
left join $РегистрОстатки.Ожидания() as Склад on Склад.Заявка = $ВидДокумента36.Заявка + Заявка.iddoc
left join $РегистрОстатки.ОстаткиТоваровОбщ() as Шайба on Шайба.Заявка = Заявка.iddoc

/* Шаг 1: Находим подчиненный документ НакладнаяВнПерем для текущей Заявки */
left join $Документ.НакладнаяВнПерем as НВП on $НВП.Заявка = Заявка.iddoc

/* Шаг 2: Подтягиваем остатки по регистру Развоз, связывая их по найденной накладной */
left join $РегистрОстатки.Развоз() as Развоз on Развоз.ДокументДвижения = НВП.iddoc

Проанализируем по шагам, почему этот запрос работает быстрее и не вызывает ошибок синтаксиса:

Соблюдение области видимости: Все соединения (LEFT JOIN) выполняются на самом верхнем, внешнем уровне. Таблица НВП (НакладнаяВнПерем) видит алиас Заявка, а таблица Развоз видит алиас НВП.
Эффективное использование индексов: СУБД MS SQL Server отлично оптимизирует такие цепочки левых соединений. Поиск документа НакладнаяВнПерем будет выполняться по индексу реквизита Заявка, а получение остатков из регистра Развоз — по кластерному индексу или индексу измерения ДокументДвижения.
Отсутствие дублирования кода: Запрос выглядит монолитным, легко читается и поддерживается. Нам не нужно дублировать поля и конструировать искусственные нули для отсутствующих показателей, как это делалось в UNION ALL.

Правила фильтрации данных внутри макросов 1С++

В процессе обсуждения на форуме упоминалась возможность использования параметров внутри круглых скобок макроса виртуальной таблицы. Напомним синтаксис макроса $РегистрОстатки в компоненте 1С++:

$РегистрОстатки.ИмяРегистра([ГраницаРасчета][, Соединение][, Условие][, Измерение][, Ресурс])

Второй параметр «Соединение» и третий параметр «Условие» предназначены исключительно для внутренней фильтрации данных регистра СУБД до того, как начнется расчет остатков и группировка. Это критически важно для производительности. Но использовать их нужно правильно:

Внутри этих параметров можно соединяться со справочниками или другими таблицами, чтобы отфильтровать движения (например, отобрать остатки только по определенным свойствам номенклатуры).
Там можно использовать внутренние переменные запроса (через двоеточие, например :ВыбТовар) или временные таблицы базы данных (например, #Заявки).
Категорически нельзя использовать в них алиасы таблиц, которые объявлены во внешнем FROM/JOIN основного запроса, так как это нарушает логическую последовательность фаз выполнения SQL-запроса (Logical Query Processing).

Таким образом, всегда отдавайте предпочтение классическому последовательному связыванию таблиц на внешнем уровне, если вам нужно сопоставить данные регистров через цепочку подчиненных документов.