Home » SQL & PL/SQL » SQL & PL/SQL » Sum value of ASCII characters (Oracle 10g)
Sum value of ASCII characters [message #335820] Wed, 23 July 2008 18:30 Go to next message
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 #335821 is a reply to message #335820] Wed, 23 July 2008 18:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>it doesn't work.
My car doesn't work.
Tell me how to make it go.

Please be more vague in the future to make answering more challenging.
Re: Sum value of ASCII characters [message #335823 is a reply to message #335820] Wed, 23 July 2008 18:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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 #335825 is a reply to message #335823] Wed, 23 July 2008 18:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
doicomehereoften1 wrote on Wed, 23 July 2008 16:50

I found the answer.



Please post what you found for the benefit of others who may search and find this thread in the future.

Re: Sum value of ASCII characters [message #335826 is a reply to message #335820] Wed, 23 July 2008 19:01 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> 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?
Re: Sum value of ASCII characters [message #335827 is a reply to message #335820] Wed, 23 July 2008 19:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Sum value of ASCII characters [message #335831 is a reply to message #335820] Wed, 23 July 2008 19:58 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above

>I found the answer.

which include the following:

If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.

Previous Topic: buffer overflow, limit of 1000000 bytes
Next Topic: PLS-00302: component 'A' must be declared
Goto Forum:
  


Current Time: Fri Dec 09 02:02:13 CST 2016

Total time taken to generate the page: 0.14467 seconds