# Dividing By zero

posted 2 years ago

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

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

Tea Addict, Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer, Oracle Web Service Developer

posted 2 years ago

Interesting. I'd say that this is because the

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).

`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).

posted 2 years ago
Tea Addict, Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer, Oracle Web Service Developer

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

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

Consider Paul's rocket mass heater. |