COnnect By Prrior Question

From: Chris Albanese <albanes1_at_remove-me.optonline.net>
Date: Tue, 21 May 2002 15:09:27 GMT
Message-ID: <HqtG8.91190$ja.26577936@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 - 10:09:27 CDT