Re: How about a *FUNCTION* that'd return a ResultSet ??

From: DanHW <danhw_at_aol.com>
Date: 02 Sep 2000 02:23:28 GMT
Message-ID: <20000901222328.10984.00000928_at_ng-fk1.aol.com>


>Let's say I have the following table TAB_1:
>
> CHP_1 CHP_2
> ----- --------------------------------
> 23 chaine1 chaine2 chaine3 chaine4
> 24 truc_1 truc_2 truc_3 truc_4
>
> I'd like to be able to get with this SELECT:
> > SELECT chp1, TORESULTSET(chp2)
> > FROM TAB_1;
>
> the following output:
> CHP_1 TORESULTS
> ----- ---------
> 23 chaine1
> 23 chaine2
> 23 chaine3
> 23 chaine4
> 24 truc_1
> 24 truc_2
> 24 ...
>
> but I'm afraid it's UTTERLY impossible with Oracle
> (I checked the PL/SQL manuals I have at REF CURSOR,
> but all examples are given with procedures; I'd
> really like to perform this with a function...).
>
> If anybody can gimme some hints...
>
> Thanks a lot !
>
>seb
>

Without being a database theorician, I would say that you will never get more than 1 row in the result set of a single table query from each row in the table. To do what you want would require some sort of join. One option would be to create a view that is actually a union of the table, with each subquery in the union parsing out each of the "words"

create view split_words as
select chp_1, substr(chp_2,1,instr(chp_2,' ',1)) chp_2 from tab_2 union
select chp_1, substr(chp_2,instr(chp_2,' ',1)+1, instr(chp_2,' ',2) ) from tab_2
union
select chp_1, substr(chp_2,instr(chp_2,' ',3)+1, instr(chp_2,' ',3) ) from tab_2
etc

You might not be able to do this very easily if you don't know how many "words" are in chp_2. Another option would be to write a function that did this correctly. You would probably actually use 2 functions - one to return the chp_1 value (if there was a word, return the chp_1 value; if there was no word, return a null), and one to return the n-th word in chp_2. (returns a null if no word)

Then, a query like

select a.chp_1, b.chp_2 from tab_1 a, split_words b where a.chp_1 = b.chp_2

should do what you are looking for.

Dan Hekimian-Williams Received on Sat Sep 02 2000 - 04:23:28 CEST

Original text of this message