Home » RDBMS Server » Server Administration » Sum a char field
Sum a char field [message #370730] Tue, 25 January 2000 17:51 Go to next message
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 Go to previous message
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
Previous Topic: Challenging One...
Next Topic: Speeding up a query
Goto Forum:
  


Current Time: Thu Mar 28 17:48:17 CDT 2024