Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    19

    Exclamation Unanswered: urgent plzzzzzz.

    hi all!

    may be you laugh on me but i want to know anyway.

    i am using 6i and 10g and wants to know about Hierarchical Tree to populate records like :

    1) from dept and emp tables (emp table DO NOT HAVE mgr field)
    2) display data as below:

    - Location01
    -- Deptno01
    --- emp1
    --- emp2
    --- emp3
    -- Deptno02
    --- emp1
    --- emp2
    - Location02
    -- Deptno01
    --- emp1
    --- emp2
    -- Deptno02
    --- emp1
    --- emp2
    --- emp3

    i used - or -- or --- only to make Levels its not required.
    please write an SQL statement to populate Hierarchical Tree for above.

    thanks in advance for help.

  2. #2
    Join Date
    Feb 2004
    Posts
    41
    'A candle will loose nothing by lighting an another candle'

  3. #3
    Join Date
    Nov 2004
    Posts
    19
    thnx 4 yr reply but its not my answer as i already mentioned that the emp table has no MGR field and there are all examples using CONNECT BY PRIOR empno= mgr.
    if anyone has solution as i want????

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Need table definitions, etc..

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    All you need is a simple JOIN:
    Code:
    SQL>break on loc on deptno
    SQL>select loc, d.deptno, e.empno
      2  from dept d, emp e
      3  where d.deptno = e.deptno
      4  order by 1,2,3
      5*/
    
    LOC               DEPTNO      EMPNO
    ------------- ---------- ----------
    CHICAGO               30       7499
                                   7521
                                   7654
                                   7698
                                   7844
                                   7900
    DALLAS                20       7369
                                   7566
                                   7788
                                   7876
                                   7902
    NEW YORK              10       7782
                                   7934
    
    13 rows selected.
    
    SQL>
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Nov 2004
    Posts
    19
    thanks for your help but if you see i wrote to populate records in Hierarchical Tree which we are using in Forms (as also i mentioned that "i am using 6i and 10g") i need it for Forms.

  7. #7
    Join Date
    Nov 2004
    Posts
    19
    can anyone help to solve the problem????
    want that Hierarchical Tree to populate in FORMS.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Start with a query something like this:
    Code:
    SQL> select rpad('..',level*2,'..')||label formatted
      2  from
      3  (
      4  select distinct loc label, loc data, null parent from dept
      5  UNION ALL
      6  select dname, to_char(deptno), loc parent from dept
      7  UNION ALL
      8  select ename, to_char(empno), to_char(deptno) from emp
      9  )
     10  start with parent is null
     11  connect by prior data = parent;
    
    FORMATTED
    --------------------
    ..BOSTON
    ....OPERATIONS
    ..CHICAGO
    ....SALES
    ......ALLEN
    ......WARD
    ......MARTIN
    ......BLAKE
    ......TURNER
    ......JAMES
    ..DALLAS
    ....RESEARCH
    ......SMITH
    ......JONES
    ......SCOTT
    ......ADAMS
    ......FORD
    ..NEW YORK
    ....ACCOUNTING
    ......CLARK
    ......KING
    ......MILLER
    
    22 rows selected.
    Then to suit the Forms hierarchy setup you need to make it more like this:
    Code:
    SQL> select 1 state, level depth, label, null icon, data
      2  from
      3  (
      4  select distinct loc label, loc data, null parent from dept
      5  UNION ALL
      6  select dname, to_char(deptno), loc parent from dept
      7  UNION ALL
      8  select ename, to_char(empno), to_char(deptno) from emp
      9  )
     10  start with parent is null
     11  connect by prior data = parent;
    
         STATE      DEPTH LABEL          I DATA
    ---------- ---------- -------------- - ----------------------------------------
             1          1 BOSTON           BOSTON
             1          2 OPERATIONS       40
             1          1 CHICAGO          CHICAGO
             1          2 SALES            30
             1          3 ALLEN            7499
             1          3 WARD             7521
             1          3 MARTIN           7654
             1          3 BLAKE            7698
             1          3 TURNER           7844
             1          3 JAMES            7900
             1          1 DALLAS           DALLAS
             1          2 RESEARCH         20
             1          3 SMITH            7369
             1          3 JONES            7566
             1          3 SCOTT            7788
             1          3 ADAMS            7876
             1          3 FORD             7902
             1          1 NEW YORK         NEW YORK
             1          2 ACCOUNTING       10
             1          3 CLARK            7782
             1          3 KING             7839
             1          3 MILLER           7934
    
    22 rows selected.

  9. #9
    Join Date
    Nov 2004
    Posts
    19
    thanks alot andrewst
    i really much greatful for this help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •