Division error by zero - Query SQL

Asked

Viewed 751 times

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
jquery sql database sql-server

  • are an initial date and end date field

  • You intend to rent to how many decimal places?

  • 7 equal as it is in query. decimal (7.1)

  • 1

    thanks for the help @Marconi

1 answer

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.

Show 3 more comments

Browser other questions tagged jquery sql database sql-server

You are not signed in. Login or sign up in order to post.