Home » Developer & Programmer » Reports & Discoverer » Discoverer divide by zero
Discoverer divide by zero [message #223833] Sun, 11 March 2007 23:34 Go to next message
aankha
Messages: 8
Registered: January 2007
Location: Nepal
Junior Member
I am using Oracle Discoverer Plus and I have some columns in the database table that periodically take the zero value. I have to use that column to divide other values. Is there any way I can handle this to show "0" when it is divided by zero?

Discoverer Plus help shows that PL/SQL functions can be used in calculations. But I can't find the Register PL/ SQL function option in the Tools menu. Help please!!
Re: Discoverer divide by zero [message #223854 is a reply to message #223833] Mon, 12 March 2007 01:57 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
You can create a calculation like:

decode(value1,null,0,(value2/value1))


Thus stating: if value1 equals null then return 0, if value1 is not null then divide value2 by value1. Variations on this theme can be done using CASE WHEN ELSE END.

The reference refering to the PL/SQL functions means that you can create a function in the database (ie "custom_divide"), register that in the EUL and use it in the Discoverer front end. But that seems a bit overdone just to avoid a divide by zero...
Re: Discoverer divide by zero [message #223891 is a reply to message #223833] Mon, 12 March 2007 03:43 Go to previous messageGo to next message
aankha
Messages: 8
Registered: January 2007
Location: Nepal
Junior Member
Thanks skooman!

I was ready to pull my hairs out with that one.

Re: Discoverer divide by zero [message #223913 is a reply to message #223891] Mon, 12 March 2007 04:44 Go to previous messageGo to next message
Littlefoot
Messages: 19697
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
... so next morning your comb would have to divide by zero.
Re: Discoverer divide by zero [message #224044 is a reply to message #223913] Mon, 12 March 2007 13:41 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Laughing
Re: Discoverer divide by zero [message #224079 is a reply to message #223833] Mon, 12 March 2007 22:51 Go to previous messageGo to next message
aankha
Messages: 8
Registered: January 2007
Location: Nepal
Junior Member
If I had managed to really pull out my hair.. it would be useless to me.. so why would i comb it??

Idea I'd rather send it to you...


Re: Discoverer divide by zero [message #476110 is a reply to message #224079] Tue, 21 September 2010 07:40 Go to previous message
vinayjoshipune
Messages: 1
Registered: September 2010
Location: PUNE
Junior Member
Hi,

you can use value2/nullif(value1,0). This return null.
Previous Topic: Master Detail Report using Oracle Discoverer 10g
Next Topic: need help for my question
Goto Forum:
  


Current Time: Fri Oct 24 11:38:27 CDT 2014

Total time taken to generate the page: 0.05872 seconds