• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2086
  • Last Modified:

Error: divisor is equal to zero, when using equation within a procedure

Hello,

Please help. I get the ERROR message below:
 
ORA-01476:divisor is equal to zero

Whenever I try and run this SQL WITHIN a PL/SQL procedure:

INSERT INTO table2(ANSWER) SELECT (P1 + (((P2-P1)*(SRL-R1))/(R2-R1)))
             FROM table1
             WHERE condition;

But when I run the same SQL (with hard coded condition) from the SQL command line it works for all of the values in table 1 !!

How can this be and how can it be put right ? It must be run within a procedure.

Oh P1 P2 SRL R1 R2 are all of type float

Thanks in advance

Ren
0
Beam
Asked:
Beam
  • 4
  • 2
  • 2
  • +2
1 Solution
 
alexfrlCommented:
This one works perfectly

INSERT INTO table2(ANSWER)
SELECT P1 + (P2-P1)*(SRL-R1)/decode(r2,r1,null,R2-R1)
           FROM table1
           WHERE condition;
0
 
BeamAuthor Commented:
Will test it and get back to you.
0
 
kumaran100Commented:
Hi,
Surely Divisor is Zero.

Try this
INSERT INTO table2(ANSWER) SELECT (P1 + (((P2-P1)*(SRL-R1))/    (R2-R1)     ))
           FROM table1
           WHERE condition;

Instead of (R2-R1) , Use the following condition

decode( R2-R1, 0, 1, R2-R1)    

Explanation:
  if (R2-R1) =  0 then
      divide by 1
  else
      divide by  (R2-R1)
  end if
 
So ur query will become

INSERT INTO table2(ANSWER) SELECT (P1 + (((P2-P1)*(SRL-R1))/    decode( R2-R1, 0, 1, R2-R1)     ))
           FROM table1
           WHERE condition;

I think this will help u.

rgds,
kums
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
BeamAuthor Commented:
Thanks for quick replies guys,

I tried your version alexfrl but for every instance where the sum involved an 0 or minus value the result would be null. So when I ran it on mutiple rows in table1 and inserted the answer into table2 there was lots of blanks.

I need to be able to do the mathes when zero and negative values are involved not just skip it. Any Ideas?

Kums would your suggest workout the answer where zero and/or negitive numbers are involved? Sorry I can not check it until tomorrow as do not have access to the DB server.

Thanks

Ren
0
 
pettmansCommented:
"I need to be able to do the mathes when zero and negative values are involved not just skip it. Any Ideas?"

You cannae change the laws of maths.

For those rows of Table1 where R1 = R2, the maths will always provide a value of "undefined" for that formula. So you need to either:
- exclude calculation for rows that meet that condition or;
- use a different calculation for rows that meet that condition.

Regards,
Scott
0
 
alexfrlCommented:
Let's ask it in another way: what result do you want to get in the case of R1=R2? Is this a the result of :
1) P1 + (P2-P1)*(SRL-R1)?
2) P1 + (P2-P1)
3) P1?

1)
then use one of the following:
INSERT INTO table2(ANSWER)
SELECT P1 + (P2-P1)*(SRL-R1)/decode(r2,r1,1,R2-R1)
           FROM table1
           WHERE condition;
2)
INSERT INTO table2(ANSWER)
SELECT decode(r2,r1,P2,P1 + (P2-P1)*(SRL-R1)/(R2-R1))
           FROM table1
           WHERE condition;

3)
INSERT INTO table2(ANSWER)
SELECT decode(r2,r1,P1,P1 + (P2-P1)*(SRL-R1)/(R2-R1))
           FROM table1
           WHERE condition;
0
 
BeamAuthor Commented:
Scott, like I said in original question " But when I run the same SQL (with hard coded condition) from the SQL command line it works for all of the values in table 1 !!". That the prob, not the math.

alexfrl, I will test your solution tomorrow and get back to you.


Thanks again all forl the help.

Ren
0
 
pettmansCommented:
Are there any rows in the table where R1 = R2 ?

Are there any rows in the table where wither R1 or R2 is null?

What is the hardcoded condition that you use?

Scott
0
 
oratimCommented:
run this then

select * from table 1 where <your hardcoded condition> and r1 = r2


if you get no rows returned, then that explains why it always works for you with the hardcoded condition, because there are no rows that fit your condition that will result in a zero divisor.

Then run this:

select * from table1 where r1 = r2

if you get any rows, they will give you the error you are getting , divide by zero.

in that case, you will need to use one of the solutions above, depending on how your want to handle it, or add another condition to the where clause to exclude selecting rows from table1 where r1 = r2, like this

INSERT INTO table2(ANSWER) SELECT (P1 + (((P2-P1)*(SRL-R1))/(R2-R1)))
           FROM table1
           WHERE condition AND R1 <> R2;


tim

0
 
kumaran100Commented:
Hi Ren,

Here is the solution for handling null,zero or negative.

decode( SIGN(R2-R1), NULL, 1, 0, 1, 1 )  

Explanation:

SIGN(R2-R1)  will return either null, 0, 1 or -1

IF SIGN(R2-R1) is NULL then
   1          -- you can use what ever u want  ( in this ex i am using  1)
elsif SIGN(R2-R1) = 0 then
   1          -- you can use what ever u want  ( in this ex i am using  1)
else
   1
end if;

Surely this will help You.

regards,
kums
0
 
BeamAuthor Commented:
Thanks very much for all the help guys.

I am sure that ya latest suggestions work Scott and Kums but I final got it working as needed using alexfrl's example:

INSERT INTO table2(ANSWER)
SELECT P1 + (P2-P1)*(SRL-R1)/decode(r2,r1,1,R2-R1)
           FROM table1
           WHERE condition;


Thanks so much.

Ren
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now