aspose file tools*
The moose likes Oracle/OAS and the fly likes Dividing By zero Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Dividing By zero" Watch "Dividing By zero" New topic
Author

Dividing By zero

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 283

I was doing a practice exam question and get across a question that included divide by zero scenario.

I tried this

select 1/0 from dual;

ERROR at line 1:
ORA-01476: divisor is equal to zero (I think for division operation it checks the divisor to make sure it is not a zero)

So why below query does not result in error.

select 1
from dual
where exists( select 1/0
from dual);

1
------
1


Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Interesting. I'd say that this is because the EXIST condition just looks whether the subquery returns any row, but doesn't evaluate any expressions in the SELECT clause. It doesn't have to - the existence of rows depends only on the WHERE clause, not on the expressions in the SELECT clause.

This particular case illustrates a general aspect of SQL, which is that you don't have any control over how many times and in which order individual expressions in your SQL statements will be evaluated. This has consequences especially when calling stored procedures from SQL statements, where the number of times they get called may be significant (unlike in pure SQL, where it doesn't matter).
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 283

I have created a table
create table idm(id number);
insert into idm values(1);
insert into idm values(2);
insert into idm values(3);

update idm
set id=1/0
where id=4;

This does not gives divide by zero error. (this is because here it is not trying to find out 1/0 as there is no row with id=4)
But if I try this

update idm
set id=1/0
where id=3; (this is because here it is trying to solve 1/0 as there is a row with id=3)

this results in divide by zero error
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Dividing By zero