RE: JOIN question

From: Sdjaroud <sdjaroud_at_lth.sonatrach.dz>
Date: Tue, 13 Jun 2000 14:41:36 -0000
Message-Id: <10527.108918@fatcity.com>

Sorry i don't found another way!!!

SQL>create or replace function Liste(Num Number) return varchar2 is

       V varchar2(50);
       tmp varchar2(10);

 cursor LC is select code from codes where docnum=Num; begin
v:=' ';
open LC;
fetch LC into tmp;
while LC%found loop
V:=V || ' ' ||tmp;
fetch LC into tmp;
end loop;
return V;
end;

SQL>select main.docnum,main.text,Liste(main.docnum) "Liste des codes" from main;  

---

De : 	Andreas Jung[SMTP:ajung_at_sz-sb.de]
Repondre a : 	ORACLE-L_at_fatcity.com
Date :	mardi 13 juin 2000 09:10
A :	Multiple recipients of list ORACLE-L
Objet :	JOIN question

Assume the following tables:

MAIN(docnum number primary key,text varchar2); CODES(docnum number ,code varchar2);

The CODES table contains multiple rows with the same docnum. I want to fetch the "text" and all "code" columns with the same docnum in a single row.

I tried:
select main.docnum,main.text,codes.code where main.docnum=codes.docnum

But this gives me a row for every row in CODES but not a single one. How can I reduce the multiple rows to a single one ?

Andreas

-- 
Author: Andreas Jung
  INET: ajung_at_sz-sb.de

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jun 13 2000 - 09:41:36 CDT