Jak uniknąć błędu „dziel przez zero” w SQL?

Mam ten komunikat o błędzie:

Msg 8134, poziom 16, stan 1, wiersz 1 Napotkano błąd dzielenia przez zero.

Jaki jest najlepszy sposób na napisanie kodu SQL, aby nigdy więcej nie zobaczyłem tego komunikatu o błędzie?

Mógłbym wykonać jedną z następujących czynności:

• Dodaj klauzulę where, aby mój dzielnik nigdy nie wynosił zero

Lub

• Mógłbym dodać opis przypadku, aby było specjalne traktowanie dla zera.

Czy to najlepszy sposób na użycie NULLIFklauzuli?

Czy istnieje lepszy sposób lub jak można to egzekwować?

Aby uniknąć błędu „Dzielenie przez zero” zaprogramowaliśmy go w następujący sposób:

Select Case when divisor=0 then null
Else dividend / divisor
End ,,,

Ale tutaj jest o wiele ładniejszy sposób:

Select dividend / NULLIF(divisor, 0) ...

Teraz jedynym problemem jest zapamiętanie bitu NullIf, jeśli użyję klucza „/”.

Jeśli chcesz zwrócić zero, na wypadek gdyby nastąpiło zerowe odchylenie, możesz użyć:

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

Dla każdego dzielnika, który wynosi zero, otrzymasz zero w zestawie wyników.

Wydawało się, że jest to najlepsza poprawka w mojej sytuacji, gdy próbuję rozwiązać problem dzielenia przez zero, co dzieje się w moich danych.

Załóżmy, że chcesz obliczyć proporcje mężczyzn i kobiet dla różnych klubów szkolnych, ale odkrywasz, że poniższe zapytanie kończy się niepowodzeniem i powoduje błąd dzielenia przez zero, gdy próbuje obliczyć stosunek dla Lord of the Rings Club, w którym nie ma kobiet :

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

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

Przepisz zapytanie jako:

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

Dowolna liczba podzielona przez NULLdaje NULLi nie jest generowany błąd.

Możesz to również zrobić 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 wpłynie to na dłuższe / złożone.

Możesz przynajmniej zatrzymać awarię zapytania z błędem i zwrócić, NULLjeśli istnieje podział przez zero:

SELECT a / NULLIF(b, 0) FROM t 

Jednak NIGDY nie przekonwertowałbym tego na zero, coalescetak jak pokazano w innej odpowiedzi, która uzyskała wiele pozytywnych opinii. Jest to całkowicie błędne w sensie matematycznym, a nawet niebezpieczne, ponieważ aplikacja prawdopodobnie zwróci złe i wprowadzające w błąd wyniki.

EDYCJA: Dostaję ostatnio dużo głosów negatywnych na ten temat ... więc pomyślałem, że dodam notatkę, że ta odpowiedź została napisana przed poddaniem pytania jej ostatniej edycji, w której zwracanie wartości null było podświetlone jako opcja .. . które wydaje się bardzo do przyjęcia. Część mojej odpowiedzi dotyczyła takich obaw, jak Edwardo, w komentarzach, które zdawały się opowiadać za zwrotem 0. To był przypadek, któremu się szydziłem.

ODPOWIEDŹ: Wydaje mi się, że kryje się w tym problem podstawowy, polegający na tym, że podział przez 0 jest niezgodny z prawem. Wskazuje to, że coś jest z gruntu nie tak. Jeśli dzielisz przez zero, próbujesz zrobić coś, co nie ma sensu matematycznego, więc żadna odpowiedź liczbowa, którą możesz uzyskać, nie będzie poprawna. (Zastosowanie null w tym przypadku jest uzasadnione, ponieważ nie jest to wartość, która zostanie wykorzystana w późniejszych obliczeniach matematycznych).

Więc Edwardo pyta w komentarzach „a jeśli użytkownik wpisze 0?”, I opowiada się za tym, aby w zamian otrzymać 0. Jeśli użytkownik doda zero do kwoty, a chcesz, aby 0 zwróciło, gdy to zrobi, powinieneś wstawić 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 jest ważna ... ponieważ następnym razem ktoś wywoła twoją funkcję i oczekuje, że zrobi to dobrze, i zrobi coś funky, co nie jest poprawne matematycznie, ale obsługuje konkretny przypadek krawędzi duża szansa, że ​​później kogoś ugryziesz. Tak naprawdę nie dzielisz przez 0 ... po prostu odpowiadasz złą odpowiedzią na złe pytanie.

Wyobraź sobie, że coś koduję i spieprzę to. Powinienem odczytywać wartość skalowania pomiaru promieniowania, ale w dziwnym przypadku krawędzi, którego nie spodziewałem, czytam w 0. Następnie upuszczam moją wartość do twojej funkcji ... zwracasz mi 0! Hurra, brak promieniowania! Tyle, że naprawdę tam jest i po prostu przekazywałem złą wartość ... ale nie mam pojęcia. Chcę, aby podział zgłosił błąd, ponieważ to flaga wskazuje, że coś jest nie tak.

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

Łapiąc zero za pomocą nullif (), a następnie wynikowy null za pomocą isnull (), można obejść dzielenie przez błąd zerowy.

Zamiana „dziel przez zero” na zero jest kontrowersyjna - ale nie jest też jedyną opcją. W niektórych przypadkach zastąpienie 1 jest (rozsądnie) właściwe. Często używam

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

kiedy patrzę na zmiany wyników / zliczeń i chcę ustawić domyślnie na 1, jeśli nie mam danych. Na przykład

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

