Breakdown error by zero - Query SQL
-
I have the following query below, however, when I run it, the following message occurs:
erro de divisão por zero
. I tried to use the functionnullif
but unsuccessful. How to solve?SELECT count(f.SolID) / cast(dbo.horas_uteis_trabalhadas('01-11-2017','30-11-2017') / nullif(144000,NULL) AS decimal(7,1)) [DPI] FROM tarefa f
-
Testing if it’s zero won’t help you much the way you’re doing, as if the value of the working hours continues zero, you’ll still have the split error by zero.
Most likely your function
horas_uteis_trabalhadas
should be returning an integer or a value smaller than 144000 and on the sql server when splitting any value for an integer, it will always return an integer, which in your case will be zero.What you can do is test if the working hours are greater than zero, I set an example using the case and make a
cast
Forfloat
:with v1 as ( select cast(dbo.horas_uteis_trabalhadas('01-11-2017','30-11-2017') as float) as horas, cast(nullif(144000,null) as float) as valor, count(f.SolID) as SolId from tarefa f)
select iif(horas > 0, SolId / cast(horas/valor as float), 1) as [DPI]
from v1