Значение в запросе 1С: ЗНАЧЕНИЕ, ВЫБОР, ЕСТЬNULL и группировка

Задача: сравнивать с предопределёнными значениями и считать итоги

В запросах 1С регулярно нужно отобрать документы с определённым статусом, взять предопределённый склад, подставить ноль вместо отсутствующей суммы, посчитать количество клиентов и сумму продаж по каждому. Для этого в языке запросов есть функция ЗНАЧЕНИЕ(), конструкция ВЫБОР, функция ЕСТЬNULL и агрегатные функции с группировкой. Ниже каждая из них — с рабочим кодом и подводными камнями. Основы работы с объектом Запрос и параметрами описаны в статье про запросы в 1С.

ЗНАЧЕНИЕ(): значение в запросе 1С

Функция ЗНАЧЕНИЕ() подставляет в текст запроса константу: элемент перечисления, предопределённый элемент справочника, план видов характеристик или счёт плана счетов, а также пустую ссылку. Аргумент записывается как путь по именам метаданных — без кавычек и без вычислений.

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

Имена в примере типичны для торговых конфигураций; в вашей базе подставьте реальные имена объектов. Так выглядят остальные формы записи:

// Предопределённый счёт плана счетов
РегистрБухгалтерии.Хозрасчетный.Остатки(&Дата, Счет = ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Касса)) КАК Остатки

// Системное перечисление
Движения.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)

// Предопределённый элемент плана видов характеристик
Свойства.Ссылка = ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыСубконтоХозрасчетные.Номенклатура)

// Пустые ссылки на документ и справочник
Реализации.ДокументОснование = ЗНАЧЕНИЕ(Документ.ЗаказКлиента.ПустаяСсылка)
Номенклатура.Родитель = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)

Пустая ссылка — частый способ проверить, что реквизит не заполнен: Поле = ЗНАЧЕНИЕ(Справочник.X.ПустаяСсылка). Для реквизитов составного типа и особенностей сравнения ссылок есть отдельные нюансы, их удобнее разбирать отдельно.

ЗНАЧЕНИЕ или параметр

У ЗНАЧЕНИЕ() есть ограничение: оно работает только с предопределёнными элементами и пустыми ссылками, то есть с теми, чьи имена известны при написании запроса. Обычный элемент справочника — «ООО Ромашка», созданный пользователем, — через ЗНАЧЕНИЕ() не получить: у него нет имени в метаданных. Также нельзя подставить имя элемента из переменной.

В таких случаях значение передают параметром:

Запрос.УстановитьПараметр("Статус", Перечисления.СтатусыЗаказовКлиентов.Закрыт);
Запрос.УстановитьПараметр("Контрагент", Контрагент);

// Статус выбирается по ходу выполнения
Если ТолькоОткрытые Тогда
    Запрос.УстановитьПараметр("Статус", Перечисления.СтатусыЗаказовКлиентов.ВРаботе);
КонецЕсли;

Практическое правило: если значение фиксировано в самом алгоритме — используйте ЗНАЧЕНИЕ(), текст запроса получается самодокументируемым. Если значение пришло извне, из настроек или условия кода — параметр. Для систематического выбора из списка статусов подойдёт параметр-массив с оператором В (&Статусы).

Несколько значений: оператор В

Когда подходящих значений несколько, не нужно строить цепочку из ИЛИ. Оператор В принимает список литералов ЗНАЧЕНИЕ() либо параметр-массив. Обратная проверка записывается как НЕ ... В (...).

ГДЕ
    Заказы.Статус В (
        ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовКлиентов.ВРаботе),
        ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовКлиентов.КОтгрузке))
    И НЕ Заказы.Склад В (&Склады)

Параметр &Склады здесь получает массив ссылок или список значений: Запрос.УстановитьПараметр("Склады", МассивСкладов). Если массив пустой, условие В не выполнится ни для одной строки, а НЕ В — выполнится для всех; учитывайте это при динамическом отборе.

ВЫБОР КОГДА ТОГДА ИНАЧЕ

Конструкция ВЫБОР возвращает одно из значений в зависимости от условий — аналог Если ... ИначеЕсли ... Иначе. Проверяются ветки сверху вниз, срабатывает первая подошедшая. Ветка ИНАЧЕ необязательна, но если её нет и ни одно условие не выполнилось, результатом будет NULL.

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

Результаты всех веток должны иметь совместимые типы: смешивание строк и чисел в одном ВЫБОР даёт неожиданные результаты или ошибку. Самое полезное применение ВЫБОР — условные суммы внутри агрегатных функций, когда за один проход нужно получить несколько показателей:

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

NULL в запросе: ЕСТЬNULL и ЕСТЬ NULL

