Home » SQL & PL/SQL » SQL & PL/SQL » how to remove columns not ending with leaf nodes in hierarchical queries (11g,toad 12.1)
how to remove columns not ending with leaf nodes in hierarchical queries [message #612131] Sat, 12 April 2014 11:45 Go to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
how can i remove columns not ending with leaf nodes in hierarchical queries

what i have done so far


CREATE OR REPLACE PROCEDURE FRONTROOM.SP_CUST_HIER_DENORM_2

IS 
L_ERR_MSG VARCHAR2(1000);
l_cust_id number(10);
v_cnt number(1);
BEGIN
SP_LOG_ENTRY(CURRENT_TIMESTAMP,'SP_CUST_HIER_DENORM_2','START',NULL,NULL,NULL);

-- select cust_id into l_cust_id from customer where true_gcdb_source_key ='9900000001';

    
    delete CUST_HIER_DNORM_2;
    
    for v_cnt in 2..6
    loop

                  
   -- v_cnt := 6;                
     For j in (  SELECT cust_id AS child_id
           FROM customer
          WHERE LEVEL = v_cnt
          START WITH cust_id = 12660
         CONNECT BY PRIOR cust_id = cust_cust_id)

     LOOP
     begin
     
            INSERT INTO CUST_HIER_DNORM_2
                (LVL_1,LVL_2,LVL_3,LVL_4,LVL_5,LVL_6,LVL_7,LVL_8 )
               SELECT SUM(DECODE(LEVEL,v_cnt,cust_id))  LVL_1 ,
                      SUM(DECODE(LEVEL,v_cnt-1,cust_id))  LVL_2 ,
                      SUM(DECODE(LEVEL,v_cnt-2,cust_id))  LVL_3 ,
                      SUM(DECODE(LEVEL,v_cnt-3,cust_id))  LVL_4 ,
                      SUM(DECODE(LEVEL,v_cnt-4,cust_id))  LVL_5 ,
                      SUM(DECODE(LEVEL,v_cnt-5,cust_id))  LVL_6 ,
                      SUM(DECODE(LEVEL,v_cnt-6,cust_id))  LVL_7 ,
                      SUM(DECODE(LEVEL,v_cnt-7,cust_id))  LVL_8 
              FROM CUSTOMER 
              WHERE LEVEL <=v_cnt
              START WITH cust_id = j.child_id  
              CONNECT BY  cust_id = PRIOR cust_cust_id ;
        EXCEPTION
        WHEN OTHERS
        THEN
        SP_LOG_ENTRY (CURRENT_TIMESTAMP,
               'SP_CUST_HIER_DENORM_2',
               'ERROR WHILE INSERTING ',
               SQLERRM,
               '* ' || j.CHILD_ID  || ' *',NULL

            );
       END;
   END LOOP ;
  end loop;
   
   COMMIT;
   
   

UPDATE cust_hier_dnorm_2 cd
   SET LVL_1_DESC =
          (SELECT c.true_gcdb_source_key
             FROM customer c
            WHERE cd.lvl_1 = C.CUST_ID ),
       LVL_2_DESC =
          (SELECT c.true_gcdb_source_key
             FROM customer c
            WHERE cd.lvl_2 = C.CUST_ID ),
       LVL_3_DESC =
          (SELECT c.true_gcdb_source_key
             FROM customer c
            WHERE cd.lvl_3 = C.CUST_ID ),
       LVL_4_DESC =
          (SELECT c.true_gcdb_source_key
             FROM customer c
            WHERE cd.lvl_4 = C.CUST_ID ),
       LVL_5_DESC =
          (SELECT c.true_gcdb_source_key
             FROM customer c
            WHERE cd.lvl_5 = C.CUST_ID ),
       LVL_6_DESC =
          (SELECT c.true_gcdb_source_key
             FROM customer c
            WHERE cd.lvl_6 = C.CUST_ID  ),
       LVL_7_DESC =
          (SELECT c.true_gcdb_source_key
             FROM customer c
            WHERE cd.lvl_7 = C.CUST_ID ),
       LVL_8_DESC =
          (SELECT c.true_gcdb_source_key
             FROM customer c
            WHERE cd.lvl_8 = C.CUST_ID );
 
commit;
   
   
   SP_LOG_ENTRY(CURRENT_TIMESTAMP,'SP_CUST_HIER_DENORM_2','START',NULL,NULL,NULL);
   
   EXCEPTION
   WHEN OTHERS THEN
   L_ERR_MSG :=SQLERRM;
   SP_LOG_ENTRY(CURRENT_TIMESTAMP,'SP_LOAD_CUST_HIER_DENORM_2',NULL,L_ERR_MSG,NULL,NULL);
   END;
/

exec SP_CUST_HIER_DENORM_2();
Re: how to remove columns not ending with leaf nodes in hierarchical queries [message #612132 is a reply to message #612131] Sat, 12 April 2014 11:49 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
lvl 1 lvl2 lvl3 lvl4 lvl5 lvl6
12660 12389 12517 12476 12557 3953(leaf node)
12660 12389 12517 12476 12557 3978(leaf node)
12660 12389 12517 12476 12557 3992(leaf node)
12660 12389 12517 12476 12557
12660 12389 12517 12476
12660 12389 12517


desired output
lvl 1 lvl2 lvl3 lvl4 lvl5 lvl6
12660 12389 12517 12476 12557 3953(leaf node)
12660 12389 12517 12476 12557 3978(leaf node)
12660 12389 12517 12476 12557 3992(leaf node)

[Updated on: Sat, 12 April 2014 11:59]

Report message to a moderator

Re: how to remove columns not ending with leaf nodes in hierarchical queries [message #612134 is a reply to message #612132] Sat, 12 April 2014 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 23189
Registered: January 2009
Senior Member
Since we don't have your tables or data, we can not run, test, debug or improve posted code
Re: how to remove columns not ending with leaf nodes in hierarchical queries [message #612139 is a reply to message #612134] Sun, 13 April 2014 00:53 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
create table cust_hier_dnorm_2
(
lvl_1 number(20),
lvl_2 number(20),
lvl_3 number(20),
lvl_4 number(20),
lvl_5 number(20),
lvl_6 number(20),
lvl_7 number(20),
lvl_8 number(20),
lvl_1_desc varchar(50),
lvl_2_desc varchar(50),
lvl_3_desc varchar(50),
lvl_4_desc varchar(50),
lvl_5_desc varchar(50),
lvl_6_desc varchar(50),
lvl_7_desc varchar(50),
lvl_8_desc varchar(50)
)
u can ignore the desc part

the data shown above was extracted from the above table only
Re: how to remove columns not ending with leaf nodes in hierarchical queries [message #612140 is a reply to message #612139] Sun, 13 April 2014 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

We still have not your data.

Re: how to remove columns not ending with leaf nodes in hierarchical queries [message #612233 is a reply to message #612131] Tue, 15 April 2014 02:01 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
  select * from cust_hier_dnorm_2 where lvl_6=3953
         union
         select * from cust_hier_dnorm_2 where lvl_5=12557
         union
         select * from cust_hier_dnorm_2 where lvl_4=12476

data

LVL_1	LVL_2	LVL_3	LVL_4	LVL_5	LVL_6	LVL_7	lvl_8
12389	12517	12476	12557	3953			
12389	12517	12476	12557	3978			
12389	12517	12476	12557	3992			
12389	12517	12476	12557				
12389	12517	12476					


desired result

12389	12517	12476	12557	3953			
12389	12517	12476	12557	3978			
12389	12517	12476	12557	3992	

which means removing the columns not ending with leaf nodes.
how bout using connect_by_isleaf

[Updated on: Tue, 15 April 2014 02:19]

Report message to a moderator

Re: how to remove columns not ending with leaf nodes in hierarchical queries [message #612234 is a reply to message #612233] Tue, 15 April 2014 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sun, 13 April 2014 07:56

We still have not your data.



SQL> create table cust_hier_dnorm_2
  2  (
  3  lvl_1 number(20),
  4  lvl_2 number(20),
  5  lvl_3 number(20),
  6  lvl_4 number(20),
  7  lvl_5 number(20),
  8  lvl_6 number(20),
  9  lvl_7 number(20),
 10  lvl_8 number(20),
 11  lvl_1_desc varchar(50),
 12  lvl_2_desc varchar(50),
 13  lvl_3_desc varchar(50),
 14  lvl_4_desc varchar(50),
 15  lvl_5_desc varchar(50),
 16  lvl_6_desc varchar(50),
 17  lvl_7_desc varchar(50),
 18  lvl_8_desc varchar(50)
 19  )
 20  /

Table created.

SQL> select * from cust_hier_dnorm_2 where lvl_6=3953
  2           union
  3           select * from cust_hier_dnorm_2 where lvl_5=12557
  4           union
  5           select * from cust_hier_dnorm_2 where lvl_4=12476
  6  /

no rows selected


Do you understand now?

Re: how to remove columns not ending with leaf nodes in hierarchical queries [message #612235 is a reply to message #612234] Tue, 15 April 2014 02:27 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
the above data shown with levels was pulled from cust_hier_dnorm_2 table


          SELECT  SUM(DECODE(LEVEL,6,cust_id))    LVL_1 , 
                      SUM(DECODE(LEVEL,6-1,cust_id))  LVL_2 ,
                      SUM(DECODE(LEVEL,6-2,cust_id))  LVL_3 ,
                      SUM(DECODE(LEVEL,6-3,cust_id))  LVL_4 ,
                      SUM(DECODE(LEVEL,6-4,cust_id))  LVL_5 ,
                      SUM(DECODE(LEVEL,6-5,cust_id))  LVL_6 ,
                      SUM(DECODE(LEVEL,6-6,cust_id))  LVL_7 ,
                      SUM(DECODE(LEVEL,6-7,cust_id))  LVL_8 
              FROM CUSTOMER 
              WHERE LEVEL <=6 --and
             -- connect_by_isleaf = 0
             -- and
              START WITH cust_id = 3953  
              CONNECT BY  cust_id = PRIOR cust_cust_id;
              
lvl1     lvl2    lvl3    lvl4    lvl5    lvl6
12660	12389	12517	12476	12557	3953
Re: how to remove columns not ending with leaf nodes in hierarchical queries [message #612236 is a reply to message #612235] Tue, 15 April 2014 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 60063
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> SELECT  SUM(DECODE(LEVEL,6,cust_id))    LVL_1 ,
  2                        SUM(DECODE(LEVEL,6-1,cust_id))  LVL_2 ,
  3                        SUM(DECODE(LEVEL,6-2,cust_id))  LVL_3 ,
  4                        SUM(DECODE(LEVEL,6-3,cust_id))  LVL_4 ,
  5                        SUM(DECODE(LEVEL,6-4,cust_id))  LVL_5 ,
  6                        SUM(DECODE(LEVEL,6-5,cust_id))  LVL_6 ,
  7                        SUM(DECODE(LEVEL,6-6,cust_id))  LVL_7 ,
  8                        SUM(DECODE(LEVEL,6-7,cust_id))  LVL_8
  9                FROM CUSTOMER
 10                WHERE LEVEL <=6 --and
 11               -- connect_by_isleaf = 0
 12               -- and
 13                START WITH cust_id = 3953
 14                CONNECT BY  cust_id = PRIOR cust_cust_id;
              FROM CUSTOMER
                   *
ERROR at line 9:
ORA-00942: table or view does not exist

Re: how to remove columns not ending with leaf nodes in hierarchical queries [message #612240 is a reply to message #612236] Tue, 15 April 2014 03:37 Go to previous message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
using connect_by_isleaf = 1 solved my problem
Previous Topic: select query
Next Topic: How to check row count of sys_refcursor
Goto Forum:
  


Current Time: Sat Dec 27 19:06:23 CST 2014

Total time taken to generate the page: 0.08152 seconds