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.
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.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
