Jak uniknąć błędu "podziel przez zero" w SQL?

W przypadku, gdy chcesz zwrócić zero, w przypadku, gdy zdarzy się Zero devision, możesz użyć:

SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable

Dla każdego dzielnika, który jest zerem, otrzymasz zero w zbiorze wynikowym.

Wydawało się to najlepszym rozwiązaniem dla mojej sytuacji, gdy próbuję rozwiązać dzielenie przez zero, co zdarza się w moich danych.

Załóżmy, że chcesz obliczyć stosunek mężczyzna-kobieta dla różnych klubów szkolnych, ale odkrywasz, że poniższe zapytanie nie powiedzie się i wyświetla błąd dzielenia przez zero, gdy próbuje obliczyć stosunek dla klubu Władcy Pierścieni, który nie ma kobiet: {]}

SELECT club_id, males, females, males/females AS ratio
  FROM school_clubs;

Możesz użyć funkcji NULLIF, aby uniknąć dzielenia przez zero. NULLIF porównuje dwa wyrażenia i zwraca null, jeśli są równe lub pierwsze wyrażenie w przeciwnym razie.

Przepisz zapytanie jako:

SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio
  FROM school_clubs;

Każda liczba podzielona przez NULL daje NULL i nie generuje się żadnego błędu.

Możesz to zrobić również na początku zapytania:

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

Więc jeśli masz coś takiego 100/0 zwróci NULL. Zrobiłem to tylko dla prostych zapytań, więc nie wiem, jak to wpłynie na dłuższe / złożone.

Edytuj: Dostaję wiele negatywnych opinii na ten temat recently...so pomyślałem, że dodam tylko notkę, że ta odpowiedź została napisana zanim pytanie zostało poddane ostatniej edycji, gdzie zwracanie null zostało wyróżnione jako opcja...co wydaje się bardzo akceptowalne. Część mojej odpowiedzi była skierowana do obaw, takich jak Edwardo, w komentarzach, który wydawał się opowiadać za powrotem 0. To jest sprawa, przeciwko której walczyłem.

Odpowiedź: Myślę, że jest tu podstawowy problem, który jest ten podział przez 0 nie jest legalny. To oznaka, że coś jest nie tak. Jeśli dzielisz przez zero, próbujesz zrobić coś, co nie ma sensu matematycznie, więc żadna liczbowa odpowiedź, którą możesz uzyskać, nie będzie prawidłowa. (Użycie null w tym przypadku jest rozsądne, ponieważ nie jest to wartość, która będzie używana w późniejszych obliczeniach matematycznych).

Więc Edwardo pyta w komentarzach "co jeśli użytkownik wstawi 0?", i opowiada się za tym, aby w zamian otrzymać 0. Jeśli użytkownik umieszcza zero w kwocie, a Ty chcesz, aby 0 wrócił, gdy to zrobią, powinieneś umieścić kod na poziomie reguł biznesowych, aby złapać tę wartość i zwrócić 0...nie ma specjalnego przypadku, w którym dzielenie przez 0 = 0.

To subtelna różnica, ale ważna...ponieważ następnym razem, gdy ktoś wywoła twoją funkcję i spodziewa się, że zrobi to, co należy, i robi coś dziwnego, co nie jest matematycznie poprawne, ale po prostu obsługuje konkretny przypadek krawędzi, ma dużą szansę pogryzienia kogoś później. Nie dzielisz się przez 0...po prostu odpowiadasz na złe pytanie.

Wyobraź sobie, że coś koduję i to spieprzę. Powinienem odczytać wartość pomiaru promieniowania, ale w dziwnym przypadku krawędzi nie przewidziałem, czytałem w 0. Następnie obniżam swoją wartość do twojej funkcji...zwracasz mi 0! Hurra, żadnego promieniowania! Tylko, że to naprawdę tam jest i po prostu przechodziłem w złej wartości...ale nie mam pojęcia. I want division wrzucić błąd, bo to flaga, że coś jest nie tak.

Możesz przynajmniej zatrzymać zapytanie przed zerwaniem z błędem i zwrócić NULL jeśli istnieje podział przez zero:

SELECT a / NULLIF(b, 0) FROM t 

Jednakże, nigdy nie zamieniłbym tego na Zero za pomocą coalesce, Jak pokazano w innej odpowiedzi, która ma wiele pozytywnych opinii. Jest to całkowicie błędne w sensie matematycznym, a nawet niebezpieczne, ponieważ Twoja aplikacja prawdopodobnie zwróci błędne i wprowadzające w błąd wyniki.

SELECT Dividend / ISNULL(NULLIF(Divisor,0), 1) AS Result from table

Łapiąc zero za pomocą nullif (), a następnie wynikowe null za pomocą isnull() możesz obejść dzielenie przez zero błędu.

Zastąpienie" divide by zero " przez zero jest kontrowersyjne - ale nie jest to jedyna opcja. W niektórych przypadkach zastąpienie przez 1 jest (rozsądnie) właściwe. Często używam

 ISNULL(Numerator/NULLIF(Divisor,0),1)

Kiedy patrzę na zmiany w wynikach/liczbach i chcę domyślnie ustawić 1, jeśli nie mam danych. Na przykład

NewScore = OldScore *  ISNULL(NewSampleScore/NULLIF(OldSampleScore,0),1) 

