RE: JOIN question - you can't do it

From: Elliott, Patrick <Patrick.Elliott_at_bestbuy.com>
Date: Tue, 13 Jun 2000 15:09:55 -0500
Message-Id: <10527.109077@fatcity.com>

The only way I know of to do this is to create a stored function that returns a varchar2. This isn't quite what you want, but it comes close.

CREATE OR REPLACE FUNCTION concat_code (cv_docnum number)   lv_codes VARCHAR2(255) := '';
  CURSOR c1 IS
    SELECT code

      FROM codes b
     WHERE a.docnum = cv_docnum
       AND b.docnum = a.docnum;

BEGIN
  FOR r1 IN c1 LOOP
    lv_codes := lv_codes || ' ' || r1.code;   END LOOP;
  RETURN lv_codes;
END; You can then include this function in a select statement.

SELECT text, concat_code(docnum)
  FROM main;

> -----Original Message-----
> From: Glenn Travis [SMTP:Glenn.Travis_at_wcom.com]
> Sent: Tuesday, June 13, 2000 2:21 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: JOIN question - you can't do it
>
>
>
> > -----Original Message-----
> > From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Markellos
> > Diorinos
> > Sent: Tuesday, June 13, 2000 9:08 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: JOIN question - you can't do it
> >
> >
> >
> > > 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.
> >
> > My understanding is that this is principally IMPOSSIBLE in any
> > SQL dialect.
> >
> > You need a result set with an unknown number of columns, i.e. one
> > row can be 1x text, 3x code, the next one could be 1x text, 10x
> > code. There is no support for this in SQL.
> >
> > There are a few features for 'turning arround' a table (i.e.
> > exchanging rows/columns) but this will no do in your case.
> >
>
> Can you explain these features in more detail?
>
> Given table A:
>
> col1 col2
> ---- ----
> Manager John
> Executive Mark
> Customer James
> President Tom
>
> Provide a single query (or a procedure) which returns;
>
> Manager Executive Customer President
> ------- --------- -------- ---------
> John Mark James Tom
>
> What happens when you have table A: ?
>
> col1 col2
> ---- ----
> Manager John
> Executive Mark
> Customer James
> President Tom
> Customer Jim
>
> This is a simple example. A more realistic example would have multiple
> tables and col1 would be the code value describing the 'position' and a
> joinback needed to get the columns headings (Manager, ...) in the output
> row.
>
> I've seen this asked before but cannot locate the solutions. any ideas?
>
> ____________
> Glenn Travis
> MCI WorldCom
> Enterprise Systems Development - DBA
> Glenn.Travis_at_wcom.com
>
>
> > Anybody else knows something that I am missing?
> >
> >
> > rgds
> > markellos
> >
> > ----
> > Markellos J Diorinos
> > Intracom R&D
> > email : mdio_at_intracom.gr
> > tel : ++30-1-6690308
> > fax : ++30-1-6860312
> >
> > --
> > Author: Markellos Diorinos
> > INET: mdio_at_intracom.gr
> >
> > 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).
> >
>
> --
> Author: Glenn Travis
> INET: Glenn.Travis_at_wcom.com
>
> 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
Received on Tue Jun 13 2000 - 15:09:55 CDT