Re: Connect by dumps

Hello,

What you want to do is trivial under Oracle 9i:

create table t1(PARENT INT, CHILD INT);

insert into t1 values(1, 2);
insert into t1 values(1, 3);
insert into t1 values(2, 4);
insert into t1 values(2, 5);
insert into t1 values(3, 6);
insert into t1 values(3, 7);
insert into t1 values(4, null);
insert into t1 values(5, 8);
insert into t1 values(5, 9);
insert into t1 values(6, null);
insert into t1 values(7, null);
insert into t1 values(8, null);
insert into t1 values(9, null);

select * from t1
  connect by parent = prior child
  start with parent=1
  order siblings by child desc;

PARENT CHILD
------ ----
1 3
3 7
7 null
3 6
6 null
1 2
2 5
5 9
9 null
5 8
8 null
2 4
4 null

Under 8i, you'll have to write a function that can be used in the order clause.

Rgds.
"foolishHurts" <silverback_at_photobooks.com> wrote in message news:d587rvks328l3frfav7nthonbbmvn0tgj8_at_4ax.com...
> Thanks for a great way to get a hierarchy.
> BUT... Give a mouse a cookie and it wants a glass of milk.
> There is one thing I can not find.
>
> Given data as follows, I would like to control the order of traversal such
> that child 3 is traversed before child 2 (same tree structure, reverse
> order) :
>
> The ability to psudo order within child grouping would allow me to blow
off
> other tools and just do the whole thing in SQLPLUS.
>
> Thanks,
>
> Evan
>
> --- Raw Data
> PARENT CHILD
> ---------- ----------
> 1 2
> 1 3
> 2 4
> 2 5
> 3 6
> 3 7
> 4
> 5 8
> 5 9
> 6
> 7
> 8
> 9
> --- Result of
> SELECT parent, child
> FROM parents
> START WITH parent = 1
> CONNECT BY prior child = parent;
>
> PARENT CHILD
> ---------- ----------
> 1 2
> 2 4
> 4
> 2 5
> 5 8
> 8
> 5 9
> 9
> 1 3
> 3 7
> 7
> 3 6
> 6
>
> Desired result....
> PARENT CHILD
> ---------- ----------
> 1 3
> 3 6
> 6
> 3 7
> 7
> 1 2
> 2 5
> 5 9
> 9
> 5 8
> 8
> 2 4
> 4
>
>
> On Thu, 13 Nov 2003 13:22:18 +0000 (UTC), Lucyna Witkowska
> <ypwitkow_at_nospamcyf-kr.edu.pl> wrote:
>
> >Ray <rbujarski_at_hotmail.com> wrote:
> >
> >> create view treepath
> >> as
> >> SELECT parent, child
> >> FROM td_view_dependants
> >> START WITH parent in (select parent from td_view_dependants)
> >> CONNECT BY child = prior parent
> >
> >> However the results from this are p1 has p2 and p2, p2 has p3.
> >
> >If you want start with p1 just write:
> >START WITH parent = 'p1'
> >
> >If you want to go down the tree:
> >CONNECT BY PRIOR child = parent
> >
> >greetings,
> >LW
>
Received on Thu Nov 13 2003 - 17:53:48 CST