| Sum value of ASCII characters [message #335820] |
Wed, 23 July 2008 18:30  |
doicomehereoften1
Messages: 13
Registered: June 2008
Location: CO
|
Junior Member |
|
|
Hello! I have a column that contains a single character for each record. Each person can have multiple records. What I am trying to do for output is so that it's one person per row, with a column replacing the single character column with something that uniquely identifies the sum of all of the ASCII values of the single characters a person has. There may be a better way to do the summed single-character column, so if you have a better suggestion, please let me know.
The only thing I can figure out is essentially the same as below and it doesn't work.
Select person, Sum(ASCII(sgc)) "sum_col"
from atab
group by person |
|
|
|
|
|
| Re: Sum value of ASCII characters [message #335823 is a reply to message #335820] |
Wed, 23 July 2008 18:50  |
doicomehereoften1
Messages: 13
Registered: June 2008
Location: CO
|
Junior Member |
|
|
Well, based on my post count, you safely assumed I am new to the forums and SQL in general, however I have read the guidelines, thank you very much.
Due to my relative newness with SQL, I assumed that I had missed something that would be obvious to someone more experienced than I am. After Google-searching as well as searching the forums, I didn't find an answer to my 1 1/2 workday-old problem, hence the new thread, appealing to those more experienced.
That's all right though. I found the answer.
Please make your posts more snarky in the future to have them reported.
|
|
|
|
| Re: Sum value of ASCII characters [message #335824 is a reply to message #335820] |
Wed, 23 July 2008 18:56 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If I understand you correctly, the query that you posted returns results without error, as demonstrated below, but the values may not be unique. Perhaps you can explain why you want this. If you are looking for a unique identifier, then a sequence is the standard method.
SCOTT@orcl_11g> CREATE TABLE atab AS
2 SELECT dname AS person, SUBSTR (dname, rn, 1) AS sgc
3 FROM dept,
4 (SELECT ROWNUM rn
5 FROM DUAL
6 CONNECT BY LEVEL <= 3)
7 /
Table created.
SCOTT@orcl_11g> SELECT * FROM atab ORDER BY person
2 /
PERSON S
-------------- -
ACCOUNTING A
ACCOUNTING C
ACCOUNTING C
OPERATIONS P
OPERATIONS O
OPERATIONS E
RESEARCH R
RESEARCH E
RESEARCH S
SALES L
SALES S
SALES A
12 rows selected.
SCOTT@orcl_11g> Select person, Sum(ASCII(sgc)) "sum_col"
2 from atab
3 group by person
4 /
PERSON sum_col
-------------- ----------
ACCOUNTING 199
OPERATIONS 228
RESEARCH 234
SALES 224
SCOTT@orcl_11g>
|
|
|
|
|
|
|
|
| Re: Sum value of ASCII characters [message #335827 is a reply to message #335820] |
Wed, 23 July 2008 19:05 |
doicomehereoften1
Messages: 13
Registered: June 2008
Location: CO
|
Junior Member |
|
|
|
Thanks for your help! I guess part of the explanation of my problem was a little bit misleading. They're not necessarily unique per se, given that two people could have the same sets of characters. The ultimate idea is to feed the Summed number into a Decode so that I can return a Yes/No answer based on the result. I did find my problem and solved it, though. It wasn't actually anything wrong with the arrangement of the code as I've been thinking for the last day, I stupidly overlooked a typo until right after I posted the thread. The reason I didn't catch it is that the system I'm building this in (Sungard HE's Banner) doesn't have debugging help to speak of and I don't always have access to a workstation with SQL Developer.
|
|
|
|
| Re: Sum value of ASCII characters [message #335828 is a reply to message #335826] |
Wed, 23 July 2008 19:11 |
doicomehereoften1
Messages: 13
Registered: June 2008
Location: CO
|
Junior Member |
|
|
| anacedent wrote on Wed, 23 July 2008 18:01 | > however I have read the guidelines, thank you very much.
So you admit to purposefully did not follow the guidelines which
state you should post sample inputs, & desired outputs.
Are we supposed to mind read what answer you expect?
|
I pm'd you about it, this isn't place to be having this discussion.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
