Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, uma.

Asked: September 10, 2016 - 7:27 am UTC

Last updated: September 13, 2016 - 2:18 pm UTC

Version: 11G

Viewed 1000+ times

You Asked 

Oracle Version 11G.

I have a requirement to show the data in a Hierarchy manner. It will shown as a TREE structure in the UI. 
I have 3 tables 
    I)  T_MAIN,T_ITEMS_MAIN It's having the Static data.
    II) T_PURCH_ITEMS Having purchased items for each end user.(items selected From T_ITEMS_MAIN).

1) T_MAIN Table 
ID PARENT_ID PRD_NME
--- -------- -------
1  STORE
2 1 BASE
3 1 WORKERS
4 1 PURCHASE
5 4 STATIONARY
6 5 S_PENS
7 5 S_PAPERS
8 4 COLOR_ITM
9 8 COL_ERASERS
10 8 COL_SCALES

2) T_ITEMS_MAIN  Table

ITEM_NO ID ITEM_NME
------ -- --------
1 2 BASE1
2 2 BASE2
3 3 WORKER1
4 3 WORKER2
5 6 S_PEN1
6 6 S_PEN2
7 7 S_PAPER1
8 7 S_PAPER2
9 9 COL_ERASERS1
10 9 COL_ERASERS2
11 10 COL_SCALES1
12 10 COL_SCALES2
 
3) T_PURCH_ITEMS  data which are having customer data

PUR_NME ITEM_NO
------ ------
STEVE 1
STEVE 2
STEVE 4
STEVE 6
STEVE 7
STEVE 10
STEVE 12

<b>I NEED THE OUTPUT IN BELOW MANNER TO MAIN THE TREE STRUCTURE, LET ME KNOW IF ANY OTHER EASY WAY TO MAINTAIN THE TREE STRUCTURE ALSO.

ID PARENT_ID ITEM/PRDT_Nme
---    ---------       -------------
1  STORE
2 1 BASE
1 2    BASE1
2 2    BASE2
3 1 WORKERS
4 3    WORKER2
4 1 PURCHASE
5 4    STATIONARY
6 5      S_PENS
6 6        S_PEN2
7 5      S_PAPERS
7 7        S_PAPER1
8 4 COLOR_ITM
9 8    COL_ERASERS
10 9       COL_ERASERS2
10 8    COL_SCALES
12 10       COL_SCALES2


Find the all the table and data DML and DDL stms.

CREATE TABLE T_MAIN (ID NUMBER,PARENT_ID NUMBER,PRD_NME VARCHAR2(20),CONSTRAINT T_MAIN_PK PRIMARY KEY(ID));

CREATE TABLE T_ITEMS_MAIN (ITEM_NO NUMBER,ID NUMBER,ITEM_NME VARCHAR2(20), CONSTRAINT fk_T_ITMES_MAIN FOREIGN KEY (ID) REFERENCES T_MAIN(ID) );

CREATE TABLE T_PURCH_ITEMS ( PUR_NME VARCHAR2(10),ITEM_NO NUMBER);

INSERT INTO T_MAIN VALUES (1,'','STORE');
INSERT INTO T_MAIN VALUES (2,1,'BASE');
INSERT INTO T_MAIN VALUES (3,1,'WORKERS');
INSERT INTO T_MAIN VALUES (4,1,'PURCHASE');
INSERT INTO T_MAIN VALUES (5,4,'STATIONARY');
INSERT INTO T_MAIN VALUES (6,5,'S_PENS');
INSERT INTO T_MAIN VALUES (7,5,'S_PAPERS');
INSERT INTO T_MAIN VALUES (8,4,'COLOR_ITM');
INSERT INTO T_MAIN VALUES (9,8,'COL_ERASERS');
INSERT INTO T_MAIN VALUES (10,8,'COL_SCALES');
COMMIT;

INSERT INTO T_ITEMS_MAIN VALUES(1,2,'BASE1');
INSERT INTO T_ITEMS_MAIN VALUES(2,2,'BASE2');
INSERT INTO T_ITEMS_MAIN VALUES(3,3,'WORKER1');
INSERT INTO T_ITEMS_MAIN VALUES(4,3,'WORKER2');
INSERT INTO T_ITEMS_MAIN VALUES(5,6,'S_PEN1');
INSERT INTO T_ITEMS_MAIN VALUES(6,6,'S_PEN2');
INSERT INTO T_ITEMS_MAIN VALUES(7,7,'S_PAPER1');
INSERT INTO T_ITEMS_MAIN VALUES(8,7,'S_PAPER2');
INSERT INTO T_ITEMS_MAIN VALUES(9,9,'COL_ERASERS1');
INSERT INTO T_ITEMS_MAIN VALUES(10,9,'COL_ERASERS2');
INSERT INTO T_ITEMS_MAIN VALUES(11,10,'COL_SCALES1');
INSERT INTO T_ITEMS_MAIN VALUES(12,10,'COL_SCALES2');
COMMIT;

