You should post this in comp.database.theory
"Chris Albanese" <albanes1_at_remove-me.optonline.net> wrote in message
news:HqtG8.91190$ja.26577936_at_news02.optonline.net...
> I've just been thrown into a large project requiring oracle 9i (im very
> familiar with basic SQL commands from using MySQL) but I am trying to
figure
> this one out. Basically, I'm trying to figure out if I can do something
> with a SELECT statement, or if I need to do this with PL/SQL.
>
> I have a tree structure and I can get it to sort and select properly.
>
>
> select lpad(' ', (level - 1) * 2) || section_name,
> section_id,
> parent_id,
> nav_order,
> level
> from sections
> connect by prior section_id = parent_id
> start with section_id in (select section_id
> from sections
> where parent_id is null)
> order SIBLINGS by nav_order;
>
> This produces output like this:
>
> Home
> United States
> New York State
> Westchester County
> Yonkers
> White Plains
> New Rochelle
> Rockland County
> Queens County
> New Jersey State
> Hudson County
> Hoboken
> Jersey City
> Essex County
>
> What I'd like to do is have it only show levels above it if I was in that
> sub-section.
> e.g:
> Home
> United States
> New York State
> New Jersey State
> Hudson County
> Hoboken
> Jersey City
> Essex County
>
> So it would show everything at a certain level, and then everything at a
> certain other level if I told it which item is "bottom-most"..
>
> Is this possible with a connect by, or would I need to use PL/SQL, or even
> do it in the programming language for the website it is for (jsp, though
I'd
> like to avoid this and do it in Oracle if possible)
>
> thanks.
>
>
>
Received on Tue May 21 2002 - 14:33:45 CDT