NULL в запросе — это «значение отсутствует». Он появляется, когда левое (правое, полное) соединение не нашло пары для строки, а также при агрегировании пустого набора значений. Любое сравнение с NULL через = или <> даёт «неизвестно» и строка в выборку не попадает, поэтому Поле = NULL никогда не сработает. Для проверки есть оператор ЕСТЬ NULL, а для замены — функция ЕСТЬNULL(Выражение, Замена). Не путайте: первая запись — оператор из двух слов, вторая — функция, пишется слитно.

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

Контрагенты без продаж остаются в результате: счётчик у них равен 0, а сумма благодаря ЕСТЬNULL — нулю, а не NULL. Чтобы, наоборот, оставить только таких контрагентов, добавьте условие ГДЕ Реализации.Ссылка ЕСТЬ NULL (и уберите группировку). Замена в ЕСТЬNULL должна быть того же типа, что и поле: для сумм — число, для дат — ДАТАВРЕМЯ(1, 1, 1), для строк — пустая строка.

Агрегатные функции и количество

Агрегатные функции сворачивают множество строк в одно значение: СУММА, МАКСИМУМ, МИНИМУМ, СРЕДНЕЕ, КОЛИЧЕСТВО. У КОЛИЧЕСТВО три формы, и они считают разное:

• КОЛИЧЕСТВО(*) — число строк в группе, включая строки с пустыми значениями;
• КОЛИЧЕСТВО(Поле) — число строк, где Поле не равно NULL;
• КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Поле) — число уникальных значений поля.

Разница важна при левом соединении: КОЛИЧЕСТВО(*) по контрагенту без документов вернёт 1 (одна строка с NULL), а КОЛИЧЕСТВО(Реализации.Ссылка) — правильный 0. СРЕДНЕЕ, как и остальные агрегаты, пропускает NULL.

СГРУППИРОВАТЬ ПО и ИМЕЮЩИЕ

Если в списке полей есть агрегатные функции, все остальные поля должны быть перечислены в СГРУППИРОВАТЬ ПО, иначе запрос не пройдёт проверку. Условие по результату агрегата ставится не в ГДЕ, а в ИМЕЮЩИЕ: ГДЕ фильтрует строки до группировки, ИМЕЮЩИЕ — группы после неё.

ВЫБРАТЬ
    Реализация.Контрагент КАК Контрагент,
    КОЛИЧЕСТВО(*) КАК Документов,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Реализация.Склад) КАК Складов,
    СУММА(Реализация.СуммаДокумента) КАК Сумма,
    СРЕДНЕЕ(Реализация.СуммаДокумента) КАК СреднийЧек,
    МАКСИМУМ(Реализация.Дата) КАК ПоследняяПродажа,
    МИНИМУМ(Реализация.Дата) КАК ПерваяПродажа
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
    Реализация.Проведен
СГРУППИРОВАТЬ ПО
    Реализация.Контрагент
ИМЕЮЩИЕ
    СУММА(Реализация.СуммаДокумента) > 100000
УПОРЯДОЧИТЬ ПО
    Сумма УБЫВ

Запрос вернёт только контрагентов, у которых сумма проведённых продаж превышает 100 000, — с числом документов, числом складов отгрузки, средним чеком и крайними датами. Условие по проведённости стоит в ГДЕ, потому что относится к исходным документам, а не к итогу.

Типичные ошибки

• Попытка получить обычный элемент справочника через ЗНАЧЕНИЕ() — работает только для предопределённых; остальные передавайте параметром.
• Сравнение Поле = NULL вместо Поле ЕСТЬ NULL — условие никогда не выполняется.
• Условие по левому соединению в ГДЕ вместо ПО — соединение вырождается во внутреннее и строки без пары пропадают.
• КОЛИЧЕСТВО(*) по таблице, присоединённой слева, — считаются и пустые строки.
• Агрегат в ГДЕ — используйте ИМЕЮЩИЕ или вложенный запрос.
• Поле в списке выбора не включено в СГРУППИРОВАТЬ ПО — запрос не выполняется.
• Сумма без ЕСТЬNULL при отсутствии строк даёт NULL, и дальнейшая арифметика тоже превращается в NULL.

Конструкции: когда что применять

Задача	Конструкция
Константа-статус, предопределённый элемент	ЗНАЧЕНИЕ(Перечисление.X.Y)
Значение из кода, обычный элемент справочника	Параметр &Имя
Проверка «не заполнено»	= ЗНАЧЕНИЕ(Справочник.X.ПустаяСсылка)
Ноль вместо отсутствующей суммы	ЕСТЬNULL(Поле, 0)
Найти строки без пары	Поле ЕСТЬ NULL
Разные значения по условию	ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ... КОНЕЦ
Фильтр по итогу группы	ИМЕЮЩИЕ СУММА(...) > X