Re: leaf nodes from a tree choice

From: <fitzjarrell_at_cox.net>
Date: Tue, 22 Jan 2008 13:30:14 -0800 (PST)
Message-ID: <7ac27a88-b196-4767-bced-40d684dc597d@e23g2000prf.googlegroups.com>


Comments embedded.
On Jan 14, 6:44 pm, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
> hi all,
> i need to produce a report which shows the sum last down level
> employees and only those

Can you repeat that in English, please? I'm having a difficult time decoding that statement.

> i ve done an attempt which i am posting, but it is giving me weird
> results(my fault), it is giving me roots also as wel as nodes,

Define a 'root' and a 'node' so we know what we're looking for.

> something is missed from my querie but i dont know what should it be,
> to tell sql to go to the deepest level and get me the guy from there;

So you want employees at the 'end of the line', so to speak. A connect by query could provide that:

SQL> select empno, ename, level
  2 from emp
  3 connect by mgr = prior empno
  4 start with mgr is null
  5 /

     EMPNO ENAME LEVEL
---------- ---------- ----------

      7839 KING                1
      7566 JONES               2
      7788 SCOTT               3
      7876 ADAMS               4
      7902 FORD                3
      7369 SMITH               4
      7698 BLAKE               2
      7499 ALLEN               3
      7521 WARD                3
      7654 MARTIN              3
      7844 TURNER              3

     EMPNO ENAME           LEVEL
---------- ---------- ----------
      7900 JAMES               3
      7782 CLARK               2
      7934 MILLER              3

14 rows selected.

Notice the level is 4 for the lowest employee in a chain, and there are two of those employees, ADAMS and SMITH. Calculating a sum for only their salaries is then fairly straightforward:

SQL> with heir as (
  2 select empno, ename, level lvl, sal   3 from emp
  4 connect by mgr = prior empno
  5 start with mgr is null
  6 )
  7 select sum(heir.sal)
  8 from heir
  9 where lvl = (select max(lvl) from heir);

SUM(HEIR.SAL)


         1900

No 'root' nodes, no intermediate nodes, just the only two level 4 nodes in the table. Of course for some managers a level 3 node is the end of the line, but two of those level 3 people (SCOTT, FORD) have a person reporting to them. Let's see if we can get a proper 'end of the line' salary sum for this data set:

SQL> with heir as (
  2 select empno, ename, level lvl, sal, mgr   3 from emp
  4 connect by mgr = prior empno
  5 start with mgr is null
  6 ),
  7 mx as (
  8 select max(lvl) mlvl from heir
  9 )
 10 select sum(heir.sal)
 11 from heir, mx
 12 where lvl = mx.mlvl
 13 or lvl = mx.mlvl - 1
 14 and empno not in (select mgr from heir where lvl = mx.mlvl);

SUM(HEIR.SAL)


         9750

SQL> Generalizing, you apparently want to sum the salaries of all employees at the max(level) unless an employee at a level one higher is also at the his/her lowest level. Yes, I know it sounds confusing; eventually, though, that's what was written here, a connect by query to return the employee information and the level in the 'tree' for that employee. Leaf nodes generally have the highest numbered level (in this case it would be the max() for all levels [4]), but there are some level 3 employees at the end of their chain of command so they should be included. We eliminated the two level 3 employees who are the managers of our level 4 people, and then computed the sum. To verify this we should have summed the salaries of 8 employees of the 14:

SQL> with heir as (
  2 select empno, ename, level lvl, sal, mgr   3 from emp
  4 connect by mgr = prior empno
  5 start with mgr is null
  6 ),
  7 mx as (
  8 select max(lvl) mlvl from heir
  9 )
 10 select count(*)
 11 from heir, mx
 12 where lvl = mx.mlvl
 13 or lvl = mx.mlvl - 1
 14 and empno not in (select mgr from heir where lvl = mx.mlvl);

  COUNT(*)


         8

SQL> We, indeed, have 8 employee records retrieved, thus 8 salaries in our sum.

>
> select "T_P_C", substr(lpad(' ',length(substr("T_P_C",1,12))-5)||m,
> 1,15) "Sum_$" from
> (select sum((assgn_md*job_chg_day)+task_ovhd) m,
>  task_prj_code as "T_P_C"
>  from job, assgn, task
>  where assgn.job_code = job.job_code
>  and assgn.task_id= task.task_id
>  group by task_prj_code
>  order by "T_P_C")
>
> * to me it seems something is missing from the where cluse, but i dont
> know the concept well,
> would anyone please help

It may not be possible with this data, but I believe you need, as explained above, a connect by query to identify and isolate your 'leaf' node employees.

David Fitzjarrell Received on Tue Jan 22 2008 - 15:30:14 CST

Original text of this message