Częściej niż nie, faktycznie obliczałem ten stosunek gdzie indziej (zwłaszcza dlatego, że może on rzucić bardzo duże współczynniki dostosowania dla niskich mianowników. W tym przypadku normalnie kontroluję OldSampleScore jest większy niż próg; co następnie wyklucza zero Ale czasami „hack” jest odpowiedni.

Jakiś czas temu napisałem funkcję do obsługi jej dla moich procedur przechowywanych :

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 CHECK, które zmusza Divisordo bycia niezerową
2. Dodaj walidator do formularza, aby użytkownik nie mógł wprowadzić zerowych wartości w tym polu.

W przypadku aktualizacji SQL:

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

Nie ma magicznego globalnego ustawienia „wyłącz podział przez 0 wyjątków”. Operacja musi zostać odrzucona, ponieważ matematyczne znaczenie x / 0 różni się od NULL, więc nie może zwrócić NULL. Zakładam, że zajmujesz się oczywistością, a twoje zapytania mają warunki, które powinny wyeliminować zapisy z dzielnikiem 0 i nigdy nie oceniać podziału. Zwykłe „haczyka” jest niż większość deweloperów spodziewać SQL zachowywać się jak językach proceduralnych i oferują logicznego operatora zwarcie, ale robi NOT . Polecam przeczytanie tego artykułu: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

Oto sytuacja, w której możesz podzielić przez zero. Zasadą biznesową jest to, że aby obliczyć obroty zapasów, bierzesz koszt sprzedanych towarów za dany okres, ujmując go rocznie. Po uzyskaniu rocznej liczby dzielisz przez średnią wartość zapasów w danym okresie.

Patrzę na obliczenie liczby zwrotów zapasów, które występują w okresie trzech miesięcy. Obliczyłem, że mam Koszt Towaru sprzedany w okresie trzech miesięcy w wysokości 1000 USD. Roczna stopa sprzedaży wynosi 4000 USD (1000 USD / 3) * 12. Początkowy zapas wynosi 0. Końcowy zapas wynosi 0. Mój średni zapas wynosi teraz 0. Mam sprzedaż 4000 USD rocznie i nie mam zapasów. Daje to nieskończoną liczbę zwojów. Oznacza to, że wszystkie moje zapasy są konwertowane i kupowane przez klientów.

Jest to reguła biznesowa obliczania obrotów zapasów.

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

Odfiltruj dane za pomocą klauzuli where, aby nie uzyskać 0 wartości.

Czasami 0 może być niewłaściwe, ale czasami 1 również nie jest właściwe. Czasami skok od 0 do 100 000 000, opisany jako zmiana 1 lub 100 procentowa, może również wprowadzać w błąd. W tym scenariuszu odpowiednie może być 100 000 000 procent. To zależy od tego, jakie wnioski zamierzasz wyciągnąć na podstawie wartości procentowych lub współczynników.

Na przykład bardzo sprzedający się przedmiot przechodzący z 2-4 sprzedanych i bardzo sprzedający się przedmiot zmieniający się z 1 000 000 na 2 000 000 sprzedanych może oznaczać bardzo różne rzeczy dla analityka lub kierownictwa, ale oba przyniosą 100% lub 1 zmiana.

Wydzielenie wartości NULL może być łatwiejsze niż przeszukanie wiązki 0% lub 100% wierszy zmieszanych z wiarygodnymi danymi. Często 0 w mianowniku może wskazywać na błąd lub brakującą wartość, i możesz nie chcieć po prostu wpisać dowolnej wartości, aby twój zestaw danych wyglądał schludnie.

CASE
     WHEN [Denominator] = 0
     THEN NULL --or any value or sub case
     ELSE [Numerator]/[Denominator]
END as DivisionProblem

Tak to naprawiłem:

IIF (wartość A! = 0, suma / wartość A, 0)

Może być zapakowany w aktualizację:

USTAW Pct = IIF (wartość A! = 0, suma / wartość A, 0)

Lub w wybranych:

WYBIERZ IIF (wartość A! = 0, suma / wartość A, 0) AS Pct FROM Tablename;

Myśli?

Możesz odpowiednio obsłużyć błąd, gdy propaguje się z powrotem do programu wywołującego (lub zignoruj ​​go, jeśli tego chcesz). W języku C # wszelkie błędy występujące w SQL spowodują zgłoszenie wyjątku, który mogę przechwycić, a następnie obsłużyć mój kod, tak jak każdy inny błąd.

Zgadzam się z Beską, że nie chcesz ukryć błędu. Być może nie masz do czynienia z reaktorem jądrowym, ale ukrywanie błędów ogólnie jest złą praktyką programistyczną. Jest to jeden z powodów, dla których większość współczesnych języków programowania implementuje ustrukturyzowaną obsługę wyjątków w celu oddzielenia rzeczywistej wartości zwracanej za pomocą kodu błędu / statusu. Jest to szczególnie prawdziwe, gdy robisz matematykę. Największym problemem jest to, że nie można odróżnić zwracanego poprawnie 0 od 0 w wyniku błędu. Zamiast tego każda zwracana wartość jest wartością obliczoną, a jeśli coś pójdzie nie tak, zgłaszany jest wyjątek. To oczywiście będzie się różnić w zależności od tego, w jaki sposób uzyskujesz dostęp do bazy danych i jakiego języka używasz, ale zawsze powinieneś mieć komunikat o błędzie, z którym możesz sobie poradzić.

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)psuje się, jeśli exp jest, nullale NULLIF(ISNULL(exp,0),0)nie psuje się