hi all,
i need to produce a report which shows the sum last down level
employees and only those
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,
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;

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

Comments embedded.
On Jan 14, 6:44*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
Can you repeat that in English, please? I'm having a difficult time
decoding that statement.

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

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.

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

Comments embedded.
On Jan 14, 6:44*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
Can you repeat that in English, please? I'm having a difficult time
decoding that statement.

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

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.

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

Comments embedded.
On Jan 14, 6:44*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
Can you repeat that in English, please? I'm having a difficult time
decoding that statement.

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

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.

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

Comments embedded.
On Jan 14, 6:44*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
Can you repeat that in English, please? I'm having a difficult time
decoding that statement.

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

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.

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