Sum of Character column [message #445990] |
Fri, 05 March 2010 01:12 |
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 |
|
Michel Cadot
Messages: 68704 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 |
|
Michel Cadot
Messages: 68704 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
|
|
|