1
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 nullif, but without success. 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
0
Testing if it is null will not help you much in the way you are doing, because if the value of working hours remains zero, you will still get division error by zero.
Most likely its function horas_uteis_trabalhadas must be returning an integer or value less than 144000 and in sql server when dividing any value by 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 work is greater than zero, I set an example using the case and make a cast for float:
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
My friend, the same error occurred.
I made an edit on my reply, using iif, if possible, run using a select * from v1 and post the result in the question.
I think just changing the NULL para 0dentro do T-SqlNULLIFjá resolveria o problema dele. Mesmo quetimes workedretorne0não dá erro.0/Somemnumero` is 0, on the contrary the question error will occur.
if useful hours return 0, it will give zero when divided by nullif, and when trying to split Count(f.Solid) by the hours/nullif result will give error again.
@Pablotondolodevargas yes, you’re right. It may be that the query is like this: count(f.SolId) / (0/144000)
anyway here nullif(144000,null)will always return 144000. See documentation : https://docs.microsoft.com/pt-br/sql/t-sql/language-elements/nullif-transact-sql
Yes, this nullif is being used in a useless way in his case, I’ll even edit my reply
ball show @Pablotondolodevargas, all right.
Browser other questions tagged jquery sql database sql-server
You are not signed in. Login or sign up in order to post.
are an initial date and end date field
– Renan Bessa
You intend to rent to how many decimal places?
– Marconi
7 equal as it is in query. decimal (7.1)
– Renan Bessa
thanks for the help @Marconi
– Renan Bessa