Syntax to resolve ORACLE error is ORA-01476: divisor is equal to zero

I’m using the NVL function to try and correct

ERROR: Error fetching from cursor. ORACLE error is ORA-01476: divisor is equal to zero.

I’m having trouble figuring out what the correct syntax is.

(nvl(sum(case when t.LABEL in (12,14,24,25,26,33) and t.sales_credit=1 then t.AMOUNT else 0 end),0) / nvl(sum(case when t.sales_credit=1 then t.AMOUNT else 0 end),0))*100 as DOLSHAR_COR_M

Answer

The normal method is nullif() in the dividend. So, instead of:

nvl(sum(case when t.sales_credit=1 then t.AMOUNT else 0 end), 0))

You would use:

nullif(sum(case when t.sales_credit=1 then t.AMOUNT else 0 end), 0))

Assuming that t.Amount is never negative or 0, it is sufficient to just drop the else 0:

sum(case when t.sales_credit = 1 then t.AMOUNT end)