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 |
|
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 #612139 is a reply to message #612134] |
Sun, 13 April 2014 00:53 |
|
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 #612233 is a reply to message #612131] |
Tue, 15 April 2014 02:01 |
|
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 |
|
Michel Cadot
Messages: 68625 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 |
|
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 |
|
Michel Cadot
Messages: 68625 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
|
|
|
|
Goto Forum:
Current Time: Fri Mar 29 01:17:58 CDT 2024
|