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