Sorry Elke, I didn't mean to sound ungrateful ;-)
Thank you very much for your solution. Steve > -----Original Message----- > From: Zabach, Elke [mailto:[EMAIL PROTECTED]] > Sent: 30 April 2002 18:24 > To: '[EMAIL PROTECTED]' > Subject: RE: query question ... > > > Steve Brett wrote: > > > i have a table of people and a table of names( forenames and > > surnames). > > > > there is a link table with person_id,fname_id and position in it and > > surnames are kept in a seperate table. > > > > what i need to do is pull out a specific person with the name > > in the format > > > > <fname1> <fname2> <fname3> <surname> > > > > i can't work out how to do it in a single sql statement .... > > > > any ideas ? > > > > Steve Brett > > SID > > EMIS Ltd. > > > After sending the following answer for up to exactly 3 forenames, > Steve asked for a solution for ANY number of forenames. > (Never give people your little finger for help. They will always ask > for the whole arm or more. ;-) > ) > But anyway, because I was interested myself in finding a > solution, it is at > the end > > Hi, > > assuming these tables filled as given below: > > > CREATE TABLE FNAMES ( > FID INT, > FNAME CHAR (20)) > > FID | FNAME > ------------+--------------------- > 0 | <-- little Trick > 1 | Tom > 2 | Peter > 3 | Frank > 4 | Mary > > > CREATE TABLE SNAMES ( > SID INT, > SNAME CHAR (20)) > > SID | SNAME > ------------+--------------------- > 1 | Miller > 2 | Smith > > > CREATE TABLE PERSON ( > PID INT, > FID INT, > POSITION INT, > SID INT) > > PID | FID | POSITION | SID > ------------+-------------+-------------+------------ > 1 | 2 | 1 | 2 > 1 | 3 | 2 | 2 > 2 | 3 | 1 | 1 > 2 | 1 | 2 | 1 > 2 | 2 | 3 | 1 > 3 | 4 | 1 | 2 > > the wanted (?) result: > > Mary Smith > Peter Frank Smith > Frank Tom Peter Miller > > > Ok, try this 'easy' single select: > > > SELECT F1.FNAME > || DECODE (LENGTH(F2.FNAME),0, '', ' ' || F2.FNAME) > || DECODE (LENGTH(F3.FNAME),0, '', ' ' || F3.FNAME) > || ' ' || SNAMES.SNAME > FROM > (SELECT P1.SID, P1.FID, > VALUE (P2.FID, 0) FID2, > VALUE (P3.FID, 0) FID3 > FROM PERSON P1, PERSON P2, PERSON P3 > WHERE P1.POSITION = 1 > AND P1.PID = P2.PID (+) > AND P2.POSITION (+) = P1.POSITION + 1 > AND P1.PID = P3.PID (+) > AND P3.POSITION (+) = P1.POSITION + 2 > ) ONE_PERSON, > FNAMES F1, FNAMES F2, FNAMES F3, SNAMES > WHERE > ONE_PERSON.FID = F1.FID > AND ONE_PERSON.FID2 = F2.FID > AND ONE_PERSON.FID3 = F3.FID > AND ONE_PERSON.SID = SNAMES.SID > > If you want just one person, you should qualify p1 in the > inner select. > If you can accept superfluous blanks between last forename and > the surname (if person does not have 3 forenames) this funny > DECODE(..) > can be shortened to ' ' || f2.fname resp. ' ' || f3.fname. > > > DECLARE C CURSOR FOR > WITH RECURSIVE PX (FORENAMES, SID, PID, MYLEVEL) AS > (SELECT FNAME, SID, PID, 1 > FROM PERSON, FNAMES > WHERE PERSON.POSITION = 1 > AND PERSON.FID = FNAMES.FID > UNION ALL > SELECT FORENAMES || ' ' || FNAME, PX.SID, PX.PID, MYLEVEL + 1 > FROM PERSON, FNAMES, PX > WHERE PERSON.PID = PX.PID > AND PERSON.POSITION = PX.MYLEVEL + 1 > AND PERSON.FID = FNAMES.FID > ) > SELECT FORENAMES || ' ' || SNAME > FROM PX, SNAMES > WHERE PX.SID = SNAMES.SID > AND (PID,MYLEVEL) = ANY (SELECT PID,MAX(POSITION) > FROM PERSON > GROUP BY PID > ) > > Do not forget to restrict pid in the first select + (better) > in the second > one > + in the subquery, if you want just one person. > > > Elke > SAP Labs Berlin > > > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general > _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
