Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> see error in query ORA-01476: divisor is equal to zero
mfa
post Jul 21 2006, 02:20 AM
Post #1


Advanced Member
***

Group: Members
Posts: 55
Joined: 16-February 06
Member No.: 3,697



sir i user this query but system give me error

SELECT STUSECMTAMT.totdebit/STUSECMTAMT.totalcr,STUJOURNAL.cre_bal/(STUSECMTAMT.totdebit/STUSECMTAMT.totalcr) FROM STUSECMTAMT,STUDEG,STUJOURNAL WHERE STUSECMTAMT.vhno=STUJOURNAL.ref_no AND STUSECMTAMT.studegid=STUDEG.studegid;

error


ERROR at line 1:
ORA-01476: divisor is equal to zero


please give me idea

thank

aamir
Go to the top of the page
 
+Quote Post
campbell_b
post Jul 21 2006, 02:34 AM
Post #2


Advanced Member
***

Group: Members
Posts: 91
Joined: 3-May 06
Member No.: 4,131



It means what it says, you are dividing by 0.

This means that either

STUSECMTAMT.totalcr, (STUSECMTAMT.totdebit/STUSECMTAMT.totalcr) or STUSECMTAMT.totalcr equates to 0.

You could use a decode or a case to capture the 0 and deal with it

i.e.

STUSECMTAMT.totdebit / DECODE(STUSECMTAMT.totalcr,0,NULL,STUSECMTAMT.totalcr)

This would then return NULL.
Go to the top of the page
 
+Quote Post
michaelasc
post Jul 21 2006, 03:52 PM
Post #3


Advanced Member
***

Group: Members
Posts: 189
Joined: 5-May 05
From: Cookeville, Tennessee
Member No.: 2,014



Hi
You can also put the calculation inside the DECODE, thus only attempting it when the divisor is not zero.

In the following example a zero will be returned whenever the divisor is 0.

DECODE(STUSECMTAMT.totalcr,0,0,STUSECMTAMT.totdebit / STUSECMTAMT.totalcr)

or why not try a CASE statement?

CASE WHEN STUSECMTAMT.totalcr = 0 THEN 0 ELSE STUSECMTAMT.totdebit / STUSECMTAMT.totalcr END

I hope this helps
Regards
Michael
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd December 2014 - 02:47 PM