Re: hierarchical query

From: Stephan Born <stephan.born_at_beusen.de>
Date: Wed, 27 Oct 1999 10:24:40 +0200
Message-ID: <3816B6C8.59B7D650@beusen.de>

"Rajkumar Manickavasagam (Raj)" schrieb:

> hi
>
> how does one perform the hierarchical query using connect prior, etc in
> regular SQL in PL/SQL?
>
> regards,
> raj

assuming the following table

table a
(

    id                number;
    parentid      number;

)

where parentid references id from the same table.

select level, a.* -- you do not need pseudo-column level from a
start with id = <start_id>
connect by prior id = parentid

Attention:

There must not be cycles in the hierarchie!
an optional WHERE-clause is performed AFTER the CONNECT BY-clause!
It is performed a 'depth search' (look at column LEVEL). If you want to perform a 'width search' you have to append an ORDER BY LEVEL clause.

In PL/SQL you have to use Cursors if you want to fetch more than one row from a statement

declare

Cursor CurHierachy is

        select level, a.* -- you do not need pseudo-column level
        from a
        start with id = <start_id>
        connect by prior id = parentid;

        Val    number;

begin

for RecHierachy in CurHierarchy loop

        Val := RecHierachy.id;
        .....

end loop;
end;

Hope this will help you, let me know.

Regards, Stephan
--

Dipl.-Inf. (FH) Stephan Born   | beusen Consulting GmbH
fon: +49 30 549932-17          | Landsberger Allee 392
fax: +49 30 549932-29          | 12681 Berlin
mailto:stephan.born_at_beusen.de  | Germany
---------------------------------------------------------------

Received on Wed Oct 27 1999 - 03:24:40 CDT