Divide Zero error when calculating Sales % in SQL

I keep getting the following error message:

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

I’ve seen some posts about using IFNULL which I have tried. For example:

,Case when a.DiscountReasonCode = 'RL' then IFNULL(((a.ORIGINALRETAIL-a.RetOne) / (a.ORIGINALRETAIL) * 100),0) end as [PctSB]

But this returns the following error:

‘IFNULL’ is not a recognized built-in function name.

What I’m doing is trying to calculate the proper sales percent and then find and compare it to what is already in the table to find errors or missing Sales %’s.

I’m not sure where I’m going wrong but any help would be greatly appreciated.

SELECT 
a.packnum
,a.description
,a.CatID
,a.PctSavings
,Case when a.DiscountReasonCode = 'RL' then (a.ORIGINALRETAIL-a.RetOne) / (a.ORIGINALRETAIL) * 100 
end as [PctSB]

FROM PIC704Current a Join CatalogInfo b ON (a.CatID = b.Catalog) and (a.Year = b.MailYear)

WHERE
b.MediaId in('CAT Catalog','SCAT Sale Catalog','SSTF Sale Statement Stuff','STUF Statement 
Stuffer','PKG Package Insert','SPKG Sale Pkg Insert')
and a.DiscountReasonCode = 'RL'
and a.year >='2020'
and (Case when a.PctSavings <> (a.ORIGINALRETAIL-a.RetOne)/a.ORIGINALRETAIL*100 then 'False' else 
'True' END) = 'False'

Thanks

Answer

Wrapping a divide in a null test will not get rid of the divide by 0 error. Instead, you need to check whether the value being divided by is 0 and return a different value instead. You can do this using IIF:

IIF(a.ORIGINALRETAIL = 0, 0, 100.0 * (a.ORIGINALRETAIL-a.RetOne) / a.ORIGINALRETAIL)