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

← На главную

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

В этой статье мы подробно разберем, почему стандартное соединение (JOIN) или условие вхождения (IN) могут не работать «из коробки», как устроена внутренняя структура хранения ссылок 1С 7.7 и как написать быстрый, производительный и правильный SQL-запрос для решения этой задачи.

Постановка задачи

Представим практическую ситуацию. В нашей конфигурации существует документ __СканыДоков__. У этого документа есть реквизит шапки Док, имеющий тип «Документ» (без указания конкретного вида, то есть документ неопределенного вида). В табличной части документа хранятся пути к файлам сканов (реквизиты Имя и Путь).

Нам необходимо отобрать все сканы документов, у которых в реквизите Док указан документ, относящийся к определенному контрагенту (ВыбКонтрагент). Для фильтрации по контрагенту мы хотим использовать системный механизм — графу отбора «Контрагент», данные которой хранятся в таблице _1SCRDOC.

В чем сложность: Разница в форматах хранения идентификаторов

Выясним причину, по которой простой интуитивный запрос не возвращает данные. Дело в том, что 1С 7.7 хранит ссылки на объекты метаданных в базе SQL в разных форматах в зависимости от жесткости типизации реквизита:

Документ определенного вида: Если бы реквизит Док был жестко типизирован (например, только «Реализация»), он хранился бы в виде простой 9-символьной строки — физического идентификатора документа (IDDoc), например "B6LV098 ".
Документ неопределенного вида: Поскольку тип документа заранее неизвестен, системе необходимо хранить не только сам идентификатор, но и информацию о том, к какому метаклассу (виду документа) он относится. Поэтому в базе данных такой реквизит хранится в виде 13-символьной строки.
Таблица связей графы отбора (_1SCRDOC): Поле CHILDID (идентификатор подчиненного документа) в системных таблицах всегда имеет фиксированную длину 9 символов.

Проанализируем структуру 13-символьного идентификатора неопределенного документа:

Первые 4 символа — это 36-ричное представление идентификатора вида документа (IDDOCDEF), дополненное пробелами (например, "17Y ").
Следующие 9 символов — это непосредственно уникальный идентификатор самого документа (IDDoc), например "B5PS098 ".

Попытка напрямую сравнить 13-символьное поле "17Y B5PS098 " с 9-символьным "B5PS098 " через оператор = или IN закономерно приведет к тому, что СУБД вернет пустой результат.

Решение 1: Простое сопоставление через функцию RIGHT

Самый быстрый способ обойти ограничение по длине строки — «отрезать» от 13-символьного реквизита неопределенного вида правые 9 символов, чтобы получить чистый IDDoc, и сравнить его с выборкой из таблицы _1SCRDOC. Для этого в SQL используется функция RIGHT().

Рассмотрим пример готового текста запроса для класса ЗапросSQL:


ТекстЗапроса = "
|SELECT
|    $ДокументСканыДоков.Док as [Док $Документ]
|    ,$ДокументСканыДоковТЧ.Имя as Имя
|    ,$ДокументСканыДоковТЧ.Путь as Путь
|FROM
|    $Документ.__СканыДоков__ as ДокументСканыДоков
|LEFT JOIN 
|    $ДокументСтроки.__СканыДоков__ as ДокументСканыДоковТЧ ON ДокументСканыДоковТЧ.IDDoc = ДокументСканыДоков.IDDoc
|WHERE RIGHT($ДокументСканыДоков.Док, 9) IN (
|    SELECT
|        ГрафаОтбора.CHILDID
|    FROM
|        _1SCRDOC as ГрафаОтбора
|    WHERE
|        ГрафаОтбора.MDID = $ГрафаОтбора.Контрагент
|        AND ГрафаОтбора.PARENTVAL = :ВыбКонтрагент*
|        AND ГрафаОтбора.CHILD_DATE_TIME_IDDOC BETWEEN :ДатаНачала AND :ДатаКонца~
|)";

Разберем, как работает это решение. Функция RIGHT($ДокументСканыДоков.Док, 9) преобразует значение "17Y B5PS098 " в "B5PS098 ", что позволяет оператору IN успешно сопоставить его со значениями колонки CHILDID во вложенном запросе.

