descargar - sql server 2014

¿Cómo evitar el error "dividir por cero" en SQL?

Question

Tengo este mensaje de error:

Msg 8134, Nivel 16, Estado 1, Línea 1 Divide por cero error encontrado.

¿Cuál es la mejor manera de escribir código SQL para que nunca vuelva a ver este mensaje de error?

Podría hacer cualquiera de los siguientes:

• Agrega una cláusula where para que mi divisor nunca sea cero

O

• Podría agregar una declaración de caso, para que haya un tratamiento especial para el cero.

¿Es la mejor manera de usar una cláusula NULLIF ?

¿Hay una mejor manera, o cómo se puede hacer cumplir esto?

Answer 1

Esta parece ser la mejor solución para mi situación al tratar de resolver la división por cero, lo que sucede en mis datos.

Suponga que desea calcular las proporciones entre hombres y mujeres para varios clubes escolares, pero descubre que la siguiente consulta falla y emite un error de división por cero cuando trata de calcular la proporción para el Club del Señor de los Anillos, que no tiene mujeres :

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

Puedes usar la función NULLIF para evitar la división por cero. NULLIF compara dos expresiones y devuelve nulo si son iguales o la primera expresión de lo contrario.

Reescribe la consulta como:

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

Cualquier número dividido por NULL da NULL y no se genera ningún error.

Answer 2

1. Agregue una restricción CHECK que obligue al Divisor a no ser cero
2. Agregue un validador al formulario para que el usuario no pueda ingresar valores cero en este campo.

Answer 3

Al menos puede detener la consulta para que no se rompa con un error y devolver NULL si hay una división por cero:

SELECT a / NULLIF(b, 0) FROM t

Sin embargo, NUNCA convertiría esto a Cero con una coalesce como se muestra en esa otra respuesta que obtuvo muchos votos positivos. Esto es completamente incorrecto en un sentido matemático, e incluso es peligroso ya que su aplicación probablemente arrojará resultados erróneos y engañosos.

Answer 4

Aquí hay una situación en la que puedes dividir por cero. La regla de negocios es que para calcular los giros de inventario, usted toma el costo de los bienes vendidos por un período y lo anualiza. Una vez que tenga el número anualizado, divida por el inventario promedio para el período.

Estoy considerando calcular la cantidad de giros de inventario que se producen en un período de tres meses. He calculado que he vendido el costo de los bienes durante el período de tres meses de $ 1,000. La tasa anual de ventas es de $ 4,000 ($ 1,000 / 3) * 12. El inventario inicial es 0. El inventario final es 0. Mi inventario promedio ahora es 0. Tengo ventas de $ 4000 por año, y no hay inventario. Esto produce un número infinito de vueltas. Esto significa que todo mi inventario está siendo convertido y comprado por los clientes.

Esta es una regla comercial de cómo calcular los giros de inventario.

Answer 5

EDITAR: Recibo muchos comentarios negativos sobre esto recientemente ... así que pensé que solo agregaría una nota de que esta respuesta se escribió antes de que la pregunta se sometiera a su edición más reciente, donde se resaltó la opción null. .que parece muy aceptable. Parte de mi respuesta fue dirigida a inquietudes como la de Edwardo, en los comentarios, que parecían estar abogando por devolver un 0. Este es el caso contra el que estaba criticando.

RESPUESTA: Creo que hay un problema subyacente aquí, que es que la división por 0 no es legal. Es un indicio de que algo está mal en lo fundamental. Si está dividiendo por cero, está tratando de hacer algo que no tiene sentido matemáticamente, por lo que ninguna respuesta numérica que pueda obtener será válida. (El uso de null en este caso es razonable, ya que no es un valor que se usará en cálculos matemáticos posteriores).

Así que Edwardo pregunta en los comentarios "¿qué pasa si el usuario coloca un 0?", Y defiende que debería estar bien obtener un 0 a cambio. Si el usuario pone cero en la cantidad y desea que se devuelva 0 cuando lo haga, entonces debe ingresar el código en el nivel de reglas de negocios para capturar ese valor y devolver 0 ... no tiene algún caso especial donde la división por 0 = 0.

