Home » SQL & PL/SQL » SQL & PL/SQL » how 0 can be replaced
how 0 can be replaced [message #213405] Wed, 10 January 2007 12:13 Go to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
hello sir,
i m creating reports. i want to replace 0 with 1 in any field, i can not use NVL because it operates on NULL values. is there any function which can replace 0. my code is

return (:cashcopy*100)/:supply;

it is giving error ORA-01476: divisor is equal to zero

though i have seen there is no value 0 in supply. Plz give me some solution. thanx in advance

Re: how 0 can be replaced [message #213409 is a reply to message #213405] Wed, 10 January 2007 12:33 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Of you only looked a little bit closer, you'd see almost the same problem only a sight below, and perhaps you'd pick up some ideas.
Re: how 0 can be replaced [message #213413 is a reply to message #213409] Wed, 10 January 2007 12:41 Go to previous messageGo to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
sir, i can't use select statement there. nullif and decode could not be used without select. plz tell some other technique. thanx
Re: how 0 can be replaced [message #213415 is a reply to message #213413] Wed, 10 January 2007 12:45 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You only THINK that you can't use SELECT. Why couldn't you? Regarding the fact that you used the RETURN, it must be a function. So, declare another variable and select the result of this calculation into it, and return the variable. For example:
DECLARE
  retval NUMBER;
BEGIN
  SELECT (:cashcopy*100)/:supply
    INTO retval
    FROM dual;

  RETURN (retval);
END;
I'll leave ZERO-DIVIDE problem to you.
Re: how 0 can be replaced [message #213416 is a reply to message #213415] Wed, 10 January 2007 12:46 Go to previous messageGo to next message
amit_vass2000
Messages: 52
Registered: December 2006
Location: Delhi
Member
Thanks sir, it has worked.
Re: how 0 can be replaced [message #213420 is a reply to message #213416] Wed, 10 January 2007 12:53 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But of course! It is one of those days when almost everything works ./fa/451/0/
Previous Topic: dislaying a number in timestamp format
Next Topic: create table statement
Goto Forum:
  


Current Time: Fri Dec 09 19:15:33 CST 2016

Total time taken to generate the page: 0.13788 seconds