sqlsql-servernullif

Need to get null back rather than divide by zero

I had to change my old query to a new query to get the correct results. My old query used to return a null in the denominator, so I didn't have to worry about divide by zero, the new query returns divide my zero for 'M' (R_NTWK_CHNNL), but for 'S' or R, i don't have a zero denominator. What can I do to get a null denominator in query 2? The table definition didn't change and wondering why now I don't get a null denominator when 'M' has nothing in it. Please also, how would I add nullif to the query 2? Thank you very much.

Old query

select SUM(CASE WHEN (GNRC_CD in ('O') and  R_NTWK_CHNNL = 'M')  then (CLNT_NET_DUE_AMT + NBNR_CLNT_NET_DUE_AMT) else 0 end ) / sUM(CLMS) from #Clms_SMRY where  SRVC_DT  between @CurrentBeginDate and @CurrentEndDate)

New query

select SUM(CASE WHEN (GNRC_CD in ('O') ) and R_NTWK_CHNNL =  'M'  then (CLNT_NET_DUE_AMT + NBNR_CLNT_NET_DUE_AMT) else 0 end ) / cast(SUM(CASE WHEN GNRC_CD in ('O') and R_NTWK_CHNNL =  'M' then (CLMS)  end ) as float) from #Clms_Smry where  SRVC_DT  between @CurrentBeginDate and @CurrentEndDate),
Solution

  • Use NULLIF():

    select (SUM(CASE WHEN (GNRC_CD in ('O') and R_NTWK_CHNNL = 'M') then (CLNT_NET_DUE_AMT + NBNR_CLNT_NET_DUE_AMT) else 0 end ) / 
        NULLIF(SUM(CLMS), 0)
       ) 
from #Clms_SMRY
where SRVC_DT between @CurrentBeginDate and @CurrentEndDate)