INSERT INTO T_PURCH_ITEMS VALUES('STEVE',1);
INSERT INTO T_PURCH_ITEMS VALUES('STEVE',2);
INSERT INTO T_PURCH_ITEMS VALUES('STEVE',4);
INSERT INTO T_PURCH_ITEMS VALUES('STEVE',6);
INSERT INTO T_PURCH_ITEMS VALUES('STEVE',7);
INSERT INTO T_PURCH_ITEMS VALUES('STEVE',10);
INSERT INTO T_PURCH_ITEMS VALUES('STEVE'11);
INSERT INTO T_PURCH_ITEMS VALUES('STEVE',12);
COMMIT;

and Chris said...

Thanks for providing a complete set of create tables + inserts!

The key to getting started with this is to union (all) t_main and t_items_main.

The items should appear under the corresponding main row. t_items_main.id gives this value. So map this to parent_id.

To prevent you joining main rows back to items as children:

- Set item_no to null when selecting from t_main
- Set id to null when selecting from t_items_main

Giving:

select t.id, t.parent_id, t.prd_nme, null item_no 
from   t_main t
union all
select null id, t.id parent_id, t.item_nme prd_nme, t.item_no
from   t_items_main t;

ID  PARENT_ID  PRD_NME       ITEM_NO  
1              STORE                  
2   1          BASE                   
3   1          WORKERS                
4   1          PURCHASE               
5   4          STATIONARY             
6   5          S_PENS                 
7   5          S_PAPERS               
8   4          COLOR_ITM              
9   8          COL_ERASERS            
10  8          COL_SCALES             
    2          BASE1         1        
    2          BASE2         2        
    3          WORKER1       3        
    3          WORKER2       4        
    6          S_PEN1        5        
    6          S_PEN2        6        
    7          S_PAPER1      7        
    7          S_PAPER2      8        
    9          COL_ERASERS1  9        
    9          COL_ERASERS2  10       
    10         COL_SCALES1   11       
    10         COL_SCALES2   12


If you only want to display items a user has purchased, stick a where exists in against t_items_main.

You can then build your tree on the results of this using standard hierarchical queries.

If you're on 11.2 or higher, you can use a recursive with:

with rws as (
  select t.id, t.parent_id, t.prd_nme, null item_no from t_main t
  union all
  select null id, t.id parent_id, t.item_nme prd_nme, t.item_no
  from   t_items_main t
  where  exists (
    select * from t_purch_items p
    where  p.item_no = t.item_no
  )
), tree (id, parent_id, name, item_no, lev) as (
  select id, parent_id, prd_nme, null, 0 lev
  from   rws 
  where  parent_id is null
  union all
  select r.id, r.parent_id, r.prd_nme, r.item_no, t.lev + 1 lev
  from   tree t
  join   rws r
  on     t.id = r.parent_id
)
search depth first by id set order1
  select nvl(id, item_no) id, parent_id, lpad(' ', lev * 2) || name
  from   tree;

ID  PARENT_ID  LPAD('',LEV*2)||NAME  
1              STORE                 
2   1            BASE                
1   2              BASE1             
2   2              BASE2             
3   1            WORKERS             
4   3              WORKER2           
4   1            PURCHASE            
5   4              STATIONARY        
6   5                S_PENS          
6   6                  S_PEN2        
7   5                S_PAPERS        
7   7                  S_PAPER1      
8   4              COLOR_ITM         
9   8                COL_ERASERS     
10  9                  COL_ERASERS2  
10  8                COL_SCALES      
12  10                 COL_SCALES2


If you're not, you can stick with a traditional connect by:

with rws as (
  select t.id, t.parent_id, t.prd_nme, null item_no from t_main t
  union all
  select null id, t.id parent_id, t.item_nme prd_nme, t.item_no
  from   t_items_main t
  where  exists (
    select * from t_purch_items p
    where  p.item_no = t.item_no
  )
)
 select nvl(id, item_no) id, parent_id,
        lpad(' ', (level-1)*2) || prd_nme item_prod_name
 from   rws
 start with parent_id is null
 connect by nocycle prior id = parent_id;

