Oracle Error when dividing by Zero

Question

I have a query where I need to get a certain number divided by 2 fields in 2 columns.

shelve.total_qty/(GREATEST(NVL(y.FORECAST_QUANTITY, 0), NVL(z.sales, 0))/7) as Shelve_DOC

I get the notorious error.

ORA-01476: divisor is equal to zero 01476. 00000 - "divisor is equal to zero" *Cause:
*Action:

I have read around I need a CASE/IF but I'm not sure how to..

Any help would be appreciated.

There is a neat way using nullif http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql — edgar, Nov 27 '13 at 19:45

score 1 · Accepted Answer · answered Nov 27 '13 at 00:59

1

SELECT 
     CASE WHEN (GREATEST(NVL(y.FORECAST_QUANTITY, 0), NVL(z.sales, 0))/7) = 0 THEN null
          ELSE shelve.total_qty/(GREATEST(NVL(y.FORECAST_QUANTITY, 0), NVL(z.sales, 0))/7)
     END Shelve_DOC
  FROM ...
 WHERE ....

Should do the trick.

answered Nov 27 '13 at 00:59

DCookie

42,630
11
83
92

score 1 · Answer 2 · answered Nov 27 '13 at 01:05

1

Something like this?

CASE WHEN NVL(y.FORECAST_QUANTITY,0) <= 0 AND NVL(z.sales,0) <= 0
     THEN NULL
     ELSE shelve.total_qty/(GREATEST(NVL(y.FORECAST_QUANTITY, 0), NVL(z.sales, 0))/7)
     END AS Shelve_DOC

answered Nov 27 '13 at 01:05

MT0

143,790
11
59
117

This worked perfectly as well! Glad I can select two answers. – Nomad Nov 27 '13 at 01:23

Oracle Error when dividing by Zero

2 Answers2