1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Creating a hierarchical view

Discussion in 'SQL PL/SQL' started by wbirsak, Dec 16, 2010.

  1. wbirsak

    wbirsak Guest

    Hi everyone,

    I am new to this forum... and i need a little help.

    I've got the following table structure:

    ID (integer), PARENT_ID (integer), NAME varchar(32)

    Table is filled as follows:

    1, NULL, DEP A
    2, 1, DEP B
    3, 1, DEP C
    4, 2, DEP D
    5, 3, DEP E
    6, 4, DEP F

    I need to create a view that looks like this (Same Colums):

    1, NULL, DEP A
    2, 1, DEP B
    3, 1, DEP C
    4, 2, DEP D
    4, 1, DEP D
    5, 3, DEP E
    5, 1, DEP E
    6, 4, DEP F
    6, 2, DEP F
    6, 1, DEP F

    I need this view to determine if properties are inherited from another department... eg.

    DEP A got property A,B
    DEP B got property C,D
    DEP C got property E,F
    DEP D got property G,H
    DEP E got property I,K
    DEP F got propety L,M

    DEP F got the following property thru inheritence:
    L,M,G,H,C,D,A,B


    I've some got a suggestion, a pointer or maybe a solution :)

    Thanks in advance..
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Slightly difficult task. One solution will be to use the Pipelined Functions.

    Here is one illustration

    Code (SQL):

    SQL>  SELECT * FROM PARENT_CHILD ORDER BY 1;

            ID  PARENT_ID NAME
    ---------- ---------- --------------------
             1            A
             2          1 B
             3          1 C
             4          2 D
             5          3 E
             6          4 F
             7          5 G

    7 ROWS selected.

    SQL> CREATE OR REPLACE
      2  TYPE PARENT_CHILD_OBJ AS OBJECT
      3  ( ID            INTEGER,
      4  PARENT_ID     INTEGER,
      5  NAME          VARCHAR(32),
      6  ORDER_IDX     INTEGER);  
      7  /

    TYPE created.

    SQL> CREATE OR REPLACE
      2  TYPE PARENT_CHILD_ARRAY AS TABLE OF PARENT_CHILD_OBJ
      3  /

    TYPE created.

    SQL> CREATE OR REPLACE FUNCTION GET_PARENT_CHILD_HEIRARCHY
    RETURN PARENT_CHILD_ARRAY PIPELINED
      2    3  AS
      4  l_order_idx_n           INTEGER:=1;
      5  l_parent_id_n           INTEGER;
      6  l_parent_child_rec      PARENT_CHILD%ROWTYPE;                                                                                                      
      7  BEGIN
      8
      9      FOR rec1 IN (SELECT ID, PARENT_ID,
     10                         NAME,1 order_idx
     11                   FROM PARENT_CHILD
     12                   ORDER BY ID
     13                  )
     14      LOOP
     15
     16          PIPE ROW (PARENT_CHILD_OBJ(rec1.ID, rec1.PARENT_ID,rec1.NAME,rec1.order_idx));
     17
     18          l_parent_id_n := rec1.PARENT_ID;
     19          l_order_idx_n := 1;
     20
     21          LOOP
     22
     23              BEGIN
     24
     25                  SELECT *
     26                  INTO l_parent_child_rec
     27                  FROM PARENT_CHILD
     28                  WHERE ID = l_parent_id_n;
     29
     30                  l_order_idx_n := l_order_idx_n + 1;
     31                  l_parent_id_n := l_parent_child_rec.PARENT_ID;
     32
     33              EXCEPTION
     34              WHEN OTHERS THEN
     35                  l_parent_id_n := NULL;
     36              END;
     37
     38              EXIT WHEN l_parent_id_n IS NULL;
     39
     40              PIPE ROW (PARENT_CHILD_OBJ(rec1.ID, l_parent_id_n,rec1.NAME,l_order_idx_n));
     41
     42          END LOOP;
     43
     44      END LOOP;
     45
     46      RETURN;
     47  END GET_PARENT_CHILD_HEIRARCHY;
     48  /

    FUNCTION created.

    SQL>
    SQL> SET pagesize 100
    SQL> SELECT *
      2  FROM TABLE( GET_PARENT_CHILD_HEIRARCHY)
      3  ORDER BY ID ASC, ORDER_IDX DESC;

            ID  PARENT_ID NAME                              ORDER_IDX
    ---------- ---------- -------------------------------- ----------
             1            A                                         1
             2          1 B                                         1
             3          1 C                                         1
             4          1 D                                         2
             4          2 D                                         1
             5          1 E                                         2
             5          3 E                                         1
             6          1 F                                         3
             6          2 F                                         2
             6          4 F                                         1
             7          1 G                                         3
             7          3 G                                         2
             7          5 G                                         1

    13 ROWS selected.

    SQL> COLUMN ORDER_IDX NOPRINT
    SQL> SELECT *
      2  FROM TABLE( GET_PARENT_CHILD_HEIRARCHY)
      3   ORDER BY ID ASC, ORDER_IDX DESC;

            ID  PARENT_ID NAME
    ---------- ---------- --------------------------------
             1            A
             2          1 B
             3          1 C
             4          1 D
             4          2 D
             5          1 E
             5          3 E
             6          1 F
             6          2 F
             6          4 F
             7          1 G
             7          3 G
             7          5 G

    13 ROWS selected.

    SQL>