Home » SQL & PL/SQL » SQL & PL/SQL » Need help on hirerchy grouping
Need help on hirerchy grouping [message #262406] Sun, 26 August 2007 23:53 Go to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
I have two tables. One of the table contains the child gl head and their amount.
And another table containd glhead hierarchy. e.g.

create tab1 (head varchar2( 8 ),parent_head varchar2( 8 ));

insert into tab1(head,parent_head) values
('10000000','10000000');

insert into tab1(head,parent_head) values
('11000000','10000000');

insert into tab1(head,parent_head) values
('11010000','11000000');

insert into tab1(head,parent_head) values
('11020000','11000000');

insert into tab1(head,parent_head) values
('11010100','11010000'); -- This has no Child head

insert into tab1(head,parent_head) values
('11010200','11010000'); -- This has no Child head

insert into tab1(head,parent_head) values
('11020100','11020000'); -- This has no Child head

insert into tab1(head,parent_head) values
('11020200','12010000'); -- This has no Child head

create tab2 (head varchar2( 8 ),amount number(8,2));

insert into tab2(head,amount) values
('11010100',150);

insert into tab2(head,amount) values
('11010200',100);

insert into tab2(head,amount) values
('11020100',300);

insert into tab2(head,amount) values
('11020200',500);

In Table tab2, there exists only those head whose has no child head.

I need the following output

head Amount
10000000 1050 -- this is the sum of 11000000 as this is the parent of that head
11000000 1050 -- this is the sum of 11010000 and 11020000, as this is the parent of those 2 heads
11010000 250 -- this is the sum of 11010100 and 11010200, as this is the parent of those 2 heads
11010100 150
11010200 100
11020000 800 -- this is the sum of 11020100 and 11020200, as this is the parent of those 2 heads
11020100 300
11020200 500
Re: Need help on hirerchy grouping [message #262408 is a reply to message #262406] Mon, 27 August 2007 00:00 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Search this site and specially inside this PL/SQL group. You will get your answer.
Re: Need help on hirerchy grouping [message #262456 is a reply to message #262406] Mon, 27 August 2007 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will help you:
SQL> SELECT LPAD(' ', 2 * (LEVEL-1)) || ename empname, sal, 
  2         ( SELECT SUM(sal)
  3           FROM emp e2
  4           CONNECT BY PRIOR empno = mgr
  5           START WITH e2.mgr = e1.empno ) sum_sal
  6  FROM emp e1
  7  CONNECT BY PRIOR empno = mgr
  8  START WITH mgr IS NULL
  9  ORDER SIBLINGS BY ename
 10  /
EMPNAME                     SAL    SUM_SAL
-------------------- ---------- ----------
KING                       5000      24025
  BLAKE                    2850       6550
    ALLEN                  1600
    JAMES                   950
    MARTIN                 1250
    TURNER                 1500
    WARD                   1250
  CLARK                    2450       1300
    MILLER                 1300
  JONES                    2975       7900
    FORD                   3000        800
      SMITH                 800
    SCOTT                  3000       1100
      ADAMS                1100

14 rows selected.

Regards
Michel
Re: Need help on hirerchy grouping [message #263281 is a reply to message #262406] Wed, 29 August 2007 10:32 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
Thanks michel

That query help me
Re: Need help on hirerchy grouping [message #263297 is a reply to message #263281] Wed, 29 August 2007 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you found a solution, please post it for other readers.

Regards
Michel
Re: Need help on hirerchy grouping [message #264249 is a reply to message #262406] Sun, 02 September 2007 06:25 Go to previous message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
/* Formatted on 2007/09/02 17:28 (Formatter Plus v4.8.0) */
WITH DATA AS
     (SELECT q.head, q.parent_head, NVL (p.amount, 0) amount
        FROM (SELECT   head, SUM (amount) amount
                  FROM tab2
              GROUP BY head) p, tab1 q
       WHERE q.head = p.head(+))
SELECT DISTINCT head empname,
                NVL ((SELECT     SUM (amount)
                            FROM DATA e2
                      CONNECT BY PRIOR head = parent_head
                      START WITH e2.parent_head = e1.head), amount) sum_amt
           FROM DATA e1
     CONNECT BY PRIOR head = parent_head
     START WITH head != parent_head
       ORDER SIBLINGS BY head
Previous Topic: Change into capital letters?
Next Topic: How to Get Such resultset by count(),rollup .etc....?
Goto Forum:
  


Current Time: Sat Dec 03 01:05:07 CST 2016

Total time taken to generate the page: 0.11697 seconds