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!

How to show an oracle tree having its node value from database table column.

Discussion in 'SQL PL/SQL' started by aadityainpccs, Jan 9, 2013.

  1. aadityainpccs

    aadityainpccs Active Member

    Messages:
    52
    Likes Received:
    2
    Trophy Points:
    160
    Location:
    New delhi (INDIA)
    Hello,
    Please anyone suggest me the solution/example for that,I have to make an oracle hierarchy tree which has node value from a database table column like 'ename column' in emp table.
    When i add new record into emp table then its new ename column value shoulod be shown in oracle hierarchy tree in oracle form 10g.
    thank you.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What, exactly, do you mean by 'hierarchy tree'? Is this what you have in mind:

    Code (SQL):
     
    SQL> SELECT lpad(mgr, 2*level, ' ') mgr, ename, empno
      2  FROM emp
      3  CONNECT BY prior empno = mgr
      4  START WITH mgr IS NULL;
    MGR                  ENAME           EMPNO
    -------------------- ---------- ----------
                         KING             7839
    7839                 JONES            7566
      7566               SCOTT            7788
        7788             ADAMS            7876
      7566               FORD             7902
        7902             SMITH            7369
    7839                 BLAKE            7698
      7698               ALLEN            7499
      7698               WARD             7521
      7698               MARTIN           7654
      7698               TURNER           7844
      7698               JAMES            7900
    7839                 CLARK            7782
      7782               MILLER           7934
     
    14 ROWS selected.
     
    SQL>
    SQL> INSERT INTO emp
      2  VALUES(8001, 'DODGE', 'CLERK', 7566, sysdate-40, 800, NULL, 20);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT lpad(mgr, 2*level, ' ') mgr, ename, empno
      2  FROM emp
      3  CONNECT BY prior empno = mgr
      4  START WITH mgr IS NULL;
     
    MGR                  ENAME           EMPNO
    -------------------- ---------- ----------
                         KING             7839
    7839                 JONES            7566
      7566               SCOTT            7788
        7788             ADAMS            7876
      7566               FORD             7902
        7902             SMITH            7369
      7566               DODGE            8001
    7839                 BLAKE            7698
      7698               ALLEN            7499
      7698               WARD             7521
      7698               MARTIN           7654
      7698               TURNER           7844
      7698               JAMES            7900
    7839                 CLARK            7782
      7782               MILLER           7934
     
    15 ROWS selected.
     
    SQL>
    SQL> INSERT INTO emp
      2  VALUES(8002, 'APPLEBEE', 'CLERK', 7566, sysdate-30, 850, NULL, 20);
     
    1 ROW created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT lpad(mgr, 2*level, ' ') mgr, ename, empno
      2  FROM emp
      3  CONNECT BY prior empno = mgr
      4  START WITH mgr IS NULL;
     
    MGR                  ENAME           EMPNO
    -------------------- ---------- ----------
                         KING             7839
    7839                 JONES            7566
      7566               SCOTT            7788
        7788             ADAMS            7876
      7566               FORD             7902
        7902             SMITH            7369
      7566               DODGE            8001
      7566               APPLEBEE         8002
    7839                 BLAKE            7698
      7698               ALLEN            7499
      7698               WARD             7521
      7698               MARTIN           7654
      7698               TURNER           7844
      7698               JAMES            7900
    7839                 CLARK            7782
      7782               MILLER           7934
     
    16 ROWS selected.
     
    SQL>
     
     
    aadityainpccs likes this.