#sql #sql server #анализ данных #базы данных
В процессе работы 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, можно быть уверенным в том, что этот специфический, с точки зрения арифметики, код, являющийся частью большей задачи, не даст сбой в результате ее решения.
Читайте также: