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

← На главную

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

Причина исчезновения строк: феномен деления на ноль через ODBC

Рассмотрим ситуацию: на вход подается список из 3500 элементов номенклатуры, но в результате мы видим всего 412 строк. Выясним причину такого поведения. Оказывается, виновником часто становится операция деления в блоке SELECT, например, при расчете себестоимости: (СуммаОстаток / КоличествоОстаток).

В стандартном SQL Server попытка деления на ноль вызывает ошибку, которая прерывает выполнение инструкции. Однако при использовании старых драйверов ODBC, которые являются стандартом для 1С 7.7, сервер может генерировать предупреждение. Драйвер ODBCRecordset интерпретирует это предупреждение как сигнал к завершению передачи данных. В итоге запрос просто прекращается на той строке, где встретилось деление на ноль, и возвращает клиенту только те данные, которые успел обработать до этого момента.

Для решения этой проблемы мы должны использовать функцию NULLIF. Она сравнивает два выражения и возвращает NULL, если они равны. Рассмотрим пример корректного расчета себестоимости:


SELECT
    Рег.Номенклатура AS [Номенклатура],
    Рег.КоличествоОстаток AS Количество,
    Рег.СуммаРубОстаток AS СуммаРуб,
    -- Вместо Рег.СуммаРубОстаток / Рег.КоличествоОстаток пишем:
    Рег.СуммаРубОстаток / NULLIF(Рег.КоличествоОстаток, 0) AS Себестоимость
FROM
    $РегистрОстатки.ПартииНаличие(:ВыбДата~,, ,(Номенклатура),(Количество,СуммаРуб)) AS Рег

В этом случае, если КоличествоОстаток равно нулю, делитель станет NULL. По правилам SQL, любое деление на NULL дает в результате NULL, не вызывая исключения. Это позволяет запросу успешно завершиться и вернуть все 3500 строк. Позже, при обработке результата в 1С, значения NULL будут автоматически преобразованы в 0 (для числовых полей).

Конфликты имен и псевдонимов (алиасов) в метапарсере 1С++

Проанализируем еще один важный аспект: использование псевдонимов таблиц. При написании прямых запросов мы используем конструкции типа $Регистр.ПартииНаличие. Перед отправкой на SQL-сервер компонента 1С++ (метапарсер) заменяет эти конструкции на реальные имена таблиц БД (например, rg328).

Важное правило: псевдонимы таблиц (алиасы), имена которых транслируются из метаимен, не должны повторяться. Если вы используете один и тот же алиас Рег для основного запроса и для подзапроса к другому регистру, парсер может ошибиться. Рассмотрим, как правильно распределить алиасы:


|FROM
|    #TempTable AS temp
|INNER JOIN
|    $Справочник.Номенклатура AS Спр ON Спр.ID = temp.val
|LEFT JOIN
|    (SELECT ... FROM $Регистр.ПартииНаличие AS РегПодзапрос ...) AS subsel 
|    ON temp.val = subsel.Номенклатура
|LEFT JOIN
|    $Регистр.ПартииНаличие AS РегДвижения 
|    ON $РегДвижения.Номенклатура = subsel.Номенклатура

Обратите внимание, что для каждой таблицы мы задаем уникальное имя (temp, Спр, РегПодзапрос, РегДвижения). Это гарантирует, что метапарсер правильно подставит имена полей для каждой таблицы.

Особенности поиска последней партии

Часто в запросах требуется найти документ последнего прихода по каждой позиции номенклатуры. В 1С 7.7 для этого идеально подходит поле Date_Time_IDDoc. Это системное поле, которое содержит в себе дату, время и уникальный идентификатор документа в виде одной строки. Оно индексировано и позволяет очень быстро находить «самую позднюю» запись.

Разберем структуру этого поля:

Первые 8 символов — дата в формате YYYYMMDD.
Следующие 6 символов — время.
Последние 9 символов — уникальный IDDoc.

Чтобы найти дату последнего прихода по списку товаров, мы используем агрегатную функцию MAX. Посмотрим на структуру подзапроса:


