Home » SQL & PL/SQL » SQL & PL/SQL » Sum of Character column
icon4.gif  Sum of Character column [message #445990] Fri, 05 March 2010 01:12 Go to next message
veemani
Messages: 10
Registered: July 2009
Location: Chennai
Junior Member
Hi,
There is a table like below
seq no. number(6)
amount varchar2(20,2)

The values in the table are:
Rec1
123456
100.00

Rec2
123457
99.01

Rec3
123458
9a.01

Rec4
123459
NULL

Now I want to summarize the amount column replacing non numeric and null values to '0'(zero's).

Can anyone help me on how to frame query for this.
I have am
Re: Sum of Character column [message #445991 is a reply to message #445990] Fri, 05 March 2010 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
create or replace function my_to_number(p_val in varchar2)
return number
is
  l_val            number;
  x_invalid_number exception;
  x_value_error    exception;
  pragma exception_init(x_invalid_number, -1722);
  pragma exception_init(x_value_error, -6502);
begin
  l_val := to_number(p_val);
  return l_val;
exception
  when x_invalid_number or x_value_error then return 0;
end;
/

SQL> with data as ( select '1' val from dual union all select 'a' from dual )
  2  select val, my_to_number(val) num
  3  from data
  4  /
V        NUM
- ----------
1          1
a          0

2 rows selected.

Next time try to follow the guide and post a WORKING test case with create table and insert statements and FORMAT them as I did with code tags.

Regards
Michel

[Updated on: Fri, 05 March 2010 01:22]

Report message to a moderator

Re: Sum of Character column [message #445992 is a reply to message #445990] Fri, 05 March 2010 01:25 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And with NULL condition:
create or replace function my_to_number(p_val in varchar2)
return number
is
  l_val            number;
  x_invalid_number exception;
  x_value_error    exception;
  pragma exception_init(x_invalid_number, -1722);
  pragma exception_init(x_value_error, -6502);
begin
  l_val := to_number(p_val);
  return nvl(l_val,0);
exception
  when x_invalid_number or x_value_error then return 0;
end;
/

SQL> with 
  2    data as (
  3      select '1' val from dual 
  4      union all
  5      select 'a' from dual
  6      union all
  7      select null from dual
  8    )
  9  select val, my_to_number(val) num
 10  from data
 11  /
V        NUM
- ----------
1          1
a          0
           0

3 rows selected.

But if you want a number in this column why don't type it with NUMBER?

Regards
Michel

[Updated on: Fri, 05 March 2010 01:26]

Report message to a moderator

Previous Topic: Sybase str
Next Topic: Test
Goto Forum:
  


Current Time: Sat Dec 10 09:25:57 CST 2016

Total time taken to generate the page: 0.08969 seconds