| zero divide error... [message #38526] |
Wed, 24 April 2002 15:18  |
shreya
Messages: 15
Registered: March 2001
|
Junior Member |
|
|
Hi ,
My query is like this.
SELECT a.* FROM schema1.table1@dlink1 a,schema2.table2@dlink2 b
WHERE ABS((a.amount-b.amount))/a.amount >= 0.05
AND a.id = b.id ;
If a.amount is 0 it will give the following error.
ORA-01476: divisor is equal to zero
Does anyone have any idea how to get rid of this problem.
I tried including a.amount!=0 in the where clause but what if b.amount is having some value.
Thanks in advance,
Shreya.
|
|
|
|
|
|
|
|
| Re: zero divide error... [message #38534 is a reply to message #38526] |
Thu, 25 April 2002 04:16  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Or, to avoid returning rows where a.amount is 0, which this solution will do if abs(b.amount) >= 0.05, try
WHERE ABS(a.amount - b.amount)/decode(a.amount,0,b.amount*30,a.amount) >= 0.05
Also, are you sure the ABS shouldn't go round the whole calculation - the way it is currently written it will not return any rows where A.amount < 0
|
|
|
|
| Re: zero divide error... [message #38538 is a reply to message #38526] |
Thu, 25 April 2002 09:08 |
shreya
Messages: 15
Registered: March 2001
|
Junior Member |
|
|
Hi John,
It should work for all conditions
i.e if a.amount is<0 also it should work. I didn't try for that condition. If u are sure it doesn't work for a.amount<0 then please send me the query that works for <0 condition.
Thanks very much,
Shreya.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
