wake-up-neo.net

Come evitare l'errore "dividi per zero" in SQL?

Ho questo messaggio di errore:

Messaggio 8134, livello 16, stato 1, divisione 1, divisione per errore zero rilevato.

Qual è il modo migliore per scrivere codice SQL in modo che non visualizzerò mai più questo messaggio di errore?

Potrei fare una delle seguenti cose:

  • Aggiungi una clausola where in modo che il mio divisore non sia mai zero

O

  • Potrei aggiungere una dichiarazione di un caso, in modo che ci sia un trattamento speciale per zero. 

È il modo migliore per utilizzare una clausola NULLIF

C'è un modo migliore, o come può essere applicato?

288

Per evitare un errore "Divisione per zero" lo abbiamo programmato in questo modo:

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

Ma qui c'è un modo molto più carino di farlo:

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

Ora l'unico problema è ricordare il bit NullIf, se uso il tasto "/".

533

Nel caso in cui si desideri restituire zero, nel caso si verifichi una divisione zero, è possibile utilizzare:

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

Per ogni divisore che è zero, si otterrà uno zero nel set di risultati.

150
Tobias Domhan

Questa sembrava essere la soluzione migliore per la mia situazione quando cercavo di affrontare la divisione per zero, cosa che accade nei miei dati.

Supponiamo di voler calcolare i rapporti uomo-donna per vari club scolastici, ma scopri che la seguente query fallisce e genera un errore di divisione per zero quando cerca di calcolare il rapporto per il Signore degli Anelli, che non ha donne : 

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

È possibile utilizzare la funzione NULLIF per evitare la divisione per zero. NULLIF confronta due espressioni e restituisce null se sono uguali o altrimenti la prima espressione. 

Riscrivi la query come: 

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

Qualsiasi numero diviso per NULL fornisce NULL e non viene generato alcun errore.

58
frank

Puoi anche farlo all'inizio della query:

SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF

Quindi se hai qualcosa come 100/0, restituirà NULL. L'ho fatto solo per domande semplici, quindi non so come influenzerà quelle più lunghe o complesse.

39
Taz

EDIT: Sto facendo un sacco di downvotes su questo recentemente ... quindi ho pensato di aggiungere una nota che questa risposta è stata scritta prima che la domanda subisse la sua modifica più recente, dove il ritorno di null è stato evidenziato come opzione ... che sembra molto accettabile. Alcune delle mie risposte sono state rivolte a preoccupazioni come quella di Edwardo, nei commenti, che sembravano invocare il ritorno di uno 0. Questo è il caso contro cui mi sono scontrato.

RISPOSTA: Penso che ci sia un problema di fondo qui, che è che la divisione per 0 non è legale. È un'indicazione che qualcosa è fondamentalmente sbagliato. Se stai dividendo per zero, stai cercando di fare qualcosa che non ha senso matematicamente, quindi nessuna risposta numerica che puoi ottenere sarà valida. (L'uso di null in questo caso è ragionevole, in quanto non è un valore che verrà utilizzato in calcoli matematici successivi).

Così Edwardo chiede nei commenti "cosa succede se l'utente inserisce uno 0?" E sostiene che dovrebbe essere ok ottenere uno 0 in cambio. Se l'utente mette zero nella quantità e vuoi che venga restituito 0 quando lo fa, allora dovresti inserire il codice a livello di regole aziendali per catturare quel valore e restituire 0 ... non avere qualche caso speciale dove la divisione per 0 = 0. 

Questa è una sottile differenza, ma è importante ... perché la prossima volta qualcuno chiama la tua funzione e si aspetta che faccia la cosa giusta, e fa qualcosa di funky che non è matematicamente corretto, ma semplicemente gestisce la particolare cassa Edge che ha una buone possibilità di mordere qualcuno più tardi. Non stai davvero dividendo per 0 ... stai solo restituendo una brutta risposta a una brutta domanda.

Immagina di scrivere qualcosa, e lo rovino. Dovrei leggere un valore di ridimensionamento della misurazione delle radiazioni, ma in uno strano caso di Edge che non avevo previsto, ho letto in 0. Quindi faccio cadere il mio valore nella funzione ... mi restituisci uno 0! Evviva, nessuna radiazione! Tranne che è davvero lì ed è solo che stavo passando un brutto valore ... ma non ne ho idea. Voglio che la divisione lanci l'errore perché è la bandiera che qualcosa non va.

31
Beska

Puoi almeno interrompere la query rompendo con un errore e restituire NULL se c'è una divisione per zero:

SELECT a / NULLIF(b, 0) FROM t 

Tuttavia, vorreiMAIconvertirlo in Zero con coalesce come viene mostrato in quell'altra risposta che ha ottenuto molti upvotes. Questo è completamente sbagliato in senso matematico ed è persino pericoloso in quanto la tua applicazione probabilmente restituirà risultati errati e fuorvianti. 

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

Catturando lo zero con un nullif (), quindi il risultato nullo con un isnull () è possibile aggirare l'errore di divisione per zero. 

21
Gurufaction

