OraFAQ Forum: SQL & PL/SQL » How to avoid division by Zero?

Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid division by Zero?

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

How to avoid division by Zero? [message #11154]

Tue, 09 March 2004 21:15

SillyGuy
Messages: 2
Registered: March 2004

Junior Member

I am using a simple SQL query in VB to produce result. I am using formulas as well. My Problem is that, some fields can contain zero and my formula has some division. How to avoid division by zero?

Report message to a moderator

Re: How to avoid division by Zero? [message #11157 is a reply to message #11154]

Tue, 09 March 2004 22:54

Rajarshi Dasgupta
Messages: 52
Registered: October 2001

Member

May write something like....

Select Field1/Decode(Field2, 0, 1, Field2)
from Table1;

If you feel Field2 may contain NULL... then you may write somthing like...

Select Field1/Decode(NVL(Field2, 0), 0, 1, Field2)
from Table1;

Report message to a moderator

Re: How to avoid division by Zero? [message #11190 is a reply to message #11157]

Wed, 10 March 2004 22:23

SillyGuy
Messages: 2
Registered: March 2004

Junior Member

Select FinYear,(GrProf/SalesRev)*100,(OperProf/SalesRev)*100,(EATax/SalesRev)*100,(GrProf/EATax)*100,(Sales/CGS)*100,(OperExp/Sales)*100,(Sales/TotCA)*100,(TotCA/TotOutLiab)*100 from manufacturing

If above is the query how can I use the Decode function. Any field can have Zero value.

Thanks for the reply.

Report message to a moderator

Re: How to avoid division by Zero? [message #11198 is a reply to message #11190]

Fri, 12 March 2004 03:11

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

SELECT m.finyear
,      DECODE(m.salesrev,0,0,(m.grprof / m.salesrev) * 100)
,      DECODE(m.salesrev,0,0,(m.operprof / m.salesrev) * 100)
,      DECODE(m.salesrev,0,0,(m.eatax / m.salesrev) * 100)
,      DECODE(m.eatax,0,0,(m.grprof / m.eatax) * 100)
,      DECODE(m.cgs,0,0,(m.sales / m.cgs) * 100)
,      DECODE(m.sales,0,0,(m.operexp / m.sales) * 100)
,      DECODE(m.totca,0,0,(m.sales / m.totca) * 100)
,      DECODE(m.totoutliab,0,0,(m.totca / m.totoutliab) * 100)
FROM   manufacturing       m

HTH,

Art.

Report message to a moderator

Previous Topic:	sql*plus installation question
Next Topic:	Query to show days in a month.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 04:30:09 CDT 2024