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 usare una clausola NULLIF ?

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

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 “/”.

Nel caso in cui si desideri restituire zero, nel caso si verifichi una deviazione zero, è ansible utilizzare:

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

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

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

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

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

È ansible 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 restituisce NULL e non viene generato alcun errore.

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 / complesse.

EDIT: Ultimamente sto facendo un sacco di downvotes su questo … quindi ho pensato di aggiungere una nota che questa risposta è stata scritta prima che la domanda subisse la sua modifica più recente, dove la restituzione di null è stata evidenziata 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).

Quindi Edwardo chiede nei commenti “cosa succede se l’utente inserisce uno 0?” E sostiene che dovrebbe essere ok per 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 che 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 il caso limite particolare che ha un buone possibilità di mordere qualcuno più tardi. Non stai davvero dividendo per 0 … stai solo restituendo una ctriggers 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 limite che non avevo previsto, ho letto in 0. Quindi ho inserito 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.

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

 SELECT a / NULLIF(b, 0) FROM t 

Tuttavia, non lo convertirò mai in Zero con il coalesce come mostrato in quell’altra risposta che ha ottenuto molti risvolti positivi. Questo è completamente sbagliato in senso matematico, ed è persino pericoloso in quanto la tua applicazione probabilmente restituirà risultati errati e fuorvianti.

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

Sostituire “divide 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) 

Nella maggior parte dei casi, 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, normalmente controllo per OldSampleScore è maggiore di una soglia, che quindi preclude lo zero Ma a volte il “trucco” è appropriato.

Ho scritto una funzione un po ‘indietro 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 
  1. Aggiungi un vincolo CHECK che impone al Divisor di essere diverso da zero
  2. Aggiungi un validatore al modulo in modo che l’utente non possa immettere valori zero in questo campo.

Per gli SQL di aggiornamento:

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

Non ci sono impostazioni globali magiche ‘spegni la divisione per 0 eccezioni off’. L’operazione deve essere eseguita, poiché il significato matematico di x / 0 è diverso dal significato di NULL, quindi non può restituire NULL. Presumo che tu ti stia occupando dell’ovvio e le tue domande hanno condizioni che dovrebbero eliminare i record con il divisore 0 e non valutare mai 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 NON lo fa. Vi consiglio di leggere questo articolo: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

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.

Filtra i dati utilizzando una clausola where in modo da non ottenere valori 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 

È ansible 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 hide l’errore. Potresti non avere a che fare con un reattore nucleare ma hide gli errori in generale è una ctriggers 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 è ansible 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 è ansible 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. } 

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à