При работе с запросами в 1С часто возникает ситуация, когда после использования ЛЕВОЕ СОЕДИНЕНИЕ в результирующей выборке появляются значения NULL. Это происходит, когда для записи из левой таблицы не находится соответствующей записи в правой. В отчетах или при дальнейших вычислениях такие значения неудобны, и их требуется заменить на ноль. Рассмотрим на конкретном примере, как это сделать правильно, и разберем несколько подходов к решению этой задачи. Для практического освоения подобных примеров начинающим разработчикам пригодится готовая демонстрационная база для изучения языка запросов.
Проанализируем исходную ситуацию. Автор пытается получить данные о количестве работающих и уволенных сотрудников, сгруппированные по возрасту. Для этого он сначала получает две временные таблицы: ВТРаботающие и ВТУволенные, а затем соединяет их левым соединением.
ВЫБРАТЬ
ВТРаботающие.Возраст КАК Возраст,
ВТРаботающие.КолВо КАК Работающие,
ВТУволенные.КолВо КАК Уволенные
ИЗ
ВТРаботающие КАК ВТРаботающие
ЛЕВОЕ СОЕДИНЕНИЕ ВТУволенные КАК ВТУволенные
ПО (ВТРаботающие.Возраст = ВТУволенные.Возраст)
УПОРЯДОЧИТЬ ПО
Возраст
Проблема в том, что если для какой-то возрастной группы есть работающие сотрудники, но нет уволенных, поле Уволенные будет содержать NULL. Давайте выясним, как это исправить.
Самый прямой и очевидный способ заменить NULL — это использовать встроенную функцию языка запросов ЕСТЬNULL(). Она принимает два параметра: первое — это проверяемое выражение, второе — значение, которое будет возвращено, если первое выражение равно NULL.
Применим эту функцию к нашему запросу:
ВЫБРАТЬ
ВТРаботающие.Возраст КАК Возраст,
ВТРаботающие.КолВо КАК Работающие,
ЕСТЬNULL(ВТУволенные.КолВо, 0) КАК Уволенные
ИЗ
ВТРаботающие КАК ВТРаботающие
ЛЕВОЕ СОЕДИНЕНИЕ ВТУволенные КАК ВТУволенные
ПО (ВТРаботающие.Возраст = ВТУволенные.Возраст)
УПОРЯДОЧИТЬ ПО
Возраст
Важный момент: после выполнения такого запроса в консоли запросов или в отчете СКД вы можете по-прежнему видеть пустые ячейки вместо нулей. Это не ошибка в запросе! Дело в том, что платформа 1С по умолчанию может отображать числовые поля со значением 0 как пустую строку для лучшей читаемости. Данные в поле действительно равны нулю, но их представление отличается. Чтобы исправить именно отображение, переходим к следующему решению.
Если ваша задача — корректно отобразить данные в отчете, построенном с помощью Системы Компоновки Данных (СКД), то лучшим решением будет настройка формата поля, а не усложнение запроса. Этот подход разделяет логику получения данных и их визуальное представление. Для решения других задач визуализации ресурсов средствами компоновки без корректировки кода можно использовать инструмент для настройки вывода ресурсов в СКД без изменения структуры запроса.
Разберем по шагам, как это сделать:
ЧН=0 (расшифровывается как "Число Нулевое = 0"). Можно также задать более сложный формат, например: ЧДЦ=2; ЧН=0; ЧГ=.После применения этой настройки СКД будет принудительно выводить "0" в ячейках, где значение равно нулю, вместо того чтобы оставлять их пустыми.
Этот подход является более кардинальным и изменяет саму структуру запроса. Вместо того чтобы соединять две предварительно сгруппированные таблицы, мы сначала соберем все "сырые" данные в одну общую таблицу, а уже потом выполним группировку. Этот метод часто оказывается более производительным на больших объемах данных, так как избегает операции соединения (JOIN).
Логика следующая:
ОБЪЕДИНИТЬ ВСЕ.Посмотрим на пример такого запроса:
ВЫБРАТЬ
(РАЗНОСТЬДАТ(КадроваяИсторияСотрудниковСрезПоследних.ФизическоеЛицо.ДатаРождения, &НачалоГода, ГОД) - 1) КАК Возраст,
1 КАК КолВоРаботают,
0 КАК КолВоУволены
ПОМЕСТИТЬ втОбщая
ИЗ
РегистрСведений.КадроваяИсторияСотрудников.СрезПоследних(&НачалоГода, ) КАК КадроваяИсторияСотрудниковСрезПоследних
ГДЕ
КадроваяИсторияСотрудниковСрезПоследних.ВидСобытия <> &ВидСобытияУвольнение
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
(РАЗНОСТЬДАТ(КадроваяИсторияСотрудниковСрезПоследних.ФизическоеЛицо.ДатаРождения, &ДатаСреза, ГОД) - 1),
0,
1
ИЗ
РегистрСведений.КадроваяИсторияСотрудников.СрезПоследних(&ДатаСреза, ) КАК КадроваяИсторияСотрудниковСрезПоследних
ГДЕ
КадроваяИсторияСотрудниковСрезПоследних.ВидСобытия = &ВидСобытияУвольнение
И НЕ КадроваяИсторияСотрудниковСрезПоследних.Регистратор.СтатьяТКРФ В (&СтатьяТКРФ)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
втОбщая.Возраст КАК Возраст,
СУММА(втОбщая.КолВоРаботают) КАК Работающие,
СУММА(втОбщая.КолВоУволены) КАК Уволенные
ИЗ
втОбщая КАК втОбщая
СГРУППИРОВАТЬ ПО
втОбщая.Возраст
УПОРЯДОЧИТЬ ПО
Возраст
Такой запрос по своей структуре исключает появление NULL, так как для каждой возрастной группы всегда будут числовые значения (пусть даже и нулевые) в обеих колонках.
В качестве альтернативы функции ЕСТЬNULL можно использовать более гибкую конструкцию ВЫБОР ... КОГДА ... ТОГДА ... ИНАЧЕ ... КОНЕЦ, которую часто содержит любая шпаргалка по языку запросов. Она позволяет описывать более сложные условия.
ВЫБРАТЬ
ВТРаботающие.Возраст КАК Возраст,
ВТРаботающие.КолВо КАК Работающие,
ВЫБОР
КОГДА ВТУволенные.КолВо ЕСТЬ NULL
ТОГДА 0
ИНАЧЕ ВТУволенные.КолВо
КОНЕЦ КАК Уволенные
ИЗ
ВТРаботающие КАК ВТРаботающие
ЛЕВОЕ СОЕДИНЕНИЕ ВТУволенные КАК ВТУволенные
ПО (ВТРаботающие.Возраст = ВТУволенные.Возраст)
Для простой замены NULL на ноль эта конструкция избыточна, и ЕСТЬNULL выглядит лаконичнее. Однако ее стоит иметь в виду для случаев, когда требуется более сложная логика обработки значений.
Подведем итог: для решения задачи замены NULL на 0 у вас есть несколько инструментов. Если проблема только в отображении данных в отчете СКД, правильнее всего настроить формат поля. Если же вам нужны именно нулевые значения на уровне данных для дальнейших вычислений, используйте функцию ЕСТЬNULL. А для оптимизации сложных запросов или при работе с большими объемами данных рассмотрите возможность рефакторинга с использованием ОБЪЕДИНИТЬ ВСЕ.