Re: Strange Join needed...
From: Steven Godkin <sgodkin_at_Sophia.NCR.COM>
Date: 1 Nov 94 15:43:01 GMT
Message-ID: <5985_at_ncrsoph.Sophia.NCR.COM>
END; Something like this should suffice in this situation. You could either use the standard output procedures for printing, or insert the information into a temporary table and select from the table after the PLSQL completes.
Date: 1 Nov 94 15:43:01 GMT
Message-ID: <5985_at_ncrsoph.Sophia.NCR.COM>
If you always have the same number of entries in B and C for each record in A, then the SQL code would be fairly straightforward. However I am assuming that there will be 3 B's and 2 C's for John Smith, but for Joe Bloggs there could be 12 B's and 1 C. In this case I would recommend a simple PL/SQL program similar to the one below:
DECLARE
field3 varchar2(30);
field4 varchar2(30);
Cursor one is select key_id,field1,field2 from a;
Cursor two (akey_id) is select field3 from b where b.key_id = akey_id;
Cursor three (akey_id) is select field4 from c where c.key_id = akey_id;
BEGIN
FOR arec in one LOOP
field3 := ''; field4 := ''; FOR brec in two (arec.key_id) LOOP field3 := field3||brec.field3||','; END LOOP; FOR crec in three (arec.key_id) LOOP field4 := field4||crec.field4||','; END LOOP;END LOOP;
"Get rid of the last comma in field3 and field4"
"Print arec.field1, arec.field2, field3 and field4"
END; Something like this should suffice in this situation. You could either use the standard output procedures for printing, or insert the information into a temporary table and select from the table after the PLSQL completes.
Steven Godkin Received on Tue Nov 01 1994 - 16:43:01 CET