Решение 2: Оптимальное (Sargable) решение для высоконагруженных баз

Хотя решение с использованием функции RIGHT() является полностью рабочим и простым в реализации, оно имеет существенный недостаток с точки зрения производительности базы данных. Использование функций СУБД на левой стороне сравнения делает условие несаргабельным (non-sargable).

Это означает, что SQL Server не сможет использовать существующие индексы по полю реквизита Док (колонке SPxxxx в таблице DHxxxx документа __СканыДоков__). Базе данных придется выполнить полное сканирование таблицы (Table Scan или Index Scan) при каждом выполнении запроса. На базах данных объемом в десятки гигабайт это приведет к серьезному замедлению работы.

Чтобы сохранить высокую скорость выполнения запроса, левая часть сравнения должна оставаться «чистой», а конструирование 13-символьного значения должно происходить внутри подзапроса (на правой стороне сравнения). Для этого нам потребуется соединить таблицу графы отбора _1SCRDOC с журналом документов _1SJOURN, чтобы получить вид документа (поле IDDOCDEF):


ТекстЗапросаОптимальный = "
|SELECT
|    $ДокументСканыДоков.Док as [Док $Документ]
|    ,$ДокументСканыДоковТЧ.Имя as Имя
|    ,$ДокументСканыДоковТЧ.Путь as Путь
|FROM
|    $Документ.__СканыДоков__ as ДокументСканыДоков
|LEFT JOIN 
|    $ДокументСтроки.__СканыДоков__ as ДокументСканыДоковТЧ ON ДокументСканыДоковТЧ.IDDoc = ДокументСканыДоков.IDDoc
|WHERE $ДокументСканыДоков.Док IN (
|    SELECT
|        -- Конструируем 13-символьное значение: ВидДокумента (36-ричный) + IDDoc
|        dbo.fn_IddocdefTo36(Журнал.IDDOCDEF) + ГрафаОтбора.CHILDID
|    FROM
|        _1SCRDOC as ГрафаОтбора
|    INNER JOIN
|        _1SJOURN as Журнал ON Журнал.IDDOC = ГрафаОтбора.CHILDID
|    WHERE
|        ГрафаОтбора.MDID = $ГрафаОтбора.Контрагент
|        AND ГрафаОтбора.PARENTVAL = :ВыбКонтрагент*
|        AND ГрафаОтбора.CHILD_DATE_TIME_IDDOC BETWEEN :ДатаНачала AND :ДатаКонца~
|)";

Важное примечание: Функция dbo.fn_IddocdefTo36 — это пользовательская функция преобразования десятичного числа (IDDOCDEF) в 36-ричную систему счисления, принятую в 1С 7.7. Если в вашей базе нет такой функции, вы можете создать её на уровне SQL Server или воспользоваться предопределенными механизмами типизации ВК 1С++.

Синтаксическая магия препроцессора 1С++

Обратите внимание на использование спецсимволов препроцессора 1С++ в текстах наших запросов:

Префикс $ (например, $ДокументСканыДоков.Док или $ГрафаОтбора.Контрагент) — заставляет парсер автоматически заменить понятные имена метаданных 1С на физические имена таблиц и колонок в базе данных SQL (например, sp40969 или идентификатор графы отбора).
Суффикс * (например, :ВыбКонтрагент*) — дает команду парсеру автоматически типизировать передаваемый объект справочника 1С в СУБД-формат char(23). Препроцессор сам добавит системный префикс справочников B1, найдет внутренний идентификатор вида справочника контрагентов, ID самого элемента и заполнит оставшуюся длину строки пробелами. Разработчику не нужно формировать строку 'B1 4S CXI098 ' вручную.
Тильда ~ в конце параметра даты (:ДатаКонца~) — автоматически преобразует дату 1С в строковый формат времени SQL Server (YYYYMMDDHHMMSS), что гарантирует точное попадание документов, проведенных в последнюю секунду указанного дня, в диапазон выборки.

Используя данные рекомендации, вы сможете писать не просто работающие, но и максимально оптимизированные прямые запросы для 1С 7.7, обеспечивая стабильную работу системы даже на терабайтных базах данных.