CODEWITHSUNDEEP
sqlsql-server-2008nulldivide-by-zero
Matt
Matt

Reputation: 273

SQL NULLIF Function not working around divisor

I am trying to use a NULLIF function to return a NULL value where the divisor of my query results in zero and therefore returns a divide by zero error. However, I'm having trouble wrapping the function around my statement. The statement contains a CAST, CASE and SUM function. I've wrapped the function around the divisor in the example below, but this doesn't work and I've tried other combinations.

cast(
  round(
      cast(
          sum(
              case 
                  when @StuYear=11 AND [Levels of Progress] < 3 then 
                      1 
              when @StuYear=10 AND [Levels of Progress] < 2 then 
                      1
              when @StuYear=9 AND [Levels of Progress] < 1 then 
                      1
          else 
                      0 
              end) as decimal)
/
NULLIF(
cast(
    sum(
        case
            when [Levels of Progress] is NULL then 
                0 
            else 
                1 
        end) as decimal) * 100,1) as numeric(4,1))
,0)

Upvotes: 1

Views: 1409

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44326

The syntax you posted is invalid and hard to read, also it seems your logic is wrong.

Try this instead:

declare @stuyear int = 11
select
  cast(
    sum(
      case when @StuYear=11 and [Levels of Progress] < 3 or 
                @StuYear=10 and [Levels of Progress] < 2 or
                @StuYear=9  and [Levels of Progress] < 1 then 1
         else 0 end
     )*100.0 /
    NULLIF(count([Levels of Progress]),0)
  as Numeric(5,2))
from (values (cast(null as int))) x([Levels of Progress])

Replace the from part with your own table. This is a valid syntax returns null when the count is null.

Upvotes: 1

gvee
gvee

Reputation: 17161

Cast(
  Sum(
    CASE WHEN (@StuYear = 11 AND [Levels of Progress] < 3)
           OR (@StuYear = 10 AND [Levels of Progress] < 2)
           OR (@StuYear =  9 AND [Levels of Progress] < 1)
      THEN 1
      ELSE 0
    END
  )
, As decimal)

/

NullIf(
  Cast(
    Sum(
      CASE WHEN [Levels of Progress] IS NULL
        THEN 0
        ELSE 1
      END
    )
  , As decimal)
, 0)

Alternatively we can force the Sum() to be NULL by not summing "zeroes". The later part of our query then becomes:

Cast(
  Sum(
    CASE WHEN [Levels of Progress] IS NOT NULL
      THEN 1
    END
  )
, As decimal)

Incidentally; if you have this problem in the future then it is best to split out your values in to separate columns for debugging purposes.

Upvotes: 1

SteveB
SteveB

Reputation: 1514

Why not use a TRY ... CATCH and control the flow of your code?

begin try
    print 55/0; -- your code would go here
end try
begin catch
    print Error_Message(); -- do what you need to do here on a divide by zero
end catch

Upvotes: -1

Related Questions