Ms sql ifnull: Функция NULLIF (Transact-SQL) — SQL Server

деление на 0 без ошибки

Время прочтения: 4 мин.

В процессе работы c данными в SQL Server, мы столкнулись с такой ситуацией. Одним из промежуточных шагов в нашей задаче было выполнение простого арифметического действия, из данных в виде целых чисел, загружаемых в таблицу SQL, значения которых   участвовали в делении. В какой-то момент времени выполнение всего кода могло быть прервано сообщением об ошибке из-за того, что знаменатель принимал значение 0. Как этого избежать подобной ситуации, я расскажу в этой статье.

Обычно при делении не возникает проблем, и мы получаем соотношение двух величин, если знаменатель не равен нулю:

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 5;
select @vol_1/@vol_2 ratio_vol;

Соотношение значений вычисляется корректно:

При значении @vol_2 = 0 получаем сообщение об ошибке:

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select @vol_1/@vol_2 ratio_vol;

Для того, чтобы ошибка не возникала при выполнении запроса предлагается предусмотреть механизм, позволяющий справляться с условием, когда значение @vol_2 станет равным нулю

1. Применение функции NULLIF.

Синтаксис NULLIF следующий:

NULLIF(expr1, expr2)

При равенстве значений двух аргументов, возвращается значение NULL.

Например:

select NULLIF (55, 55) result;

Результат запроса:

Если значения аргументов не равны, возвращается значение первого аргумента (expr1).

select NULLIF (12, 55) result;

Изменим этот запрос, добавив в него NULLIF, для обхода ошибки деления на ноль.

Логика использования функции NULLIF для задачи деления на ноль следующая:

• используем в знаменателе функцию NULLIF с нулевым значением ее второго аргумента
• если значение первого аргумента функции NULLIF также равно нулю, то возвращается значение NULL, и тогда в SQL Server, если мы разделим число на значение NULL, на выходе получим NULL.
• если значение первого аргумента не равно нулю, возвращается значение первого аргумента функции NULLIF, и деление выполняется как стандартная операция деления.

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select @vol_1/NULLIF (@vol_2, 0) ratio_vol;

Ниже, результат работы такого кода (в знаменателе – значение NULL):

Добавим в код функцию ISNULL, для того, чтобы вместо значения NULL в выводе результата вычисления получать 0.

Эта функция заменяет NULL значение в expr1 и возвращает значение expr2 в качестве вывода.

Логика запроса с функциями ISNULL и NULLIF такая:

