| Need to sum char field in SQLPLUS [message #370731] |
Tue, 25 January 2000 17:53  |
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: Need to sum char field in SQLPLUS [message #370737 is a reply to message #370731] |
Wed, 26 January 2000 10:01  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
Gayle,
The quick & dirty answer is that you will either need to use decode, or a function you write, to do this. That said, I'm not 100% clear on WHAT you really want to do - are you talking about summing multiple rows, grouped by another field (as in SELECT a, SUM(b) FROM xx GROUP BY a) and, if so, are you saying that any row that contains characters should make the sum 0 or that each row containing characters should itself be counted as 0 in the sum. Or, are you saying that for each row, if the individual characters are all numeric, sum the value of the single digits (sort of like a checksum), otherwise return 0. Or are you asking for something else all together.
Beyond this, it might be worth explaining to the 'powers-that-be' that there is a tremendous amount of overhead involved in doing this kind of per-record validation in a query and that the best solution is to segregate numeric and non-numeric data into separate fields or, if this is impossible, to add an additional 'flag' field to the table(s) involved to hold a datatype identifier. The second method would allow you to run a function once to populate this field for your existing data, then incorporate the function logic (or the function itself) into a database trigger that would validate each row inserted and updated. This would simplify, and therefore speed up, retrieval. It is still the 'second best' way to solve the problem because you still must convert the numerics to numbers individually.
Hope this helps,
Paul
|
|
|
|
| Re: Need to sum char field in SQLPLUS [message #370741 is a reply to message #370731] |
Thu, 27 January 2000 07:09 |
Atavur Rahaman S.A
Messages: 23
Registered: January 2000
|
Junior Member |
|
|
Hi Gayli Pavlik,
The worst and labour process is to use TRANSLATE and SUBSTR functions. Hope so this will definetly work with any type of possible combinations. It's really dirty but worth for your query.
This will be helpful only if the VSIZE of the column is 15. You have to use it for 15 times.
select ltrim(to_char(
to_number(nvl(substr(ltrim(translate(dummy,'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+=*-/~><?',' )),1,1),0))<BR >+
...
...
.
.
.+
to_number(nvl(substr(ltrim(translate(dummy,'abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+=*-/~><?',' )),15,1),0)))) FROM
Best regards
Atavur Rahaman S.A
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
