OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » zero divisior

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

zero divisior [message #213130]

Tue, 09 January 2007 10:57

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.....

Report message to a moderator

Re: zero divisior [message #213138 is a reply to message #213130]

Tue, 09 January 2007 11:14

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: zero divisior [message #213149 is a reply to message #213130]

Tue, 09 January 2007 11:50

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Your could also do

SELECT decode(sales_price,0,0,square_footage /sales_price)
FROM TEST;

Report message to a moderator

Re: zero divisior [message #213228 is a reply to message #213149]

Tue, 09 January 2007 20:36

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

I like converting it to NULL

SELECT square_footage / NULLIF(sales_price,0)

Ross Leishman

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 30 02:21:48 CST 2024