Re: Connect By query question

I figured it out with the help of:
http://www.arsdigita.com/books/sql/tress.html

The answer is Create an Index and use it:

create index i$category$hint on category(UPPER(display), cat_id, parent_cat_id)

select /*+ INDEX(category i$category$hint) */

        LPAD(' ', 2*(level-1), '-') || display CategoryDisplay, cat_id    from category
   start with cat_id = 50000009
   connect by prior cat_id = parent_cat_i

This works on the Enterprise Edition only as Function based indexes are not on the Standard Edition.
(Use "select * from v$version" to find out)

"Sean Dolan" <nospam_at_nowhere.com> wrote in message news:9e0u2h02n2v_at_enews2.newsguy.com...
> Oracle 8.1.7:
>
> My current query and results:
> select LPAD(' ', 2*(level-1), '-') || display CategoryDisplay, cat_id
> from category
> start with cat_id = 50000095
> connect by prior cat_id = parent_cat_id;
>
> Results:
> ---------------------------------
> SALES DEPT 50000095
> - Inside Sales 50000117
> --- Product 50000119
> ----- monitors 50000503
> ----- printers 50000502
> ----- computers 50000504
> ----- Keyboards 50000505
> --- Service 50000120
> - Outside Sales 50000118
> --- Product 50000121
> --- Service 50000122
> - Computer sales 50000461
>
> GOAL : I am trying to alphabetize each level of the tree so that it would
> come out looking like:
>
> SALES DEPT 50000095
> - Computer sales 50000461
> - Inside Sales 50000117
> --- Product 50000119
> ----- computers 50000504
> ----- Keyboards 50000505
> ----- monitors 50000503
> ----- printers 50000502
> --- Service 50000120
> - Outside Sales 50000118
> --- Product 50000121
> --- Service 50000122
>
>
> Question : How do you order just the level within the tree ????
>
> Thanks,
> Sean Dolan
>
>
>
Received on Thu May 17 2001 - 12:29:37 CDT