Menu
I have a table like this:
+---------+--------+
| EMP_ID | MGR_iD |
+---------+--------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 2 |
| 7 | 3 |
| 8 | 5 |
| 9 | 7 |
| 10 | 5 |
| 11 | 7 |
| 12 | 9 |
| 13 | 9 |
| 14 | 9 |
+---------+--------+
I'm trying to parse this adjacent list to produce a following result set:
| EMP_ID | MGR_ID | LV | LEVEL1 | LEVEL2 | LEVEL3 | LEVEL4 | LEVEL5 |
---------------------------------------------------------------------
| 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 1 | 2 | 2 | 2 | 2 |
| 3 | 1 | 2 | 1 | 3 | 3 | 3 | 3 |
| 4 | 2 | 3 | 1 | 2 | 4 | 4 | 4 |
| 5 | 2 | 3 | 1 | 2 | 5 | 5 | 5 |
| 6 | 2 | 3 | 1 | 2 | 6 | 6 | 6 |
| 7 | 3 | 3 | 1 | 3 | 7 | 7 | 7 |
| 8 | 5 | 4 | 1 | 2 | 5 | 8 | 8 |
| 9 | 7 | 4 | 1 | 3 | 7 | 9 | 9 |
| 10 | 5 | 4 | 1 | 2 | 5 | 10 | 10 |
| 11 | 7 | 4 | 1 | 3 | 7 | 11 | 11 |
| 12 | 9 | 5 | 1 | 3 | 7 | 9 | 12 |
| 13 | 9 | 5 | 1 | 3 | 7 | 9 | 13 |
| 14 | 9 | 5 | 1 | 3 | 7 | 9 | 14 |
Here is what I have managed to get so far:
create table PC (
EMP_ID NUMBER NULL,
MGR_ID NUMBER NULL
);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (1.0, 1.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (2.0, 1.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (3.0, 1.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (4.0, 2.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (5.0, 2.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (6.0, 2.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (7.0, 3.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (8.0, 5.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (9.0, 7.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (10.0, 5.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (11.0, 7.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (12.0, 9.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (13.0, 9.0);
INSERT INTO PC (EMP_ID, MGR_ID)
VALUES (14.0, 9.0);
And the query:
WITH PERSON_HIER AS
(
SELECT 1 as level1,
CAST(NULL AS NUMBER) as level2,
CAST(NULL AS NUMBER) as level3,
CAST(NULL AS NUMBER) as level4,
CAST(NULL AS NUMBER) as level5
FROM PC
WHERE EMP_ID = 1 AND MGR_ID = 1
UNION ALL
SELECT L1.EMP_ID AS LEVEL1,
L2.EMP_ID AS LEVEL2,
L3.EMP_ID AS LEVEL3,
L4.EMP_ID AS LEVEL4,
L5.EMP_ID AS LEVEL5
FROM PC L1
LEFT OUTER JOIN PC L2 ON (L1.EMP_ID = L2.MGR_ID AND L2.EMP_ID != L1.EMP_ID)
LEFT OUTER JOIN PC L3 ON (L2.EMP_ID = L3.MGR_ID)
LEFT OUTER JOIN PC L4 ON (L3.EMP_ID = L4.MGR_ID)
LEFT OUTER JOIN PC L5 ON (L4.EMP_ID = L5.MGR_ID)
WHERE L1.MGR_ID = L1.EMP_ID
)
SELECT level1,
coalesce(level2, level1) AS LEVEL2,
coalesce(level3, level2, level1) AS LEVEL3,
coalesce(level4, level3, level2, level1) AS LEVEL4,
coalesce(level5, level4, level3, level2, level1) AS LEVEL5
FROM PERSON_HIER
order by level5
I'm using Oracle 10g so Recursive CTE or Hierarchial queries not possible in Oracle 10g
653
To review, Oracle hard parsing reads in statistics, utilizes index information and creates an execution plan. Soft parsing already has the execution plan and doesn't need to revisit the statisticsand so on.
In Oracle Database 10g, you can use both SQL and PL/SQL to implement regular expression support. Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. String manipulation and searching contribute to a large percentage of the logic in a Web-based application.
Description. A soft parse is recorded when the Oracle Server checks the shared pool for a SQL statement and finds a version of the statement that it can reuse. This metric represents the percentage of parse requests where the cursor was already in the cursor cache compared to the number of total parses.
REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE . This condition evaluates strings using characters as defined by the input character set.
You can use a single hierarchical query using CONNECT BY:
SQL> SELECT root,
2 MAX(DECODE(lvl, max_lvl, mgr_id)) lvl1,
3 MAX(DECODE(lvl, max_lvl, emp_id)) lvl2,
4 MAX(DECODE(lvl, greatest(max_lvl-1, 1), emp_id)) lvl3,
5 MAX(DECODE(lvl, greatest(max_lvl-2, 1), emp_id)) lvl4,
6 MAX(DECODE(lvl, greatest(max_lvl-3, 1), emp_id)) lvl5
7 FROM (SELECT connect_by_root(emp_id) root,
8 emp_id,
9 mgr_id,
10 level lvl,
11 MAX (level)
12 OVER (PARTITION BY connect_by_root(emp_id)) max_lvl
13 FROM pc
14 CONNECT BY NOCYCLE PRIOR mgr_id = emp_id) v
15 GROUP BY root
16 ORDER BY 1;
ROOT LVL1 LVL2 LVL3 LVL4 LVL5
---- ----- ----- ----- ----- -----
1 1 1 1 1 1
2 1 2 2 2 2
3 1 3 3 3 3
4 1 2 4 4 4
5 1 2 5 5 5
6 1 2 6 6 6
7 1 3 7 7 7
8 1 2 5 8 8
9 1 3 7 9 9
10 1 2 5 10 10
11 1 3 7 11 11
12 1 3 7 9 12
13 1 3 7 9 13
14 1 3 7 9 14
SQLFiddle demo.
60
Now an improved version(shows correct hierarcy):
select emp_id, mgr_id, lvl, h,
nvl(substr(h,instr(h,'/',1, 2)+1, instr(h, '/',1, 3)- instr(h,'/',1,2)-1), emp_id) as lvl1,
nvl(substr(h,instr(h,'/',1, 3)+1, instr(h, '/',1, 4)- instr(h,'/',1,3)-1), emp_id) as lvl2,
nvl(substr(h,instr(h,'/',1, 4)+1, instr(h, '/',1, 5)- instr(h,'/',1,4)-1), emp_id) as lvl3,
nvl(substr(h,instr(h,'/',1, 5)+1, instr(h, '/',1, 6) -instr(h,'/',1,5)-1), emp_id) as lvl4,
nvl(substr(h,instr(h,'/',1, 6)+1, instr(h, '/',1, 7) -instr(h,'/',1,6)-1), emp_id) as lvl5
from(
select emp_id, mgr_id , level lvl, sys_connect_by_path(mgr_id, '/')||'/' h
from pc
connect by nocycle prior emp_id = mgr_id
start with emp_id = 1
)
order by emp_id;
EMP_ID MGR_ID LVL H LVL1 LVL2 LVL3 LVL4 LVL5
2 1 1 1/1/ 1 2 2 2 2
3 1 1 1/1/ 1 3 3 3 3
4 2 2 2/1/2/ 1 2 4 4 4
5 2 2 2/1/2/ 1 2 5 5 5
6 2 2 2/1/2/ 1 2 6 6 6
7 3 2 3/1/3/ 1 3 7 7 7
8 5 3 5/1/2/5/ 1 2 5 8 8
9 7 3 7/1/3/7/ 1 3 7 9 9
10 5 3 5/1/2/5/ 1 2 5 10 10
11 7 3 7/1/3/7/ 1 3 7 11 11
12 9 4 9/1/3/7/9/ 1 3 7 9 12
13 9 4 9/1/3/7/9/ 1 3 7 9 13
14 9 4 9/1/3/7/9/ 1 3 7 9 14
15 14 5 14/1/3/7/9/14/ 1 3 7 9 14
SQL FIDDLE
This is my first try:
select emp_id, mgr_id, lvl, h,
nvl(substr(h,instr(h,' ',1, 1), instr(h, ' ',1, 2)- instr(h,' ',1,1)), emp_id) as lvl1,
nvl(substr(h,instr(h,' ',1, 2), instr(h, ' ',1, 3)- instr(h,' ',1,2)), emp_id) as lvl2,
nvl(substr(h,instr(h,' ',1, 3), instr(h, ' ',1, 4)- instr(h,' ',1,3)), emp_id) as lvl3,
nvl(substr(h,instr(h,' ',1, 4), instr(h, ' ',1, 5)- instr(h,' ',1,4)), emp_id) as lvl4,
nvl(substr(h,instr(h,' ',1, 5), instr(h, ' ',1, 6) -instr(h,' ',1,5)), emp_id) as lvl5
from(
select emp_id, mgr_id , level lvl, sys_connect_by_path(mgr_id, ' ') h
from pc
connect by nocycle prior emp_id = mgr_id
start with emp_id = 1
)
order by emp_id;
See SQLFiddle here
3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
