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!

Hierarchical Tree using Record Group

Discussion in 'Oracle Forms and Reports' started by Akshita, Mar 25, 2010.

  1. Akshita

    Akshita Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    I am new to Forms. I would like to create a hierarchical tree with Department number as a node and all the employees (only employee names) under that department as shown below:

    [-]Department - 10
    KING
    JAMES
    -----
    [-]Department - 20
    MARY
    |
    |

    and so on...

    I have created a hierarchical tree 'HT_DEPTNO' under block 'BL_EMP'. I also created a Record Group 'RG_HTREE' with query as shown below:

    SELECT 1, LEVEL, E.ENAME, D.DEPTNO||' - '||D.DNAME DEPARTMENT, D.DEPTNO
    FROM EMP E, DEPT D
    WHERE E.DEPTNO = D.DEPTNO
    START WITH E.DEPTNO = 10
    CONNECT BY PRIOR E.EMPNO=E.MGR

    I know that there is some problem with the query, but am not able to resolve it. Can anyone please help me with this?

    Thanks for your time and concern.

    Akshita.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Try this instead:

    Code (SQL):
    SELECT 1, LEVEL, E.ENAME, D.DEPTNO||' - '||D.DNAME DEPARTMENT, D.DEPTNO
    FROM EMP E, DEPT D
    WHERE E.DEPTNO = D.DEPTNO
    START WITH E.DEPTNO = 10
    CONNECT BY PRIOR E.MGR=E.EMPNO
    Not being able to see the output it's difficult to actually determine what may be wrong. If the above query doesn't help post what results you are seeing so someone can assist you.
     
  3. Akshita

    Akshita Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Zargon, but this code is not working for me. this is not giving me an error, but the output which I get is in the below format:
    [-]KING
    HANS
    OLIVE
    [-]JONES
    JAMES
    MARTIN

    and so on...

    Akshita
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This query:

    Code (SQL):
    SELECT lpad(' ', 2*(level -1))||D.DEPTNO||' - '||D.DNAME DEPARTMENT, E.ename, D.DEPTNO
    FROM EMP E, DEPT D
    WHERE E.DEPTNO = D.DEPTNO
    CONNECT BY e.mgr = prior E.empno
    START WITH e.mgr IS NULL
    ORDER BY e.deptno, level
    /
    returns this result:

    Code (SQL):
    DEPARTMENT                                    ENAME          DEPTNO
    --------------------------------------------- ---------- ----------
    10 - ACCOUNTING                               KING               10
      10 - ACCOUNTING                             CLARK              10
        10 - ACCOUNTING                           MILLER             10
      20 - RESEARCH                               JONES              20
        20 - RESEARCH                             SCOTT              20
        20 - RESEARCH                             FORD               20
          20 - RESEARCH                           ADAMS              20
          20 - RESEARCH                           SMITH              20
      30 - SALES                                  BLAKE              30
        30 - SALES                                MARTIN             30
        30 - SALES                                TURNER             30

    DEPARTMENT                                    ENAME          DEPTNO
    --------------------------------------------- ---------- ----------
        30 - SALES                                JAMES              30
        30 - SALES                                ALLEN              30
        30 - SALES                                WARD               30
    What you do with it is your decision.
     
    Akshita likes this.
  5. Akshita

    Akshita Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    I got the required output when I changed the code in my record group query. The changed code is:

    Code (SQL):
    SELECT 1,level,dessc,dname, DEPTNO
    FROM (SELECT '1' l_level ,D.DEPTNO||' - '||D.DNAME dessc ,D.DNAME ,D.DEPTNO
    FROM DEPT D
    UNION
    SELECT '2' l_level,E.ENAME dessc , D.DEPTNO||' - '||D.DNAME dname,D.DEPTNO
    FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO) dept
    START WITH l_level = 1
    CONNECT BY PRIOR substr(dessc,1,instr(dessc,' - ')-1) = substr(dname,1,instr(dname,' - ')-1)
    This served my purpose...

    Thanks to Sinida for helping me in this regard.

    Regards,
    Akshita