Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!pd2nf1so.cg.shawcable.net!residential.shaw.ca!sjc70.webusenet.com!news.webusenet.com!cyclone.bc.net!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: derekabernethy@hotmail.com (Derek Abernethy)
Newsgroups: comp.databases.oracle.misc
Subject: How to append information to simmilar data
Date: 8 Aug 2003 02:57:15 -0700
Organization: http://groups.google.com/
Lines: 45
Message-ID: <17af6360.0308080157.76fbef08@posting.google.com>
NNTP-Posting-Host: 194.83.70.228
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1060336636 23365 127.0.0.1 (8 Aug 2003 09:57:16 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2003 09:57:16 GMT
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:130359

Hi

I'm currently pulling out our user information for the tech guys to
set up our new email system. Unfortunately they've decided to go with
forename.surname as the username to make life easier so people wont
always be forgetting their usernames.

Now as you can imagine using this system there are quite alot of
duplicates. I've wrote two queries one which pulls out all the unique
forename.surname users and one which gives me all the duplicates
usernames.

What I was wondering is if its possible to append these usernames when
pulling out the info so that instead of

john.smith , field2, field3
john.smith , field2, field3
john.smith , field2, field3

I get

john.smith1 , field2, field3
john.smith2 , field2, field3
john.smith3 , field2, field3

The rest of the fields are different for every user so they are not
true duplicate data.


Below is the sql statement I'm using to pull out the duplicate users.

select stu_fnm1||'.'||stu_surn as username, field2, field3 ect... 
from table1
where (stu_fnm1||'.'||stu_surn) in (select (stu_fnm1||'.'||stu_surn)
from table1
GROUP BY stu_fnm1||'.'||stu_surn
HAVING Count(stu_fnm1||'.'||stu_surn)>1)
order by username

Any Suggestions would be appreciated as otherwise I'll prob have to go
through all those records and do it manually.

Thanks

Derek