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 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_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 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2