• первый аргумент ((@vol_1/ NULLIF (@vol_2,0)) вернет значение NULL;
• для функции ISNULL указываем нулевое значение второго аргумента;
• так как первый аргумент — NULL, то вывод всего запроса равен нулю, т.е. значению второго аргумента.

Пример кода с функциями ISNULL и NULLIF:

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select ISNULL (@vol_1/NULLIF (@vol_2, 0),0) ratio_vol;

Вывод результата успешного выполнения запроса:

2. Использование оператора CASE.

Посмотрим, как использовать для нашей задачи оператор CASE для возврата значений на основе определенных условий.

Оператор CASE проверит значение параметра @vol_2:

• если значение @vol_2 равно нулю, возвращается значение NULL;
• если это условие не выполняется, то производится арифметическая операция деления (@vol_1/@vol_2) и возвращается ее результат.

declare @vol_1 int;
declare @vol_2 int;
set @vol_1 = 10;
set @vol_2 = 0;
select CASE
	    when @vol_2 = 0
	    then NULL
	    else @vol_1/@vol_2
       end as ratio_vol;

Успешный результат запроса:

Применяя рассмотренные методы, позволяющие избежать ошибки деления на ноль в SQL Server, можно быть уверенным в том, что этот специфический, с точки зрения арифметики, код, являющийся частью большей задачи, не даст сбой в результате ее решения.

Функция NVL — заменить NULL — миграция Oracle на SQL Server

В Oracle функция NVL( exp1 , exp2 ) принимает 2 выражения (параметра) и возвращает первое выражение, если оно равно

, а не NULL, иначе NVL возвращает второе выражение.

В SQL Server вы можете использовать функцию ISNULL ( exp1 , exp2 ).

Пример Oracle:

 -- Вернуть «Н/Д», если имя равно NULL
  ВЫБЕРИТЕ NVL(имя, 'Н/Д') ИЗ стран; 

Пример SQL Server:

 -- Вернуть «Н/Д», если имя равно NULL
  ВЫБЕРИТЕ ISNULL(имя, 'Н/Д') ИЗ стран; 

Сводка по преобразованию Oracle NULL в SQL Server:

	Oracle	SQL Server
Синтаксис	NULL( exp1 , exp2 )	ISNULL( exp1 , exp2 )
Альтернативы	CASE	CASE	Совместимость с ANSI SQL
	ОБЪЕДИНЕНИЕ	ОБЪЕДИНЕНИЕ
	NVL2	IIF	Начиная с SQL Server 2012

Последнее обновление : Oracle 11g R2 и Microsoft SQL Server 2012

В Oracle функция NVL используется для замены NULL указанным значением и может быть преобразована в функцию ISNULL в SQL Server.

Обратите внимание: если вы хотите, чтобы ваше приложение поддерживало базы данных Oracle и SQL Server, вы можете использовать CASE, совместимый с ANSI SQL. выражение или функция COALESCE, которые поддерживаются как Oracle, так и SQL Server:

Оракул :

 -- Вернуть «Н/Д», если имя равно NULL
  ВЫБЕРИТЕ NVL(имя, 'Н/Д') ИЗ стран;

  -- Тот же результат с использованием выражения CASE
  SELECT CASE WHEN name IS NULL THEN 'N/A' ELSE name END FROM countrys;

  -- Использование COALESCE
  ВЫБЕРИТЕ ОБЪЕДИНЕНИЕ(имя, 'Н/Д') ИЗ стран; 

SQL Server :

 -- Вернуть «Н/Д», если имя равно NULL
  ВЫБЕРИТЕ ISNULL(имя, 'Н/Д') ИЗ стран;

  -- Тот же результат с использованием выражения CASE
  SELECT CASE WHEN name IS NULL THEN 'N/A' ELSE name END FROM countrys;

  -- Использование COALESCE
  ВЫБЕРИТЕ ОБЪЕДИНЕНИЕ(имя, 'Н/Д') ИЗ стран; 

Кроме того, вы можете использовать функцию IIF Oracle NVL2 и SQL Server (доступна начиная с SQL Server 2012):

Оракул :

 -- Вернуть «Н/Д», если имя равно NULL
  ВЫБЕРИТЕ NVL2 (имя, имя, «Н/Д») ИЗ стран; 

SQL Server 2012 или новее :

 -- Вернуть «Н/Д», если имя равно NULL
  ВЫБЕРИТЕ IIF(имя IS NULL, 'Н/Д', имя) ИЗ стран; 

• База знаний по миграции с Oracle на SQL Server

Справочник по языку SQL Oracle 11g R2

• Функция NVL

• Функция NVL2

• Выражение CASE

• ОБЪЕДИНЕНИЕ Функция

Microsoft SQL Server 2012 — Электронная документация

• ISNULL Функция

• ОБЪЕДИНЕНИЕ Функция

• Выражение CASE

• Функция IIF

SQLines предлагает услуги по конвертации баз данных и приложений с Oracle на Microsoft SQL Server.

Для получения более подробной информации, пожалуйста, свяжитесь с нами.

tsql — SQL-запрос, если значение равно null, возвращается 1

спросил 10 лет, 2 месяца назад

Изменено 5 лет, 9 месяцев назад

Просмотрено 315 тысяч раз

У меня есть запрос, который возвращает значение обменного курса, установленное в нашей системе. Не каждый заказ будет иметь обменный курс (currate.currentrate), поэтому он возвращает нулевые значения.

Могу ли я заставить его возвращать 1 вместо нуля?

Возможно, что-то вроде оператора if:

 если isnull(currate.currentrate), то 1 иначе currate.currentrate
 

Вот мой запрос ниже. Я очень ценю всю вашу помощь!

 ВЫБЕРИТЕ orderhed.ordernum, orderhed.orderdate, currrate. currencycode, currrate.currentrate
 ОТ ЗАКАЗАННОГО ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
                  currrate ON orderhed.company = currrate.company И orderhed.orderdate = currrate. Effectivedate
 

• tsql
• null
• ssms

3

Вы можете использовать инструкцию CASE .

 ВЫБОР
    СЛУЧАЙ, КОГДА currate.currentrate IS NULL THEN 1 ELSE currate.currentrate END
ОТ ...
 

14

Вы можете использовать COALESCE :

 SELECT orderhed.ordernum,
    заказхед.заказдата,
    currrate.currencycode,
    объединить (currrate.currentrate, 1) как текущую скорость
ОТ заказанного
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
    ПО orderhed.company = currrate.company
    И orderhed.orderdate = текущая ставка.эффективная дата
 

Или даже IsNull() :

 SELECT orderhed.ordernum,
    заказхед. заказдата,
    currrate.currencycode,
    IsNull(currrate.currentrate, 1) как текущая ставка
ОТ заказанного
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
    ПО orderhed.company = currrate.company
    И orderhed.orderdate = текущая ставка.эффективная дата
 

Вот статья, которая поможет выбрать между COALESCE и IsNull :

http://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

1

 ВЫБОР
    ISNULL (текущая скорость. текущая скорость, 1)
ОТ ...
 

менее подробен, чем победивший ответ, и делает то же самое. isnull(PartNum,0) AS PartNumber, PartID ОТ части

b) Если вы хотите 0, когда значение равно null, а в противном случае 1

 SELECT
  (СЛУЧАЙ
    КОГДА PartNum равен NULL, ТОГДА 0
    ИНАЧЕ 1
  КОНЕЦ) AS PartNumber,
  Идентификатор части
ОТ части
 

 ВЫБЕРИТЕ orderhed.ordernum, orderhed.orderdate, currrate.currencycode,
case(currrate.
No related posts.