SELECT
    $Рег.Номенклатура AS [Номенклатура],
    MAX(Рег.Date_Time_IDDOC) AS ДатаПослПрихода
FROM
    $Регистр.ПартииНаличие as Рег
INNER JOIN
    _1Sjourn as ЖурналДок ON ЖурналДок.IDDoc = Рег.IDDoc
WHERE
    ЖурналДок.iddocdef = $ВидДокумента.ПоступлениеТМЦ_Склад
    AND $Рег.Номенклатура IN (SELECT val FROM #TempTable)
GROUP BY 
    $Рег.Номенклатура

Затем этот подзапрос соединяется с основной таблицей по полю Date_Time_IDDOC, чтобы получить данные конкретной партии (цену, поставщика и т.д.). Однако будьте внимательны: если в рамках одного и того же документа (с одним IDDoc) товар прошел по разным строкам или разным движениям, LEFT JOIN может «размножить» строки. Чтобы этого не произошло, необходимо убедиться, что условие связи максимально уникально.

Использование временных таблиц для фильтрации

Когда мы работаем с большим списком номенклатуры (более 1000 элементов), передача его через оператор IN ('...', '...') в самом тексте запроса может привести к ошибкам из-за ограничения длины строки или снижения производительности. Лучшая практика — использование временных таблиц #TempTable.

Рассмотрим алгоритм действий:

Выгружаем список номенклатуры из 1С в объект СписокЗначений.
Создаем временную таблицу в SQL и наполняем её идентификаторами номенклатуры (по 9 символов).
Используем INNER JOIN или IN (SELECT val FROM #TempTable) в основном запросе.

Это не только решает проблему с передачей параметров, но и позволяет SQL Server построить эффективный план выполнения, используя индексы временной таблицы.

Итоговый пример структуры запроса

Объединим все рекомендации: правильные соединения, уникальные алиасы и защиту от деления на ноль. Проанализируем следующую структуру:


SELECT 
    temp.val AS [Товар $Справочник.Номенклатура],
    Спр.Code AS Код,
    balance.Количество AS [Остаток $Число],
    -- Безопасный расчет себестоимости
    ISNULL(sebest.Сумма / NULLIF(sebest.Количество, 0), 0) AS [СредняяЦена $Число],
    CAST(LEFT(subsel.ДатаПослПрихода, 8) AS DateTime) AS ДатаПрихода
FROM 
    #TempTable AS temp
INNER JOIN 
    $Справочник.Номенклатура AS Спр ON Спр.ID = temp.val
LEFT JOIN 
    (
        -- Подзапрос для поиска даты последнего прихода
        SELECT 
            $РегП.Номенклатура AS Ном,
            MAX(РегП.Date_Time_IDDOC) AS ДатаПослПрихода
        FROM $Регистр.ПартииНаличие AS РегП
        WHERE ЖурналДок.iddocdef = $ВидДокумента.ПоступлениеТМЦ
        GROUP BY $РегП.Номенклатура
    ) AS subsel ON temp.val = subsel.Ном
LEFT JOIN
    (
        -- Виртуальная таблица остатков
        SELECT 
            r.Номенклатура AS Ном,
            r.КоличествоОстаток AS Количество
        FROM $РегистрОстатки.ОстаткиТМЦ(:ВыбДата~,, , , ) AS r
    ) AS balance ON temp.val = balance.Ном
LEFT JOIN
    (
        -- Виртуальная таблица для себестоимости
        SELECT 
            s.Номенклатура AS Ном,
            s.КоличествоОстаток AS Количество,
            s.СуммаРубОстаток AS Сумма
        FROM $РегистрОстатки.ПартииНаличие(:ВыбДата~,, , , ) AS s
    ) AS sebest ON temp.val = sebest.Ном
ORDER BY Спр.Code

При таком подходе мы гарантированно получаем все 3500 строк из временной таблицы, даже если по некоторым позициям нет остатков или движений (благодаря LEFT JOIN), а отсутствие данных о цене или количестве не приведет к прерыванию запроса благодаря функции NULLIF.