Как добавить в выборку SQL-запроса номенклатуру без движений в 1С 7.7

← На главную

При построении сложных отчетов на платформе 1С 7.7 с использованием прямых SQL-запросов (через 1С++ или другие компоненты) разработчики часто сталкиваются с задачей: необходимо вывести список всей номенклатуры из переданного списка, даже если по ней не было никаких движений (поступлений, продаж, остатков) за указанный период. В стандартном INNER JOIN такие позиции просто исчезают из результата, а при попытке добавить их через UNION ALL возникают ошибки несовпадения типов данных. Рассмотрим, как правильно решить эту задачу и корректно типизировать «пустые» строки.

Разбор проблемы: почему возникают ошибки типизации

Проанализируем ситуацию: у нас есть временная таблица #TempTable, в которую загружен список идентификаторов номенклатуры. Мы пытаемся объединить данные о реальных документах поступления с «заглушками» для тех позиций, по которым прихода не было. Основная ошибка кроется в том, что SQL-сервер требует строгого соответствия типов данных во всех частях объединения UNION ALL.

Если в первой части запроса поле содержит ссылку на документ (тип CHAR(9)), а во второй части вы пытаетесь подставить просто цифру или строку другой длины, SQL выдаст ошибку. В 1С 7.7 ссылки на объекты — это специфические строки, и для «пустых» значений необходимо использовать специальные метасимволы 1С++.

Решение через использование UNION ALL и метасимволов

Рассмотрим правильный подход к формированию второй части запроса. Чтобы итоговая таблица корректно отображала номенклатуру без движений, мы должны «подсунуть» системе пустые идентификаторы того же типа, что и реальные данные. Выясним причину ошибки: в исходном коде автора вместо идентификаторов использовалось поле VAL, которое не соответствовало структуре полей документов и контрагентов.

Для исправления ситуации воспользуемся следующим кодом:


|SELECT
|    Жур.IDDoc AS [ТекДок],
|    Жур.IDDocDef AS ТекДок_вид,
|    $Документ.ПоступлениеТМЦ_Склад.Контрагент AS [Контрагент],
|    $ДокСтроки.Номенклатура [Номенклатура],
|    $ДокСтроки.Количество [Количество]
|FROM
|    _1SJourn Жур
|INNER JOIN
|    $ДокументСтроки.ПоступлениеТМЦ_Склад AS ДокСтроки ON ДокСтроки.IDDoc = Жур.IDDoc
|LEFT JOIN
|    $Документ.ПоступлениеТМЦ_Склад AS ПоступлениеТМЦ_Склад ON Жур.IDDOC = ПоступлениеТМЦ_Склад.IDDOC
|WHERE
|    Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~
|    AND $ДокСтроки.Номенклатура IN (SELECT Val FROM #TempTable)
|
|UNION ALL
|
|SELECT
|    $ПустойИД,                               -- Пустая ссылка на документ
|    $ВидДокумента.ПоступлениеТМЦ_Склад,      -- Идентификатор вида документа
|    $ПустойИД,                               -- Пустая ссылка на контрагента
|    val,                                     -- ID номенклатуры из списка
|    0                                        -- Нулевое количество
|FROM  
|    #TempTable

Разберем по шагам, что мы сделали в блоке UNION ALL:

$ПустойИД: Этот метасимвол разворачивается в строку из 9 пробелов. Именно так в базе данных SQL 1С 7.7 хранятся пустые ссылки на справочники или документы. Это обеспечивает идеальное совпадение типов с полями IDDoc и Контрагент.
$ВидДокумента.ИмяДокумента: Поле IDDocDef в журнале документов хранит числовой идентификатор метаданных. Чтобы выборка была однородной, мы подставляем идентификатор именно того вида документа, который анализируем.
Порядок полей: Крайне важно, чтобы порядок колонок во второй части UNION ALL в точности совпадал с порядком в первой части. Если первым идет ТекДок, то и во второй части на первом месте должен быть пустой IDDoc.

Альтернативный и более эффективный метод: LEFT JOIN

Хотя метод с UNION ALL работает, проанализируем ситуацию с точки зрения производительности. Более «чистым» решением в SQL считается использование LEFT JOIN от справочника (или временной таблицы) к данным. В этом случае нам не нужно вручную прописывать пустые значения, так как SQL сам подставит NULL там, где данных нет.

Посмотрим на пример реализации через LEFT JOIN:


|SELECT
|    СпрНоменклатура.Code [Код],
|    Temp.Val [Номенклатура $Справочник.Номенклатура],
|    ISNULL(ДокДанные.Количество, 0) [Количество],
|    ISNULL(ДокДанные.ТекДок, $ПустойИД) [Док $Документ.ПоступлениеТМЦ_Склад],
|    ISNULL(ДокДанные.Контрагент, $ПустойИД) [Контр $Справочник.Контрагенты]
|FROM #TempTable AS Temp
|LEFT JOIN (
|    SELECT
|        $ДокСтроки.Номенклатура [Номенклатура],
|        Жур.IDDoc [ТекДок],
|        $ДокШапка.Контрагент [Контрагент],
|        $ДокСтроки.Количество [Количество]
|    FROM _1SJourn AS Жур
|    INNER JOIN $ДокументСтроки.ПоступлениеТМЦ_Склад AS ДокСтроки ON ДокСтроки.IDDoc = Жур.IDDoc
|    INNER JOIN $Документ.ПоступлениеТМЦ_Склад AS ДокШапка ON ДокШапка.IDDoc = Жур.IDDoc
|    WHERE Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~
|      AND Жур.CLOSED & 1 = 1
|) AS ДокДанные ON Temp.Val = ДокДанные.Номенклатура
|INNER JOIN $Справочник.Номенклатура AS СпрНоменклатура ON СпрНоменклатура.ID = Temp.Val

В этом варианте мы выбираем все записи из #TempTable. Если во вложенном запросе ДокДанные нет соответствующей номенклатуры, функция ISNULL заменит пустоту на 0 или $ПустойИД. Этот метод исключает дублирование строк, которое могло возникнуть при UNION ALL, если номенклатура есть и в списке, и в документах.

Важные замечания по работе с типами данных

При работе с прямыми запросами в 1С 7.7 следует помнить о нескольких критически важных моментах:

Типизация чисел: Поле «Количество» в документах обычно имеет тип NUMERIC(14, 3). Когда вы пишете просто 0 в UNION, SQL может воспринять это как INT. В некоторых случаях это приводит к ошибке Operand type clash. Чтобы этого избежать, можно использовать явное преобразование: CAST(0 AS NUMERIC(14, 3)).

Индексация временных таблиц: Если ваш список номенклатуры в #TempTable содержит сотни или тысячи элементов, обязательно создайте индекс по полю Val перед выполнением основного запроса. Это ускорит операцию соединения в разы.

Состояние документов: Не забывайте проверять флаг Жур.CLOSED & 1 = 1. Это гарантирует, что в выборку попадут только проведенные документы. Если вам нужны и непроведенные, это условие следует убрать, но обычно для отчетов по поступлениям анализируются только проведенные записи.

Подведем итог: для добавления «пустых» позиций в отчет используйте либо UNION ALL с тщательным выравниванием типов через $ПустойИД, либо (что предпочтительнее) LEFT JOIN от полной таблицы номенклатуры к таблице движений с обработкой NULL через ISNULL.