Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Another hierarchical SELECT statement

user13117585Nov 22 2011 — edited Nov 22 2011
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?
This post has been answered by Frank Kulash on Nov 22 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 20 2011
Added on Nov 22 2011
6 comments
94 views