Re: Relational Databases and Their Guts

From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Fri, 27 Jun 2003 09:57:36 -0700
Message-ID: <_O_Ka.7$2b5.100_at_news.oracle.com>

"Marshall Spight" <mspight_at_dnai.com> wrote in message news:n6uJa.108528$YZ2.276226_at_rwcrnsc53...
> ISTM that all that is necessary to do this is an aggregate operator that
> will aggregate a column across a relation into a list (or perhaps set.)
Then
> one could use the aggregate operator to group the various RCOL values
> for each R1PK into a list. The resulting relation would have a list of
> RCOL's type as the associated column type. The case of *no* values
> would produce a zero-length list.
>
> I'm not aware of any existing DBMS that has such an aggregate operator,
> but now that I've thought about it, it seems like a necessity.

There are at least 4 ways to do that in oracle. http://www.dbazine.com/tropashko2.html
lists 3 methods. The 4th method is leveraging oracle notorious "connect by" syntax and sys_connect_by_path aggregate function that concatenates nodes:

with
ordemp as (select * from emp order by deptno), seqemp as (select rownum rn, ordemp.* from ordemp), startfinish as (select min(rn) low, max(rn) high, deptno

                      from seqemp ee group by deptno),

paths as (

    select sys_connect_by_path(ename,',') concat, deptno, rn     from seqemp o
    connect by rn = prior rn + 1 and deptno = prior deptno     start with rn in (select low from startfinish s

                        where s.deptno =  o.deptno)

) select concat, deptno from paths p
where rn in (select high from startfinish s

                     where s.deptno = p.deptno);

CONCAT

---------------------------------------    DEPTNO

---

,CLARK,KING,MILLER
        10

,SMITH,ADAMS,FORD,SCOTT,JONES
        20

,ALLEN,BLAKE,MARTIN,JAMES,TURNER,WARD
        30

I'm suspecting "recursive with" systax would allow to program user defined agregates much more elegantly. Received on Fri Jun 27 2003 - 18:57:36 CEST