Как получить 0 вместо NULL в запросе 1С при соединении со срезом последних цен номенклатуры?

← На главную

В процессе разработки отчетов и обработок в среде 1С:Предприятие программисты регулярно сталкиваются с необходимостью вывести полный список товаров из справочника или остатков и сопоставить его с актуальными ценами. При этом возникает классическая задача: если для какого-то товара цена определенного типа еще не установлена в системе, в результирующей таблице вместо пустого значения (NULL) должен отображаться красивый и аккуратный ноль (0).

На первый взгляд задача кажется тривиальной, однако попытки ее решения часто приводят к типичным ошибкам, из-за которых товары без цен вообще исчезают из результата запроса, либо отчет начинает работать крайне медленно. В этой статье мы подробно разберем, почему возникают эти ошибки, как работает механизм LEFT JOIN (Левое соединение) в СУБД, и как составить идеальный, быстрый и отказоустойчивый запрос.

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

Давайте проанализируем изначальный запрос, который часто пишут начинающие разработчики:


ВЫБРАТЬ
    ВЫБОР
        КОГДА ЦеныНоменклатурыСрезПоследних.Цена = NULL ТОГДА 0
        ИНАЧЕ ЦеныНоменклатурыСрезПоследних.Цена
    КОНЕЦ КАК Цена,
    товар.Ссылка ИЗ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Конец, ) КАК ЦеныНоменклатурыСрезПоследних
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК товар
        ПО ЦеныНоменклатурыСрезПоследних.Номенклатура = товар.Ссылка
ГДЕ
    ЦеныНоменклатурыСрезПоследних.ТипЦен = &ТипЦен

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

Ошибка 1. Неверное направление левого соединения. Механизм левого соединения работает так: из таблицы, которая указана слева (после ключевого слова ИЗ), выбираются абсолютно все записи. Из таблицы, которая указана справа (после ключевого слова ЛЕВОЕ СОЕДИНЕНИЕ), выбираются только те записи, которые соответствуют условию связи. Если соответствия нет, поля правой таблицы заполняются значениями NULL.

В ошибочном примере левой таблицей выступает РегистрСведений.ЦеныНоменклатуры.СрезПоследних, а правой — Справочник.Номенклатура. Это означает, что запрос выберет только те товары, по которым в регистре физически существует хотя бы одна запись о цене. Если товара нет в регистре цен, он вообще не попадет в выборку. Наша же цель — вывести все товары из справочника, независимо от наличия цен.

Ошибка 2. Сравнение с NULL через знак равенства. В языке запросов 1С (как и в стандартном SQL) значение NULL обозначает отсутствие данных, «ничто». Его нельзя сравнивать с помощью обычных операторов равенства (=) или неравенства (<>). Результатом любого сравнения значения с NULL через знак «равно» всегда будет ложь (FALSE). Проверить поле на пустоту можно только специальной конструкцией ЕСТЬ NULL или функцией ЕСТЬNULL().

Ошибка 3. Наложение отбора в секции ГДЕ на правую таблицу соединения. Это самая коварная ошибка. Секция ГДЕ применяется СУБД уже после того, как таблицы были соединены по правилу ЛЕВОЕ СОЕДИНЕНИЕ. Когда СУБД обрабатывает строку с товаром, у которого нет цены, на месте поля ТипЦен образуется значение NULL. Условие ГДЕ ЦеныНоменклатурыСрезПоследних.ТипЦен = &ТипЦен превращается в ГДЕ NULL = &ТипЦен, что дает ложь. В итоге СУБД просто отбрасывает эту строку. Таким образом, левое соединение фактически превращается во внутреннее (INNER JOIN), сводя на нет всю логику левого соединения.

Правильное решение: ЕСТЬNULL и отборы в параметрах виртуальной таблицы

Чтобы решить поставленную задачу корректно и обеспечить максимальную скорость работы СУБД, нам необходимо выполнить три действия:

Сделать ведущей (левой) таблицей справочник Справочник.Номенклатура (или временную таблицу с нужным списком товаров).
Перенести отбор по типу цен из секции ГДЕ непосредственно в параметры виртуальной таблицы СрезПоследних.
Использовать функцию ЕСТЬNULL() для замены пустых значений на 0.

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


ВЫБРАТЬ
    ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Цена,
    товар.Ссылка КАК Товар
ИЗ
    Справочник.Номенклатура КАК товар
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Конец, ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних
        ПО товар.Ссылка = ЦеныНоменклатурыСрезПоследних.Номенклатура

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

Таблица Справочник.Номенклатура находится слева. Мы гарантированно получим абсолютно все товары, существующие в базе данных.
Конструкция РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Конец, ТипЦен = &ТипЦен) ограничивает выборку цен на уровне самого регистра еще до выполнения соединения. Платформа 1С передает этот отбор прямо в СУБД в качестве подзапроса. СУБД рассчитает срез последних только для одного указанного типа цен, что сбережет оперативную память сервера и колоссально ускорит выполнение запроса.
Функция ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) проверяет: если в результате соединения цена для товара не найдена (вернулся NULL), то функция мгновенно подставит число 0. Если цена есть, она останется неизменной.

