Tengo este mensaje de error:
Msg 8134, Level 16, State 1, Line 1 Error de división por cero 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 lo siguiente:
O
¿Es la mejor manera usar una cláusula NULLIF?
¿Hay una mejor manera, o cómo se puede hacer cumplir esto?
Para evitar un error de "División por cero" lo hemos programado de esta manera:
Seleccionar caso cuando divisor=0 entonces null
De lo contrario dividendo / divisor
Fin ...Pero aquí hay una forma mucho mejor de hacerlo:
Seleccionar dividendo / NULLIF(divisor, 0) ...Ahora el único problema es recordar el NullIf, si uso la tecla "/".
@Anderson Eso no es cierto en absoluto. ¿Estás seguro de que no usaste accidentalmente IsNull en lugar de NullIf? ¡Pruébalo tú mismo! SELECT Value, 1/NullIf(Value,0)FROM(VALUES(0),(5.0),(NULL))x(Value); A menos que por "breaks" te refieras a que devuelve un NULL? Puedes convertir eso a lo que quieras con IsNull o Coalesce.
@ErikE, es cierto... intenta ejecutar ... select 1/nullif(null,0) ... obtendrás "The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known." Maneja esto usando "coalesce(FieldName,0)" ... e.g. select 1/nullif(coalesce(null,0),0)
Algunas pruebas revelan que COALESCE es ligeramente más lento que ISNULL. Sin embargo, COALESCE está en los estándares, por lo que es más portátil.
Si alguien más no entiende de inmediato por qué esto funciona, NULLIF(d,0) devolverá NULL si d es 0. En SQL, dividir por NULL devuelve NULL. El Coalesce reemplaza el NULL resultado por 0.
También ten en cuenta que retorna 0 cuando un dividendo o divisor son NULL, mientras que por estándar retornaría NULL.
Supongamos que quieres calcular las razones entre hombres y mujeres para varios clubes escolares, pero descubres que la siguiente consulta falla y emite un error de división por cero cuando intenta calcular la razón para el Club de El 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 en caso 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.
Sí, de hecho, eso es MUCHO MEJOR que esa otra respuesta que tiene tantos votos positivos. En tu solución, al menos tienes un NULL, lo que indica que no puedes proporcionar un resultado correcto. Pero si conviertes el resultado de NULL a cero, entonces simplemente obtienes resultados incorrectos y engañosos.
Por cierto, si deseas calcular una proporción de hombres/mujeres, entonces te sugiero que la compares mejor con el total, así: select hombres/(hombres+mujeres), mujeres/(hombres+mujeres). Esto te dará la distribución porcentual de hombres y mujeres en un club, por ejemplo, 31% hombres, 69% mujeres.
Funciona para mí. En mi caso, tengo que usar la operación de división en la cláusula WHERE. Estoy seguro de que no hay ningún divisor cero, porque cuando comento WHERE, no hay valores cero en los resultados. Pero de alguna manera el optimizador de consultas hace una división por cero al filtrar. SET ARITHABORT OFF SET y ANSI_WARNINGS OFF hacen que funcione - después de 2 días de luchar con la división por cero en la cláusula WHERE. ¡Gracias!
¡Esto "se siente" tan sucio pero me encanta! Necesitaba en una consulta que hace agregaciones y usar la declaración CASE no era una opción porque entonces tendría que agregar esa columna al GROUP BY lo que cambió totalmente los resultados. Hacer que la consulta inicial sea un subselect y luego realizar un GROUP BY en la consulta externa también cambia los resultados porque hay división involucrada.
OK, todavía me gusta esta "solución" pero, al igual que muchos de ustedes probablemente sintieron, sentí que tenía que haber una forma "más limpia". ¿Y si olvidé habilitar de nuevo las advertencias? ¿O si alguien clonó mi código (¡nunca sucede, verdad?) y no pensó en las advertencias? De todos modos, vi otras respuestas sobre NULLIF(). Sabía sobre NULLIF() pero no me di cuenta de que dividir por NULL devuelve NULL (pensé que sería un error). Así que... fui con lo siguiente: ISNULL( (SUM(foo) / NULLIF(SUM(bar),0) ), 0) COMO Avg
EDITAR: Recientemente he recibido muchos votos negativos en esto... así que pensé en agregar una nota de que esta respuesta fue escrita antes de que la pregunta sufriera su edición más reciente, donde devolver nulo fue resaltado como una opción... lo cual parece muy aceptable. Algunos de mis comentarios fueron dirigidos a preocupaciones como las de Edwardo, en los comentarios, quien parecía estar abogando por devolver un 0. Ese es el caso contra el que estaba poniendo objeciones.
RESPUESTA: Creo que hay un problema subyacente aquí, que es que la división por 0 no es legal. Es una indicación de que algo está fundamentalmente mal. Si estás dividiendo por cero, estás tratando de hacer algo que no tiene sentido matemáticamente, por lo que ninguna respuesta numérica que obtengas será válida. (El uso de nulo en este caso es razonable, ya que no es un valor que se utilizará en cálculos matemáticos posteriores).
Entonces, Edwardo pregunta en los comentarios "¿qué pasa si el usuario ingresa un 0?", y él defiende que debería estar bien obtener un 0 a cambio. Si el usuario ingresa cero en la cantidad, y quieres que se devuelva 0 cuando hagan eso, entonces deberías añadir código a nivel de reglas de negocio para detectar ese valor y devolver 0... no tener un caso especial donde la división por 0 = 0.
Esa es una diferencia sutil, pero importante... porque la próxima vez que alguien llame a tu función y espere que haga lo correcto, si hace algo raro que no es matemáticamente correcto, pero simplemente maneja el caso particular que tiene, hay una buena probabilidad de que le cause problemas más adelante. Realmente no estás dividiendo por 0... simplemente estás devolviendo una respuesta incorrecta a una pregunta incorrecta.
Imagina que estoy codificando algo, y lo estropeo. Debería estar leyendo un valor de escala de medición de radiación, pero en un caso extraño que no anticipé, leo un 0. Luego dejo caer mi valor en tu función... ¡me devuelves un 0! ¡Hurra, no hay radiación! Excepto que realmente está ahí y es solo que pasé un valor incorrecto... pero no tengo idea. Quiero que la división lance el error porque es la señal de que algo está mal.
Estoy en desacuerdo. Sus reglas de negocio nunca deben terminar haciendo matemáticas ilegales. Si terminas haciendo algo así, lo más probable es que tu modelo de datos esté equivocado. Si te encuentras con una división por 0, deberías reflexionar si los datos deberían haber sido NULL en lugar de 0.
No puedo creer que fui votado negativamente por alguien que pregunta si alguna vez "hice programación real" porque estoy diciendo que lo hagan bien, en lugar de ser perezoso. suspiro
Lo siento, no fue mi intención ofenderte. Pero la pregunta es perfectamente válida en muchas aplicaciones LOB comunes, y responder con un "la división por 0 no es legal" no agrega valor en mi opinión.
Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.
7 votos
Quizás es necesario realizar una validación de datos.