Re: Nested sort, trying again

Vadim Tropashko wrote:
> Bob Stearns wrote:

[...]
>
> You can generate the paths from nested sets as follows:
>
> create table input (
> left integer,
> right integer,
> name varchar2(10)
> );
>
> insert into input
> select 1 left, 10 right, 'A' name from dual
> union
> select 2, 3, 'C' from dual
> union
> select 4, 5, 'B' from dual
> ;
>
> SELECT ii.left
> ,CONCAT_LIST(CAST( COLLECT('.'||i.name) AS strings )) path
> FROM input i, input ii
> where ii.left between i.left and i.right
> group by ii.left;
>
>
> LEFT PATH
> 1 .A
> 2 .A.C
> 4 .A.B

Unfortunately, your example is broken because you rely on 'group by' supplying rows for concatenation in the correct order. It's not a correct assumption as the following experiment shows (the m.p. is reversed):

--
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE or replace TYPE strings AS TABLE OF VARCHAR2(100);
  2  /

Type created.

SQL>
SQL>
SQL> CREATE or replace Function CONCAT_LIST ( lst IN strings )
  2    RETURN  VARCHAR2 AS
  3      ret   varchar2(1000);
  4  BEGIN
  5      FOR j IN 1..lst.LAST  LOOP
  6          ret := ret || lst(j);
  7      END LOOP;
  8      RETURN ret;
  9  END;
 10  /

Function created.

SQL>
SQL>
SQL> create table input (
  2     left integer,
  3     right integer,
  4     name varchar2(10)
  5  );

Table created.

SQL>
SQL>
SQL> insert into input
  2  select 1 left, 10 right, 'A' name from dual
  3  union
  4  select 2, 3, 'C' from dual
  5  union
  6  select 4, 5, 'B' from dual
  7  ;

3 rows created.

SQL>
SQL>
SQL> SELECT ii.left
  2         ,CONCAT_LIST(CAST( COLLECT('.'||i.name) AS strings )) path
  3  FROM   input i, input ii
  4  where ii.left between i.left and i.right
  5  group by ii.left;

LEFT PATH

 4   .B.A
 2   .C.A
 1   .A

SQL>
--


Additionaly,  if you have a parallel query, rows can arrive intermixed
from several parallel execution threads producing a meaningless
concatenation instead of the expected m.p.