ID  PARENT_ID  ITEM_PROD_NAME        
1              STORE                 
2   1            BASE                
1   2              BASE1             
2   2              BASE2             
3   1            WORKERS             
4   3              WORKER2           
4   1            PURCHASE            
5   4              STATIONARY        
6   5                S_PENS          
6   6                  S_PEN2        
7   5                S_PAPERS        
7   7                  S_PAPER1      
8   4              COLOR_ITM         
9   8                COL_ERASERS     
10  9                  COL_ERASERS2  
10  8                COL_SCALES      
12  10                 COL_SCALES2


Results using test scripts in question as provided.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

uma gupta, September 13, 2016 - 11:46 am UTC

Thanks Chris for the Answer :

as per your query i get all the table data hierarchy. But i need only the specific purchased items (t_purch_items ). And their hierarchy only.

I added few rows to T_MAIN table
INSERT INTO T_MAIN VALUES (11,1,'DATA1');
INSERT INTO T_MAIN VALUES (12,11,'DATA2');
INSERT INTO T_MAIN VALUES (13,11,'DATA3');
INSERT INTO T_MAIN VALUES (14,1,'DATA4');
INSERT INTO T_MAIN VALUES (15,14,'DATA5');
INSERT INTO T_MAIN VALUES (16,15,'DATA6');
INSERT INTO T_MAIN VALUES (17,16,'DATA7');
INSERT INTO T_MAIN VALUES (18,1,'DATA8');
INSERT INTO T_MAIN VALUES (19,16,'DATA9');

But as per my requirement based on t_purch_items Hierarchy needs to build up to their relatives.

That means the heirarchy needs to stop upto Id 10 in t_main table itself and also can i get the counts like rollup,

Counts of child and parents.
Store 8
BASE 0
Contacts 3
COnt1 1
Cont2 1
Cont3 1
Station 5
Paper 3
Paper1 1
Paper2 1
Paper3 1
Pen 2
Pen1 1
Pen2 1


Chris Saxon
September 13, 2016 - 2:18 pm UTC

Here's one way to do it:

Walk the tree twice. First in reverse. Then the correct way round.

When reversing up the tree, only include those items that have been purchased. While doing this note the starting point for each.

This will give you multiple rows per node. e.g STORE will appear once for every item purchased. Collapse each node into one row using group by. At the same time, produce a comma separated list of the the roots using listagg().

Then walk back down the tree. At the same time you can find the number of child nodes by counting the commas in your list of roots and adding one:

with rws as (
  select t.id, t.parent_id, t.prd_nme, null item_no from t_main t
  union all
  select null id, t.id parent_id, t.item_nme prd_nme, t.item_no
  from   t_items_main t
  where  exists (
    select * from t_purch_items p
    where  p.item_no = t.item_no
  )
), rtree as (
  select distinct r.* , connect_by_root prd_nme rt
  from   rws r
  start with item_no in (select item_no from t_purch_items)
  connect by prior parent_id = id
), rtreegrp as (
  select id, parent_id, prd_nme, item_no, 
         listagg(rt, ',') within group (order by rt) l
  from   rtree
  group  by id, parent_id, prd_nme, item_no
)
  select nvl(id, item_no) id, parent_id, 
         lpad(' ', (level-1)*2) || prd_nme item_prod_name, 
         length(l) - length(replace(l, ',')) + 1 ct
  from   rtreegrp
  start with parent_id is null
  connect by parent_id = prior id;

ID  PARENT_ID  ITEM_PROD_NAME        CT  
1              STORE                 7   
2   1            BASE                2   
1   2              BASE1             1   
2   2              BASE2             1   
3   1            WORKERS             1   
4   3              WORKER2           1   
4   1            PURCHASE            4   
5   4              STATIONARY        2   
6   5                S_PENS          1   
6   6                  S_PEN2        1   
7   5                S_PAPERS        1   
7   7                  S_PAPER1      1   
8   4              COLOR_ITM         2   
9   8                COL_ERASERS     1   
10  9                  COL_ERASERS2  1   
10  8                COL_SCALES      1   
12  10                 COL_SCALES2   1


Note - needs 11gR2 for listagg.

Thanks Chris for your time and help.

uma gupta, September 14, 2016 - 4:46 pm UTC

My issue got solved
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.