Re: Tree Query question

On Mon, 10 Nov 1997 22:11:33 -0500, rsenn <rsenn_at_capaccess.org> wrote:

>I'm trying to work through a tree query in PL*SQL and getting nowhere.
>Can anybody give me a hand with this? E-mail replies are encouraged,
>even if you also reply to the newsgroup.
>
>Thanks.
>
>Sincerely,
>Randall
>
>
>Data look something like this.
>
>Job Parent Spent
>10 NULL 100 -- top level has NULL parent
>20 10 500 -- child
>27 20 700 -- grandchild
>32 27 50 -- great grandchild
>21 20 200 -- a 2nd branch from job 20
>
>There is no limit to the breadth or depth of the tree, although as a
>practical matter I suspect about 10 or 15 levels will be the depth limit
>reached and 20 or 30 will be the practical limit for breadth.
>
>I want to construct a report similar to this, but can't see the coding
>logic to doing it. Can you point me in the right direction?
>
>Job Spent_by_Job_&_Progeny
>10 1550
>20 1450
>21 200
>27 750
>32 50
>

Hi,

the query that will do the trick might look like:

 SELECT job, spent
   FROM your_table
CONNECT BY PRIOR job_id = parent_job_id
  START WITH parent_job_id IS NULL;

You may wish to include the pseudocolumn level in the select list; its value is the current depth in the tree, so you can use it for some formatting. Have a look at the Oracle Server SQL Reference, there are some examples in the chapter 'Hierarchical Queries'.

HTH
Peter

Peter Schneider
pschneider.ctj_at_metronet.de Received on Wed Nov 12 1997 - 00:00:00 CST