Home » SQL & PL/SQL » SQL & PL/SQL » Zero Divide
Zero Divide [message #225223] Mon, 19 March 2007 03:53 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
EX > select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

EX > select sal,comm,sal/comm from emp;
ERROR:
ORA-01476: divisor is equal to zero



no rows selected



How can I avoid this error?

rzkhan


Re: Zero Divide [message #225226 is a reply to message #225223] Mon, 19 March 2007 04:07 Go to previous messageGo to next message
rollyortiz
Messages: 4
Registered: March 2007
Junior Member
You could try using the condition "where" like as follows:

select sal, comm, sal/comm
from emp
where comm <> 0;

The reason for this is, you cannot divide a number by zero.
Re: Zero Divide [message #225227 is a reply to message #225226] Mon, 19 March 2007 04:16 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
But I need all the rows returned by the query. you are omitting the rows with null comm....

which does not satisfy my condition

rzkhan
Re: Zero Divide [message #225228 is a reply to message #225223] Mon, 19 March 2007 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use decode or case instruction, for instance:

SQL> select sal,comm, decode(comm,0,to_number(null),sal/comm) ratio from emp;
       SAL       COMM      RATIO
---------- ---------- ----------
       800
      1600        300 5.33333333
      1250        500        2.5
      2975
      1250       1400 .892857143
      2850
      2450
      3000
      5000
      1500          0
      1100
       950
      3000
      1300

14 rows selected.

Regards
Michel

Re: Zero Divide [message #225235 is a reply to message #225227] Mon, 19 March 2007 04:34 Go to previous messageGo to next message
rollyortiz
Messages: 4
Registered: March 2007
Junior Member
Try using the following



SELECT sal,comm,
DECODE(comm,0,sal*0,sal/comm) as "sal/comm"
FROM emp;
Re: Zero Divide [message #225238 is a reply to message #225228] Mon, 19 March 2007 04:40 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
i have doubt in those query.
actually, null means not zero

select sal,comm, decode(comm,0,to_number(null),sal/comm) ratio from emp

but ur query working how is it possible.

Thanks,
Srinivas

Re: Zero Divide [message #225241 is a reply to message #225238] Mon, 19 March 2007 04:56 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The error is caused by the record that has a ZERO in the COMM-column (empid 7844), NOT by the nulls.
Division by null yields null, division by zero yields an error.
Previous Topic: query help
Next Topic: sql help
Goto Forum:
  


Current Time: Fri Dec 02 22:42:22 CST 2016

Total time taken to generate the page: 0.11211 seconds