Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-01476: divisor equals to zero

sgalaxyJun 17 2015 — edited Jun 17 2015

Hi,
In order to avoid the ORA-01476 oracle error(in sql, not pl/sql) i check the divisor first - so as this not to be zero - and afterwards i do the division.
For example:

with sample_data(n1, n2) as
(select 6, 4 from dual union all
select 8, 0 from dual)
select case when n2<>0 then n1/n2 else 0 end
  from sample_data

Is there any other way to avoid the division by zero error?


I ask this because recently, i faced a situation in which i had a part of sql query like this:

...
case when dxy+dey+kty+kay-kts<>0 then round(100*(ypol/ sum(case when eidos<>'1' then dxy+dey+kty+kay-kts  end) over(partition by ylik_cd))
.....

But if i had a requirement that multiple "when" conditions should be included - as the divisor- the writing of the above
solution would be complicated and prone to errors(in case i forgot a "when" clause).For example if i had:

.....
round(100*(ypol/ sum(case when eidos='1' then dxy+dey+kty+kay-kts when eidos<>'1' and dxy=0 then dxy+dey+kty+kay end) over(partition by ylik_cd))

.......


Note:
I use OraDb 11g v.2

Thank you,
Sim

This post has been answered by Frank Kulash on Jun 17 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 15 2015
Added on Jun 17 2015
5 comments
5,098 views