Sostituire "dividere per zero" con zero è controverso - ma non è nemmeno l'unica opzione. In alcuni casi la sostituzione con 1 è (ragionevolmente) appropriata. Mi capita spesso di usare

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

quando guardo ai turni in punteggi/conteggi e voglio impostare il valore predefinito su 1 se non ho dati. Per esempio

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

Il più delle volte, ho calcolato questo rapporto da qualche altra parte (non ultimo perché può generare alcuni fattori di regolazione molto grandi per i denominatori bassi. In questo caso, io normalmente controllo per OldSampleScore è maggiore di una soglia, che quindi preclude lo zero Ma a volte il "trucco" è appropriato.

7
N Mason

Ho scritto una funzione qualche tempo fa per gestirlo per le mie stored procedure :

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
5
Ron Savage
  1. Aggiungi un vincolo CHECK che costringe Divisor a non-zero
  2. Aggiungi un validatore al modulo in modo che l'utente non possa immettere valori zero in questo campo.
3
finnw

Per l'aggiornamento di SQL:

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

Ecco una situazione in cui puoi dividere per zero. La regola aziendale è che per calcolare i turni di inventario, si prende il costo delle merci vendute per un periodo, annualizzarlo. Dopo aver ottenuto il numero annualizzato, dividi per inventario medio per il periodo. 

Sto calcolando il numero di giri di inventario che si verificano in un periodo di tre mesi. Ho calcolato che ho il costo dei beni venduti durante il periodo di tre mesi di $ 1.000. Il tasso annuale di vendita è $ 4000 ($ 1,000/3) * 12. L'inventario iniziale è 0. L'inventario finale è 0. Il mio inventario medio è ora 0. Ho un fatturato di $ 4000 all'anno e nessun inventario. Questo produce un numero infinito di turni. Ciò significa che tutto il mio inventario viene convertito e acquistato dai clienti. 

Questa è una regola aziendale su come calcolare i turni di inventario. 

2
Jimmy

Non ci sono impostazioni globali magiche per "disattivare la divisione per 0 eccezioni off". L'operazione deve essere lanciata, poiché il significato matematico di x/0 è diverso dal significato NULL, quindi non può restituire NULL . Presumo che tu ti stia occupando dell'ovvio e le tue query abbiano condizioni che dovrebbero eliminare i record con il divisore 0 e mai valutare la divisione. Il solito "gotcha" è che la maggior parte degli sviluppatori si aspetta che SQL si comporti come linguaggi procedurali e offra un cortocircuito dell'operatore logico, ma lo fa NON. Vi consiglio di leggere questo articolo: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

2
Remus Rusanu

Filtra i dati utilizzando una clausola where in modo da non ottenere valori 0.

1
nunespascal
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
1
Gregory Hart

A volte, 0 potrebbe non essere appropriato, ma a volte 1 non è appropriato. A volte un salto da 0 a 100.000.000 descritto come 1 o 100 percento potrebbe anche essere fuorviante. 100.000.000 percento potrebbe essere appropriato in quello scenario. Dipende dal tipo di conclusioni che intendi attingere in base alle percentuali o ai rapporti. 

Ad esempio, un oggetto di vendita molto piccolo spostato da 2-4 venduti e un oggetto di vendita molto grande che passa da 1.000.000 a 2.000.000 venduti potrebbe significare cose molto diverse per un analista o per la gestione, ma entrambi si presenterebbero come 100% o 1 modificare. 

Potrebbe essere più semplice isolare i valori NULL piuttosto che setacciare un gruppo di righe 0% o 100% mescolate con dati legittimi. Spesso, uno 0 nel denominatore può indicare un errore o un valore mancante, e potresti non voler semplicemente inserire un valore arbitrario solo per rendere ordinato il set di dati.

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

È possibile gestire l'errore in modo appropriato quando si propaga di nuovo al programma chiamante (o ignorarlo se è ciò che si desidera). In C # eventuali errori che si verificano in SQL generano un'eccezione che posso catturare e quindi gestire nel mio codice, proprio come qualsiasi altro errore.

Sono d'accordo con Beska nel senso che non vuoi nascondere l'errore. Potresti non avere a che fare con un reattore nucleare ma nascondere gli errori in generale è una cattiva pratica di programmazione. Questo è uno dei motivi per cui la maggior parte dei moderni linguaggi di programmazione implementa la gestione strutturata delle eccezioni per disaccoppiare il valore di ritorno effettivo con un codice di errore/stato. Questo è particolarmente vero quando stai facendo matematica. Il problema più grande è che non è possibile distinguere tra uno 0 calcolato correttamente o uno 0 come risultato di un errore. Invece qualsiasi valore restituito è il valore calcolato e se qualcosa va storto viene generata un'eccezione. Questo ovviamente sarà diverso a seconda di come si accede al database e quale lingua si sta utilizzando, ma si dovrebbe sempre essere in grado di ottenere un messaggio di errore che è possibile gestire.

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.
}
0
Despertar

Usa NULLIF(exp,0) ma in questo modo - NULLIF(ISNULL(exp,0),0)

NULLIF(exp,0) interrompe se exp è null ma NULLIF(ISNULL(exp,0),0) non si interromperà

0
Johnny Kancharla