-
How to get all the parents of a given node from a tree in single row?
HI,
I have a requirement to get the all the parent nodes till the root, of all the leaf nodes in a Tree structure in a single row instead of multiple rows.
Normally we can get the tree structure as follows for a leaf level node_id : 1023.
select node_id,node_name,node_level,parent_node_id
from tree
start with node_id=1023
connect by prior parent_node_id=node_id
This query returns all the parents of the leaf nodes, but in multiple rows as follows
1023, node_district, 4, 1022
1022, node_region, 3, 1021
1021, node_state, 2, 1020
1020, node_company, 1, 0
but my requirement is to get the node_names as follows in a single row.
node_company, node_state, node_region, node_district
How should I achieve this with a SQL query?
Thank you,
Srinivas Medukonduru
-
What Oracle version? (it is critical here)
-
How to get all the parents of a given node from a tree in single row?
I am using Oracle 8.1.7.2.
-
With 8.1.7, the only option would be write a user-defined function, I guess. 9i has a ready function for it though - sys_connect_by_path.
Cheers!
OraKid.
-
Go to http://revealnet.com/pipelines/plsql/archives.htm read 'Using Order By with Connect By' and get hierarchy package. Then do something like this...
Code:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
SQL> CREATE OR REPLACE PACKAGE hierarchy
2 IS
3 TYPE branchtabletype IS TABLE OF VARCHAR2 (4000)
4 INDEX BY BINARY_INTEGER;
5
6 branchtable branchtabletype;
7
8 FUNCTION branch (
9 vlevel IN NUMBER,
10 vvalue IN VARCHAR2,
11 vdelimiter IN VARCHAR2 DEFAULT CHR (0))
12 RETURN VARCHAR2;
13
14 END hierarchy;
15 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY hierarchy
2 IS
3 returnvalue VARCHAR2 (4000);
4
5 FUNCTION branch (
6 vlevel IN NUMBER,
7 vvalue IN VARCHAR2,
8 vdelimiter IN VARCHAR2 DEFAULT CHR (0))
9 RETURN VARCHAR2
10 IS
11 BEGIN
12 branchtable (vlevel) := vvalue;
13 returnvalue := vvalue;
14
15 FOR i IN REVERSE 1 .. vlevel - 1 LOOP
16 returnvalue := branchtable (i) || vdelimiter || returnvalue;
17 END LOOP;
18
19 RETURN returnvalue;
20 END branch;
21
22 END hierarchy;
23 /
Package body created.
SQL> SELECT path
2 FROM (SELECT path, LEAD (path) OVER (
3 ORDER BY ROWNUM) next_path
4 FROM (SELECT ROWNUM row_num,
5 hierarchy.branch (
6 LEVEL, ename, ', ') path
7 FROM emp
8 START WITH mgr IS NULL
9 CONNECT BY PRIOR empno = mgr))
10 WHERE next_path NOT LIKE path || '%';
PATH
--------------------------------------------------------------------------------
KING, JONES, SCOTT, ADAMS
KING, JONES, FORD, SMITH
KING, BLAKE, ALLEN
KING, BLAKE, WARD
KING, BLAKE, MARTIN
KING, BLAKE, TURNER
KING, BLAKE, JAMES
7 rows selected.
SQL>
-
Re: How to get all the parents of a given node from a tree in single row?
Code:
SELECT MAX(node_company),
MAX(node_state),
MAX(node_region),
MAX(node_district)
FROM
(SELECT DECODE(node_level,1,node_name) node_company,
DECODE(node_level,2,node_name) node_state,
DECODE(node_level,3,node_name) node_region,
DECODE(node_level,4,node_name) node_district
FROM
(select node_id,node_name,node_level,parent_node_id
from tree
start with node_id=1023
connect by prior parent_node_id=node_id
)
);
The assumption here is that each leaf node you are making the report for is at exactly fourth level, ie, it allways has three parent levels.
Last edited by jmodic; 09-14-2004 at 11:42 AM.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Hi jmodic,
Your query is working fine for one leaf node, but if I give more than one leaf node in the query, its loading parents information of only one leaf.
I have tried as follows...
Code:
SELECT MAX(node_company),
MAX(node_state),
MAX(node_region),
MAX(node_district)
FROM
(SELECT DECODE(node_level,1,node_name) node_company,
DECODE(node_level,2,node_name) node_state,
DECODE(node_level,3,node_name) node_region,
DECODE(node_level,4,node_name) node_district
FROM
(select node_id,node_name,node_level,parent_node_id
from tree
start with node_id in (1023,1080,1040)
connect by prior parent_node_id=node_id
)
);
How we can achive it for multiple leaf nodes ?
Thanks in advance
Regards,
Srinivas Medukonduru
-
Following on from BOTH examples...
Code:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
SQL> CREATE TABLE tree (
2 node_id NUMBER, node_name VARCHAR2 (30),
3 node_level NUMBER, parent_node_id NUMBER);
Table created.
SQL>
SQL> INSERT INTO tree VALUES (1023, 'node_district_a', 4, 1022);
1 row created.
SQL> INSERT INTO tree VALUES (1022, 'node_region_a', 3, 1021);
1 row created.
SQL> INSERT INTO tree VALUES (1021, 'node_state_a', 2, 1020);
1 row created.
SQL> INSERT INTO tree VALUES (1020, 'node_company_a', 1, 0);
1 row created.
SQL> INSERT INTO tree VALUES (1080, 'node_district_b', 4, 1079);
1 row created.
SQL> INSERT INTO tree VALUES (1079, 'node_region_b', 3, 1078);
1 row created.
SQL> INSERT INTO tree VALUES (1078, 'node_state_b', 2, 1077);
1 row created.
SQL> INSERT INTO tree VALUES (1077, 'node_company_b', 1, 0);
1 row created.
SQL> SELECT MAX (company) company, MAX (state) state,
2 MAX (region) region, MAX (district) district
3 FROM (SELECT b.*, SUM (DECODE (lvl, lag_lvl + 1, 0, 1))
4 OVER (ORDER BY row_num) grp
5 FROM (SELECT DECODE (node_level, 1, node_name) company,
6 DECODE (node_level, 2, node_name) state,
7 DECODE (node_level, 3, node_name) region,
8 DECODE (node_level, 4, node_name) district,
9 row_num, lvl, LAG (lvl) OVER (ORDER BY row_num) lag_lvl
10 FROM (SELECT node_id, node_name, node_level,
11 parent_node_id, ROWNUM row_num, LEVEL lvl
12 FROM tree
13 START WITH node_id IN (1023, 1080)
14 CONNECT BY PRIOR parent_node_id = node_id)) b)
15 GROUP BY grp;
COMPANY STATE REGION DISTRICT
--------------- --------------- --------------- ---------------
node_company_a node_state_a node_region_a node_district_a
node_company_b node_state_b node_region_b node_district_b
SQL> SELECT path
2 FROM (SELECT path, LEAD (path) OVER (
3 ORDER BY ROWNUM) next_path
4 FROM (SELECT ROWNUM row_num,
5 hierarchy.branch (
6 LEVEL, node_name, ', ') path
7 FROM tree
8 START WITH node_id IN (1023, 1080)
9 CONNECT BY PRIOR parent_node_id = node_id))
10 WHERE next_path NOT LIKE path || '%' OR next_path IS NULL;
PATH
----------------------------------------------------------------------------------------------------
node_district_a, node_region_a, node_state_a, node_company_a
node_district_b, node_region_b, node_state_b, node_company_b
SQL>
-
Originally posted by srinivasm
How we can achive it for multiple leaf nodes ?
Look at padders's suggestions. In case for some reason you can't (or won't) use analytical functions, you can still use my method, only slightly modified:
Code:
SELECT MAX(node_company),
MAX(node_state),
MAX(node_region),
MAX(node_district)
FROM
(SELECT DECODE(node_level,1,node_name) node_company,
DECODE(node_level,2,node_name) node_state,
DECODE(node_level,3,node_name) node_region,
DECODE(node_level,4,node_name) node_district,
CEIL(ROWNUM/4) bucket
FROM
(SELECT node_id,node_name,node_level,parent_node_id
FROM tree
START WITH node_id IN (1023,1080)
CONNECT BY PRIOR parent_node_id=node_id
)
)
GROUP BY bucket;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
How we can achive it for multiple nodes without using the analytical functions?
Hi JModic,
Thank you for your query.
When I try the padder's query(which has the analytical functions),
the performance is very slow for large number of nodes(almost 1967 nodes) in the "in clause".
Then I have tried your query without analytical functions.
But I have one problem your query, that the nodes in the "IN clause" are not always level 4 nodes,
those may be any level from 4 to 1.
If the node in the "IN clause" is 4 then it returns 4,3,2,1.
If the node in the "IN clause" is 3 then it returns 3,2,1.
If the node in the "IN clause" is 2 then it returns 2,1.
If the node in the "IN clause" is 1 then it returns 1.
For the above scenario's CEIL(ROWNUM/4) doesn't work to group the results.
How we can acheive this without using the analytical functions?
Thanks in advance.
regards,
Srinivas Medukonduru
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|