| Sum a char field [message #370730] |
Tue, 25 January 2000 17:51  |
Gayle Pavlik
Messages: 3
Registered: January 2000
|
Junior Member |
|
|
I have a field that is character field, length 15. Users want a sum of the column. It contains both numeric and/or characters. Get
error 01722 if try to use to_number and comes across characters. If field contains numers need to sum, otherwise 0. Any way to
do this short of using decode to test each of the 15 characters and check if number 0 through 9?
|
|
|
|
| Re: Sum a char field [message #370734 is a reply to message #370730] |
Tue, 25 January 2000 19:07  |
Edward Jayaraj
Messages: 7
Registered: December 1999
|
Junior Member |
|
|
Try this :
SELECT
INSTR(TRANSLATE(UPPER('&1'),'ABCDEFGHIKLMNOPQRSTUVWXYZ','--------------------------'),'-')
"Feld_name"
FROM DUAL
--> &1 is the field_name
This will return 1 if there is an alphabet in the value or if it is a number is will return 0.
You have to use a SUM(DECODE(function,0,column_name,0))
function is the select column given in the begining, give the column name instead of the '&1'
Pl., let me know if you have any problem.
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
