Home » SQL & PL/SQL » SQL & PL/SQL » zero divisior
zero divisior [message #213130] Tue, 09 January 2007 10:57 Go to next message
PRGMER
Messages: 5
Registered: June 2006
Junior Member
new to sql.....trying to perform division statement keep getting zero divisior error...
how do i get rid of error...

square_footage / sales_price.....
Re: zero divisior [message #213138 is a reply to message #213130] Tue, 09 January 2007 11:14 Go to previous messageGo to next message
Littlefoot
Messages: 21042
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, don't divide by zero! Use DECODE function and choose either a very large value, or a very small value. For example:
SELECT square_footage / DECODE(sales_price, 0, 1E99) 
FROM TEST;

This will produce a very small result (pratically 0).
Re: zero divisior [message #213149 is a reply to message #213130] Tue, 09 January 2007 11:50 Go to previous messageGo to next message
Bill B
Messages: 1571
Registered: December 2004
Senior Member
Your could also do

SELECT decode(sales_price,0,0,square_footage /sales_price)
FROM TEST;
Re: zero divisior [message #213228 is a reply to message #213149] Tue, 09 January 2007 20:36 Go to previous message
rleishman
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I like converting it to NULL

SELECT square_footage / NULLIF(sales_price,0)


Ross Leishman
Previous Topic: Connect by help 2
Next Topic: My trigger has run amok and I can't DROP it--Help, please
Goto Forum:
  


Current Time: Wed Mar 29 20:00:38 CDT 2017

Total time taken to generate the page: 0.12917 seconds