Esa es una diferencia sutil, pero es importante ... porque la próxima vez que alguien llame a su función y espere que haga lo correcto, y hace algo raro que no es matemáticamente correcto, pero que solo maneja el caso particular del borde que tiene. buenas posibilidades de morder a alguien más tarde. Realmente no estás dividiendo por 0 ... solo estás devolviendo una mala respuesta a una mala pregunta.

Imagina que estoy codificando algo, y lo arruino. Debería estar leyendo en un valor de escala de medición de radiación, pero en un caso extraño que no anticipé, leí en 0. Luego coloco mi valor en tu función ... ¡me devuelves un 0! ¡Hurra, no hay radiación! Excepto que realmente está ahí y es solo que estaba pasando en un mal valor ... pero no tengo idea. Quiero que la división lance el error porque es la bandera de que algo está mal.

Answer 6

En caso de que quiera devolver cero, en caso de que ocurra una idea de cero, puede usar:

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

Por cada divisor que sea cero, obtendrá un cero en el conjunto de resultados.

Answer 7

Filtre los datos utilizando una cláusula where para que no obtenga 0 valores.

Answer 8

Hace un tiempo escribí una función para manejarla para mis procedimientos almacenados :

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

Answer 9

No hay una configuración global mágica ''desactivar división por 0 excepciones''. La operación tiene que lanzar, ya que el significado matemático de x / 0 es diferente del significado NULL, por lo que no puede devolver NULL. Supongo que usted se está ocupando de lo obvio y sus consultas tienen condiciones que deberían eliminar los registros con el divisor 0 y nunca evaluar la división. El habitual ''gotcha'' es que la mayoría de los desarrolladores esperan que SQL se comporte como lenguajes de procedimiento y ofrezca un cortocircuito de operador lógico, pero NO lo hace. Le recomiendo que lea este artículo: http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html

Answer 10

Para actualizar SQLs:

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

Answer 11

Para evitar un error de "División por cero", lo hemos programado así:

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

Pero aquí hay una manera mucho más agradable de hacerlo:

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

Ahora el único problema es recordar el bit NullIf, si uso la tecla "/".

Answer 12

Puede manejar el error de manera apropiada cuando se propaga nuevamente al programa de llamada (o ignórelo si eso es lo que desea). En C #, cualquier error que ocurra en SQL lanzará una excepción que puedo detectar y luego manejar en mi código, como cualquier otro error.

Estoy de acuerdo con Beska en que no quiere ocultar el error. Es posible que no esté tratando con un reactor nuclear, pero ocultar errores en general es una mala práctica de programación. Esta es una de las razones por las que la mayoría de los lenguajes de programación modernos implementan el manejo estructurado de excepciones para desacoplar el valor de retorno real con un código de error / estado. Esto es especialmente cierto cuando estás haciendo matemáticas. El mayor problema es que no se puede distinguir entre un 0 calculado correctamente devuelto o un 0 como resultado de un error. En cambio, cualquier valor devuelto es el valor calculado y, si algo sale mal, se lanza una excepción. Por supuesto, esto variará dependiendo de cómo esté accediendo a la base de datos y qué idioma está utilizando, pero siempre debería poder recibir un mensaje de error con el que pueda lidiar.

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

Answer 13

Reemplazar "dividir por cero" por cero es controvertido, pero tampoco es la única opción. En algunos casos, reemplazar con 1 es (razonablemente) apropiado. A menudo me encuentro usando

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

cuando estoy viendo los cambios en los puntajes / conteos, y quiero el valor predeterminado a 1 si no tengo datos. Por ejemplo

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

Más a menudo que no, en realidad calculé esta relación en otro lugar (no solo porque puede generar algunos factores de ajuste muy altos para los denominadores bajos. En este caso, normalmente el control para OldSampleScore es mayor que un umbral, lo que excluye el cero). Pero a veces el ''hack'' es apropiado.

Answer 14

También puede hacer esto al comienzo de la consulta:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

Así que si tienes algo como 100/0 devolverá NULL. Solo he hecho esto para consultas simples, por lo que no sé cómo afectará a las más largas / complejas.

Answer 15

Use NULLIF(exp,0) pero de esta manera - NULLIF(ISNULL(exp,0),0)

NULLIF(exp,0) rompe si exp es null pero NULLIF(ISNULL(exp,0),0) no se romperá

Answer 16

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

Answer 17

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

Al capturar el cero con un nullif (), el nulo resultante con un nulo () puede sortear el error de división por cero.