Hi everyone, I have a little question, hoping that someone can help me?
Imagine the following table:
DROP TABLE dpt;
CREATE TABLE dpt
(
dpt_id number(10),
dpt_name varchar2(100),
parent_id number(10),
is_valid number(1)
);
INSERT INTO dpt VALUES(1, 'Dpt 1', null, 1);
INSERT INTO dpt VALUES(2, 'Dpt 2', null, 1);
INSERT INTO dpt VALUES(3, 'Dpt 3', null, 1);
INSERT INTO dpt VALUES(10, 'Dpt 10', 1, 1);
INSERT INTO dpt VALUES(11, 'Dpt 11', 1, 1);
INSERT INTO dpt VALUES(12, 'Dpt 12', 1, 1);
INSERT INTO dpt VALUES(20, 'Dpt 20', 1, 1);
INSERT INTO dpt VALUES(100, 'Dpt 100', 10, 1);
INSERT INTO dpt VALUES(1000, 'Dpt 1000', 100, 0);
INSERT INTO dpt VALUES(200, 'Dpt 200', 20, 1);
INSERT INTO dpt VALUES(2000, 'Dpt 2000', 200, 0);
INSERT INTO dpt VALUES(20000, 'Dpt 20000', 2000, 0);
SELECT dpt_id,
dpt_name,
SYS_CONNECT_BY_PATH(dpt_id, '#') as path,
SYS_CONNECT_BY_PATH(is_valid, '#') as valid_path
FROM dpt
START WITH parent_id IS NULL
CONNECT BY PRIOR dpt_id = parent_id;
I have a table with departments. It's an organisational tree. But, some of the deepest levels may be invalid. Invalid ndes are always one or more from the deepest level in the tree. In my previous example, nodes 1000, 2000, 20000 are not valid.
I wanted to know if it was possible to have, for invalid nodes, the department id of the first valid ascendant?
Something like
1 Dpt 1 #1 #1 - 1
10 Dpt 10 #1#10 #1#1 - 10
100 Dpt 100 #1#10#100 #1#1#1 - 100
1000 Dpt 1000 #1#10#100#1000 #1#1#1#0 - 100
11 Dpt 11 #1#11 #1#1 - 11
12 Dpt 12 #1#12 #1#1 - 12
20 Dpt 20 #1#20 #1#1 - 20
200 Dpt 200 #1#20#200 #1#1#1 - 200
2000 Dpt 2000 #1#20#200#2000 #1#1#1#0 - 200
20000 Dpt 20000 #1#20#200#2000#20000 #1#1#1#0#0 - 200
2 Dpt 2 #2 #1 - 2
3 Dpt 3 #3 #1 - 3
I'm using Oracle 10g. And the previous sample data have convinient Ids. In reality, ids are completely random :)
Any idea?