Breakdown error by zero - Query SQL
Bogopo last edited by
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 function
nullifbut 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_trabalhadasshould 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
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]