Częściej niż nie, faktycznie obliczyłem ten stosunek gdzie indziej (nie tylko dlatego, że może rzucić kilka bardzo dużych współczynników korygujących dla niskich mianowników. W tym przypadku Normalnie bym kontrolował dla OldSampleScore jest większy niż próg, co wtedy wyklucza zero. Ale czasami "hack" jest odpowiedni.

Napisałem jakiś czas temu funkcję, która obsługuje moje procedury przechowywane :

print 'Creating safeDivide Stored Proc ...'
go

if exists (select * from dbo.sysobjects where  name = 'safeDivide') drop function safeDivide;
go

create function dbo.safeDivide( @Numerator decimal(38,19), @divisor decimal(39,19))
   returns decimal(38,19)
begin
 -- **************************************************************************
 --  Procedure: safeDivide()
 --     Author: Ron Savage, Central, ex: 1282
 --       Date: 06/22/2004
 --
 --  Description:
 --  This function divides the first argument by the second argument after
 --  checking for NULL or 0 divisors to avoid "divide by zero" errors.
 -- Change History:
 --
 -- Date        Init. Description
 -- 05/14/2009  RS    Updated to handle really freaking big numbers, just in
 --                   case. :-)
 -- 05/14/2009  RS    Updated to handle negative divisors.
 -- **************************************************************************
   declare @p_product    decimal(38,19);

   select @p_product = null;

   if ( @divisor is not null and @divisor <> 0 and @Numerator is not null )
      select @p_product = @Numerator / @divisor;

   return(@p_product)
end
go

1. Dodaj ograniczenie kontrolne, które wymusza Divisor Na niezerowe
2. Dodaj walidator do formularza, aby użytkownik nie mógł wprowadzić wartości zerowych do tego pola.

Dla aktualizacji SQL:

update Table1 set Col1 = Col2 / ISNULL(NULLIF(Col3,0),1)

Nie ma magicznego globalnego Ustawienia "Wyłącz dzielenie przez 0 WYJĄTKÓW". Operacja musi rzucać, ponieważ matematyczne znaczenie x / 0 różni się od znaczenia NULL, więc nie może zwrócić NULL. Zakładam, że dbasz o oczywistość i Twoje zapytania mają warunki, które powinny wyeliminować rekordy z dzielnikiem 0 i nigdy nie oceniać podziału. Zwykle "gotcha" jest niż większość programistów oczekuje SQL zachowywać się jak języki proceduralne i oferują operatory logiczne zwarcie, ale to NIE. Polecam przeczytać ten artykuł: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

Oto sytuacja, w której można podzielić przez zero. Zasada biznesowa jest taka, że aby obliczyć obroty zapasów, bierzesz koszt towarów sprzedanych na okres, annualizujesz go. Po uzyskaniu rocznej liczby dzielisz się przez średni stan zapasów na dany okres.

Patrzę na obliczenie liczby obrotów inwentarza, które występują w okresie trzech miesięcy. Obliczyłem, że koszt towarów sprzedanych w okresie trzech miesięcy wynosi $1,000. Roczna stawka sprzedaży wynosi $4,000 ($1,000/3)*12. Początek inwentarza to 0. Końcowy inwentarz to 0. Mój średni zapas wynosi teraz 0. Mam sprzedaż $4000 rocznie i brak zapasów. Daje to nieskończoną liczbę obrotów. Oznacza to, że wszystkie moje zapasy są konwertowane i kupowane przez klientów.

Jest to zasada biznesowa, jak obliczyć obroty zapasów.

Filtruj dane za pomocą klauzuli where, aby nie otrzymać wartości 0.

CREATE FUNCTION dbo.Divide(@Numerator Real, @Denominator Real)
RETURNS Real AS
/*
Purpose:      Handle Division by Zero errors
Description:  User Defined Scalar Function
Parameter(s): @Numerator and @Denominator

Test it:

SELECT 'Numerator = 0' Division, dbo.fn_CORP_Divide(0,16) Results
UNION ALL
SELECT 'Denominator = 0', dbo.fn_CORP_Divide(16,0)
UNION ALL
SELECT 'Numerator is NULL', dbo.fn_CORP_Divide(NULL,16)
UNION ALL
SELECT 'Denominator is NULL', dbo.fn_CORP_Divide(16,NULL)
UNION ALL
SELECT 'Numerator & Denominator is NULL', dbo.fn_CORP_Divide(NULL,NULL)
UNION ALL
SELECT 'Numerator & Denominator = 0', dbo.fn_CORP_Divide(0,0)
UNION ALL
SELECT '16 / 4', dbo.fn_CORP_Divide(16,4)
UNION ALL
SELECT '16 / 3', dbo.fn_CORP_Divide(16,3)

*/
BEGIN
    RETURN
        CASE WHEN @Denominator = 0 THEN
            NULL
        ELSE
            @Numerator / @Denominator
        END
END
GO

Możesz odpowiednio obsłużyć błąd, gdy propaguje się on z powrotem do programu wywołującego (lub zignorować go, jeśli tego chcesz). W C# wszelkie błędy, które występują w SQL rzuci wyjątek, który mogę złapać, a następnie obsłużyć w moim kodzie, tak jak każdy inny błąd.

try
{
    Database.ComputePercentage();
}
catch (SqlException e)
{
    // now you can handle the exception or at least log that the exception was thrown if you choose not to handle it
    // Exception Details: System.Data.SqlClient.SqlException: Divide by zero error encountered.
}

Użyj NULLIF(exp,0) ale w ten sposób - NULLIF(ISNULL(exp,0),0)

NULLIF(exp,0) breaks if exp is null but NULLIF(ISNULL(exp,0),0) will not breaks