Оптимизация для высоконагруженных систем (Использование временных таблиц)

Если в вашей базе данных справочник номенклатуры содержит сотни тысяч или миллионы элементов, а вам нужно получить цены только для определенной группы товаров или для товаров из конкретного документа, соединять весь справочник со срезом последних крайне неэффективно. Оптимизатор СУБД (особенно на PostgreSQL) может построить неоптимальный план запроса.

В таких случаях профессиональные разработчики используют временные таблицы. Давайте разберем этот продвинутый метод по шагам:

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

Посмотрим на пример реализации этого алгоритма в пакете запросов:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Товар
ПОМЕСТИТЬ ВТ_НужныеТовары
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Родитель = &ГруппаТоваров
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Цена,
    ВТ_НужныеТовары.Товар КАК Товар
ИЗ
    ВТ_НужныеТовары КАК ВТ_НужныеТовары
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
        &Конец, 
        ТипЦен = &ТипЦен И Номенклатура В (ВЫБРАТЬ ВТ_НужныеТовары.Товар ИЗ ВТ_НужныеТовары КАК ВТ_НужныеТовары)
    ) КАК ЦеныНоменклатурыСрезПоследних
        ПО ВТ_НужныеТовары.Товар = ЦеныНоменклатурыСрезПоследних.Номенклатура

Обратите внимание на конструкцию отбора в виртуальной таблице: Номенклатура В (ВЫБРАТЬ ВТ_НужныеТовары.Товар ИЗ ВТ_НужныеТовары). Этот прием ограничивает выборку цен на этапе расчета среза последних только теми позициями, которые нам действительно нужны. Это гарантирует мгновенную работу отчета даже на терабайтных базах данных.

Учет характеристик номенклатуры

В реальных конфигурациях 1С (таких как Управление торговлей, ERP или Комплексная автоматизация) цены практически никогда не хранятся просто в разрезе номенклатуры. Обычно они детализируются до характеристик номенклатуры. Если мы не учтем этот момент, наш запрос либо вернет дубли строк, либо пустые значения цен.

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


ВЫБРАТЬ
    ВТ_НужныеТовары.Товар КАК Товар,
    ВТ_НужныеТовары.Характеристика КАК Характеристика,
    ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Цена
ИЗ
    ВТ_НужныеТовары КАК ВТ_НужныеТовары
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
        &Конец, 
        ТипЦен = &ТипЦен И Номенклатура В (ВЫБРАТЬ ВТ_НужныеТовары.Товар ИЗ ВТ_НужныеТовары КАК ВТ_НужныеТовары)
    ) КАК ЦеныНоменклатурыСрезПоследних
        ПО ВТ_НужныеТовары.Товар = ЦеныНоменклатурыСрезПоследних.Номенклатура
        И ВТ_НужныеТовары.Характеристика = ЦеныНоменклатурыСрезПоследних.Характеристика

Как работать с динамическими ценами в СКД?

Отдельный важный вопрос касается динамических цен. Динамические цены рассчитываются «на лету» по формулам (например, «Базовая цена + 10% наценки»). Физически записей таких цен в регистре сведений ЦеныНоменклатуры нет, поэтому простой срез последних по динамическому типу цен вернет пустоту.

Если вы разрабатываете отчет в Системе компоновки данных (СКД), у вас есть несколько путей решения этой задачи:

Способ 1. Использование связей наборов данных в СКД

Мы можем создать два независимых набора данных-запроса в схеме компоновки:

НаборДанных1: Выбирает список номенклатуры и базовую цену (которая реально хранится в регистре).
НаборДанных2: Выбирает правила расчета (проценты наценки, формулы).

На вкладке Связи наборов данных мы связываем их по полям Номенклатура и ВидЦены. В вычисляемых полях СКД мы пишем формулу расчета динамической цены, например: ЕСТЬNULL(БазоваяЦена, 0) * (1 + ЕСТЬNULL(ПроцентНаценки, 0) / 100). СКД сама выполнит расчет для каждой строки при выводе отчета пользователю.

Способ 2. Программное заполнение цен через API конфигурации

В современных типовых конфигурациях 1С расчет цен вынесен в отдельные специализированные модули. Чтобы не изобретать велосипед и не переписывать сложные формулы расчета в запросах СКД, правильным решением будет получить данные программно перед формированием отчета.

Для этого в модуле объекта отчета переопределяется событие ПриКомпоновкеРезультата. Мы программно вызываем общий модуль ценообразования (например, ЦенообразованиеКлиентСервер.ПолучитьТаблицуЦенНоменклатуры()), который автоматически рассчитывает все виды цен (включая динамические и формульные), заполняет временную таблицу и передает ее в СКД как внешний НаборДанных - Объект.

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