Как в sql запросе вместо null поставить 0 1с
Тип значения Null – это переменная или поле с отсутствующим значением. Если реквизит объекта имеет один определенный тип, например тип «Справочник.Договоры», то незаполненное значение будет равно пустой ссылке в запросе «Справочник.Договоры.ПустаяСсылка». Если реквизит имеет мультитип, то незаполненное значение данного реквизита будет равно значению «Неопределено», так как используемые в этом реквизите типы известны, но неизвестно, какой из пустых типов нужно использовать в данном случае. Для примера, в документе «ЗаказКлиента» реквизит «ДокументОснование» может быть и «ЗаданиеТорговомуПредставителю» и «КоммерческоеПредложениеКлиенту». В случаях же, когда вообще неизвестно, какие типы мы хотим получить, значением свойства является Null. В качестве примера, в котором в запросе есть Null, можно рассмотреть левое соединение таблиц, когда в присоединяемой таблице отсутствуют значения, удовлетворяющие условию соединения.
Пример кода: присоединение к таблице контрагентов таблицы договоров
Рис. 1 Левое соединение таблиц
Если у контрагента отсутствует договор, то при выполнении запроса получим следующие значения:
Как видим в запросе в 1С есть Null и это отображается пустой строкой.
В запросе сравнение любого значения с типом значения Null дает результат Ложь. Даже условие Null = Null является ложным.
2. Методы Есть Null и ЕстьNull
Для правильной обработки данных ситуаций созданы такие методы, как:
· Есть Null – это выражение условия, проверяющее, является ли выбранное значение типом значения Null. При проверке следует применять его. Сравнение вида (ДоговорКонтрагента.Ссылка = Null) не даст корректного результата.
Рис. 2 Пример кода для проверки типа значения Null
Для корректной работы запроса обязательно следует отслеживать места, где есть Null в запросе 1С, и заменять данное значение на какой-то определенный тип.
· ЕстьNull
Этот метод позволяет менять значение Null на нужное нам значение.
Пример кода для запроса в 1С с ЕстьNull
Рис. 3 Код для ЕстьNull в запросе
Отсутствующую строку с помощью метода ЕстьNull в 1С можно заменить на пустую строку, отсутствующую цифру на ноль, ссылку на пустую ссылку, дату на пустую дату. Например ЕстьNull(“ДоговорыКонтрагентов.Номер”, “”), ЕстьNull(“ДоговорыКонтрагентов.СуммаДоговора”, 0), ЕстьNull(“ДоговорыКонтрагентов.Партнер”, Значение(Справочник.Партнеры.ПустаяСсылка)), ЕстьNull(“ДоговорыКонтрагентов.Дата”, ДАТАВРЕМЯ(1,1,1,0,0,0)).
Тип значения Null также может уже содержаться в используемом в запросе реквизите, попав туда в результате некорректной записи программным кодом, смене типа реквизита в конфигураторе либо при неправильном обмене данными между базами. В этих случаях тоже можно воспользоваться выражением ЕстьNull в запросе, но лучше исправлять такие реквизиты.
Специалист компании «Кодерлайн»
Вас могут заинтересовать следующие статьи:
94 [PROP_CODE] => TAGS2 [TITLE] => Вас могут заинтересовать следующие семинары: ) --> 95 [PROP_CODE] => TAGS [TITLE] => Вас могут заинтересовать следующие вебинары: ) -->
Вас могут заинтересовать следующие вебинары:
Программирование в 1С [1] => 1С:Бухгалтерия [2] => 1С:Управление производственным предприятием [3] => 1С: Розница [4] => 1С: CRM [5] => 1С: Управление торговлей [6] => Бюджетирование [7] => СКД [8] => 1С: Конвертация данных [9] => Администрирование 1С [10] => Конвертация данных [11] => Лицензии 1С [12] => Оптимизация [13] => 1С: Документооборот [14] => 1С: ERP [15] => Транспортная логистика [16] => Техническое задание [17] => Продажи [18] => Ценообразование [19] => Планирование [20] => MS SQL Server [21] => Полезные обработки [22] => МСФО [23] => Отчеты в 1С [24] => XML [25] => Внедрение ERP [26] => Перенос данных [27] => Торговое оборудование [28] => РСБУ [29] => 1С:Управление корпоративными финансами [30] => 1С: Колледж [31] => 1С: Аренда и управление недвижимостью [32] => Финансовый учет ) -->
NULL – отсутствующие значения.
Не путать с нулевым значением! NULL – это не число, не равно пробелу, пустой ссылке, Неопределено.
NULL – типообразующее значение, т.е. есть тип NULL и единственное значение этого типа.
NULL значения появляются в запросе в следующих ситуациях:
а) Внешнее соединение, при котором не было найдено соответствующей записи в другой таблице (при левом – во второй, при правом – в первой, при полном – в обоих)
б) Обращение к реквизитам элементов для группы и наоборот.
в) NULL в списке полей выборки (ВЫБРАТЬ)
г) Обращение к реквизитам для битой ссылки
ЕСТЬ NULL используется в операторе ВЫБРАТЬ (как бы проверя, что значение это есть пустое ( Значение ЕСТЬ NULL )):
Код 1C v 8.х
еще пример:
Код 1C v 8.х
Функция ЕСТЬNULL (значение, РезультатЕслиNULL) возвращает значение своего первого параметра, в случае если он не равен NULL, и значение второго параметра в противном случае
Является свернутым ВЫБОР…КОНЕЦ, но ЕСТЬNULL предпочтительнее.
Код 1C v 8.х
еще пример:
Код 1C v 8.х
В данном примере получаются все элементы справочника номенклатуры, после чего, для каждой номенклатуры из регистра накопления получаются текущие остатки. Т.к. для номенклатуры, по которой отсутствуют остатки, виртуальная таблица остатков не запись вернет, то в результате соединения в поле "УчетНоменклатурыОстатки.КоличествоОстаток" будут значения NULL для номенклатуры, по которой не было остатков. Для того чтобы вместо значения NULL в результате запроса присутствовало значение 0, мы использовали функцию ЕСТЬNULL(), которая осуществит желаемую замену.
ЕСТЬNULL отличается от ВЫБОР по следующим причинам:
а) При ЕСТЬNULL лучше читается запрос (проще)
б) При ЕСТЬNULL, если проверяется сложное выражение, то работает быстрее, поскольку вычисляется один раз
в) При ЕСТЬNULL выражение замены приводится к типу проверяемого выражения, если оно имеет тип Строка (длина) или Число (разрядность).
Нельзя проверять значения на NULL обычным равенством, потому что в SQL действует трехзначная логика – Истина, Ложь, NULL, и результатом такого сравнения будет UNKNOWN, что в 1С 8.0 аналогично ЛОЖЬ.
NULL <> 0, поэтому при левых внешних соединениях спр. Номенклатура с таблицами остатков, цен, Контрагентов со взаиморасчетами при отсутствии таких записей там будет NULL, который не равен 0. Лучшее решение – ЕСТЬNULL
Похожие FAQ
Еще в этой же категории
17 правил для составления оптимального ЗАПРОСа к данным базы 1С 44
Для формирования и выполнения запросов к таблицам базы данных в платформе 1С используется специальный объект языка программирования Запрос . Создается этот объект вызовом конструкции Новый Запрос . Запрос удобно использовать, когда требуется получ Нарастающий итог в запросе 23
Необходимо использовать левое соединение таблицы с самой собой. ВЫБРАТЬ Обороты.Период, Обороты.Номенклатура, Обороты.Количество ПОМЕСТИТЬ ВТдвижения ИЗ РегистрНакопления.Обороты КАК Обороты ; //////////////////////////////////////////////// Полезные сведения о языке запросов 1С 8.х 22
В статье приведены полезные приемы при работе с запросами 1С v.8.2, а также сведения, которые не так хорошо известны о языке запросов. Я не стремлюсь дать полное описание языка запросов, а хочу остановиться лишь на некоторых моментах, которые для ко Оператор ПОДОБНО 19
ПОДОБНО - Оператор проверки строки на подобие шаблону. Аналог LIKE в SQL. Оператор ПОДОБНО позволяет сравнить значение выражения, указанного слева от него, со строкой шаблона, указанной справа. Значение выражения должно иметь тип строка. Если з Как в запросе получить Вид (тип) Документа? 15
Столкнулся с задачей, нужно было выбрать все документы по выплате и сгруппировать их по типу документа! Перелистав все ЖКК и интернет, понял что простого способа получить Вид документа в запросе нет :( Пришлось сделать так: Запрос = Новый Запрос; Посмотреть все в категории Запросы
NULL – отсутствующие значения.
Не путать с нулевым значением! NULL – это не число, не равно пробелу, пустой ссылке, Неопределено.
NULL – типообразующее значение, т.е. есть тип NULL и единственное значение этого типа.
NULL значения появляются в запросе в следующих ситуациях:
а) Внешнее соединение, при котором не было найдено соответствующей записи в другой таблице (при левом – во второй, при правом – в первой, при полном – в обоих)
б) Обращение к реквизитам элементов для группы и наоборот.
в) NULL в списке полей выборки (ВЫБРАТЬ)
г) Обращение к реквизитам для битой ссылки
ЕСТЬ NULL используется в операторе ВЫБРАТЬ (как бы проверя, что значение это есть пустое ( Значение ЕСТЬ NULL )):
Код 1C v 8.х
еще пример:
Код 1C v 8.х
Функция ЕСТЬNULL (значение, РезультатЕслиNULL) возвращает значение своего первого параметра, в случае если он не равен NULL, и значение второго параметра в противном случае
Является свернутым ВЫБОР…КОНЕЦ, но ЕСТЬNULL предпочтительнее.
Код 1C v 8.х
еще пример:
Код 1C v 8.х
В данном примере получаются все элементы справочника номенклатуры, после чего, для каждой номенклатуры из регистра накопления получаются текущие остатки. Т.к. для номенклатуры, по которой отсутствуют остатки, виртуальная таблица остатков не запись вернет, то в результате соединения в поле "УчетНоменклатурыОстатки.КоличествоОстаток" будут значения NULL для номенклатуры, по которой не было остатков. Для того чтобы вместо значения NULL в результате запроса присутствовало значение 0, мы использовали функцию ЕСТЬNULL(), которая осуществит желаемую замену.
ЕСТЬNULL отличается от ВЫБОР по следующим причинам:
а) При ЕСТЬNULL лучше читается запрос (проще)
б) При ЕСТЬNULL, если проверяется сложное выражение, то работает быстрее, поскольку вычисляется один раз
в) При ЕСТЬNULL выражение замены приводится к типу проверяемого выражения, если оно имеет тип Строка (длина) или Число (разрядность).
Нельзя проверять значения на NULL обычным равенством, потому что в SQL действует трехзначная логика – Истина, Ложь, NULL, и результатом такого сравнения будет UNKNOWN, что в 1С 8.0 аналогично ЛОЖЬ.
NULL <> 0, поэтому при левых внешних соединениях спр. Номенклатура с таблицами остатков, цен, Контрагентов со взаиморасчетами при отсутствии таких записей там будет NULL, который не равен 0. Лучшее решение – ЕСТЬNULL
Похожие FAQ
Еще в этой же категории
17 правил для составления оптимального ЗАПРОСа к данным базы 1С 44
Для формирования и выполнения запросов к таблицам базы данных в платформе 1С используется специальный объект языка программирования Запрос . Создается этот объект вызовом конструкции Новый Запрос . Запрос удобно использовать, когда требуется получ Нарастающий итог в запросе 23
Необходимо использовать левое соединение таблицы с самой собой. ВЫБРАТЬ Обороты.Период, Обороты.Номенклатура, Обороты.Количество ПОМЕСТИТЬ ВТдвижения ИЗ РегистрНакопления.Обороты КАК Обороты ; //////////////////////////////////////////////// Полезные сведения о языке запросов 1С 8.х 22
В статье приведены полезные приемы при работе с запросами 1С v.8.2, а также сведения, которые не так хорошо известны о языке запросов. Я не стремлюсь дать полное описание языка запросов, а хочу остановиться лишь на некоторых моментах, которые для ко Оператор ПОДОБНО 19
ПОДОБНО - Оператор проверки строки на подобие шаблону. Аналог LIKE в SQL. Оператор ПОДОБНО позволяет сравнить значение выражения, указанного слева от него, со строкой шаблона, указанной справа. Значение выражения должно иметь тип строка. Если з Как в запросе получить Вид (тип) Документа? 15
Столкнулся с задачей, нужно было выбрать все документы по выплате и сгруппировать их по типу документа! Перелистав все ЖКК и интернет, понял что простого способа получить Вид документа в запросе нет :( Пришлось сделать так: Запрос = Новый Запрос; Посмотреть все в категории Запросы
Вместо NULL, как я могу показать `0` в результате с оператором SELECT sql?
Вместо NULL, как мне показать 0 в результате с оператором SELECT sql?
Например, хранимый процесс имеет оператор выбора, например, следующий:
Который дает мне вывод как
Теперь вместо того, чтобы показывать NULL , как я могу показать цену как 0 ?
TablePlus
When selecting data from a table, there might be some NULL values that you don’t want to show, or you want to replace it with 0 for the aggregate functions. Then you can use COALESCE to replace the NULL with 0.
For example, we have the table salaries with 5 columns: emp_no , from_date , to_date , salary , bonus . But the bonus column is optional and may contain NULL values.
Run this SELECT … COALESCE … statement to return 0 as the alternative value when bonus value is NULL:
In MySQL you can also use IFNULL function to return 0 as the alternative for the NULL values:
In MS SQL Server, the equivalent is ISNULL function:
In Oracle, you can use NVL function:
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Need a quick edit on the go? Download for iOS
Замена NULL на 0 в запросе SQL-сервера
Я разработал запрос, и в результатах для первых трех столбцов я получаю NULL . Как я могу заменить его на 0 ?
Если вы хотите заменить столбец, возможно, null чем-то другим, используйте IsNull.
Это поместит 0 в myColumn, если он изначально равен нулю.
Следуя предыдущим ответам, я терял имя столбца в базе данных SQL-сервера, однако следование этому синтаксису помогло мне также сохранить имя столбца
Проблема здесь в том, что без оператора else вы обязательно получите Null, если состояние выполнения не соответствует статусу, указанному в описании столбца. Добавление чего-либо к Null приведет к Null, и это проблема с этим запросом.
Добавьте else в операторы case, чтобы они по умолчанию равнялись нулю, если условие проверки не найдено. В настоящий момент, если условие проверки не найдено, в функцию SUM () передается NULL.
Если вы используете Presto, AWS Athena и т. Д., Функция ISNULL () отсутствует. Вместо этого используйте:
Используйте COALESCE , который возвращает первое ненулевое значение, например.
Устанавливает Succeeded как 0, если возвращается как NULL .
Оберните свой столбец этим кодом.
Может быть, проверьте, почему вы получаете нули
Простой способ
Когда вы говорите первые три столбца, вы имеете в виду свои столбцы SUM ? Если да, добавьте ELSE 0 в свои CASE заявления. SUM значения NULL равно NULL .
Хотя при суммировании when condition then 1 вы можете так же легко изменить sum на count — например:
( Count(null) возвращает 0, а sum(null) возвращает ноль.)
Вы можете использовать оба этих метода, но есть различия:
Сравнение COALESCE () и ISNULL ():
Функция ISNULL и выражение COALESCE имеют аналогичные цель, но может вести себя по-разному.
Поскольку ISNULL — это функция, она оценивается только один раз. В виде как описано выше, входные значения для выражения COALESCE могут быть оценивается несколько раз.
Определение типа данных результирующего выражения отличается. ISNULL использует тип данных первого параметра, за ним следует COALESCE. правила выражения CASE и возвращает тип данных значения с высший приоритет.
Возможность NULL результата выражения различна для ISNULL и КОАЛЕС. Возвращаемое значение ISNULL всегда считается NOT NULLable. (при условии, что возвращаемое значение не обнуляемое), тогда как COALESCE с ненулевыми параметрами считается NULL. Так что выражения ISNULL (NULL, 1) и COALESCE (NULL, 1), хотя эквивалент имеют разные значения допустимости пустых значений. Это делает разница, если вы используете эти выражения в вычисляемых столбцах, создание ключевых ограничений или создание возвращаемого значения скалярной UDF детерминированный, чтобы его можно было проиндексировать, как показано ниже пример.
— Этот оператор завершается ошибкой, потому что PRIMARY KEY не может принимать значения NULL — и допускающее значение NULL выражение COALESCE для col2 — оценивается как NULL.
— Этот оператор завершается успешно, потому что возможность значения NULL для функции ISNULL оценивается как NOT NULL.
Проверки ISNULL и COALESCE также различны. Например, значение NULL для ISNULL преобразуется в int, тогда как для COALESCE, вы должны указать тип данных.
ISNULL принимает только 2 параметра, тогда как COALESCE принимает переменную количество параметров.
Я разработал запрос, и в результатах для первых трех столбцов я получаю NULL . Как я могу заменить его на 0 ?
когда вы хотите заменить, возможно
функция ISNULL и выражение COALESCE имеют аналогичный цели, но может вести себя по-разному.
поскольку ISNULL-это функция, она вычисляется только один раз. Как описанные выше входные значения для выражения COALESCE могут быть оценено несколько раз.
определение типа данных результирующего выражения отличается. ISNULL использует тип данных первого параметра, КОАЛЕСЦИРУЕТ следующим образом выражение CASE управляет и возвращает тип данных значения с помощью более высокий приоритет.
поддерживает ли это выражение, результат индивидуален для функции isnull и СПЛОТИТЬСЯ. Возвращаемое значение функция isnull всегда считается не допускает значения null (предполагая, что возвращаемое значение не является нулевым), тогда как СПЛОТИТЬСЯ с ненулевыми параметрами считается NULL. Так выражения ISNULL(NULL, 1) и COALESCE (NULL, 1) хотя эквиваленты имеют разные значения обнуления. Это делает разница, если вы используете эти выражения в вычисляемых столбцах, создание ключевых ограничений или возвращаемое значение скалярной пользовательской функции детерминированный, чтобы его можно было индексировать, как показано ниже образец.
проверки для ISNULL и COALESCE также различны. Например, значение NULL для ISNULL преобразуется в int, тогда как для COALESCE, необходимо указать тип данных.
ISNULL принимает только 2 параметра в то время как COALESCE принимает переменную число параметров.
Если вам нужно знать больше вот полный документ от msdn.
С coalesce :
хотя, где подводятся when condition then 1 , вы могли бы так же легко изменить sum до count , например:
( Count(null) возвращает 0, в то время как sum(null) возвращает значение null.)
Читайте также: