Home » Developer & Programmer » Forms » Hierarchical tree in form 6i
Hierarchical tree in form 6i [message #573233] Mon, 24 December 2012 10:10 Go to next message
irfankundi786@yahoo.com
Messages: 224
Registered: February 2009
Location: pakistan
Senior Member

Hierarchical Tree in oracle form 6i ...

i am trying to build tree in form 6i but it give error

SELECT 1,
level,
job
,
'',
ename



FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
order by level
i want to show job wise employees ...
Re: Hierarchical tree in form 6i [message #573234 is a reply to message #573233] Mon, 24 December 2012 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know which error you have as you didn't tell us but it works in SQL:
SQL> SELECT 1,
  2  level,
  3  job
  4  ,
  5  '',
  6  ename
  7  
  8  
  9  
 10  FROM emp
 11  START WITH mgr IS NULL
 12  CONNECT BY PRIOR empno = mgr
 13  order by level
 14  /
         1      LEVEL JOB       ' ENAME
---------- ---------- --------- - ----------
         1          1 PRESIDENT   KING
         1          2 MANAGER     JONES
         1          2 MANAGER     BLAKE
         1          2 MANAGER     CLARK
         1          3 ANALYST     FORD
         1          3 SALESMAN    WARD
         1          3 CLERK       JAMES
         1          3 CLERK       MILLER
         1          3 SALESMAN    ALLEN
         1          3 ANALYST     SCOTT
         1          3 SALESMAN    MARTIN
         1          3 SALESMAN    TURNER
         1          4 CLERK       ADAMS
         1          4 CLERK       SMITH

But I don't know if this is the expected result as you didn't tell us.
There are many things that are missing in your post.

Regards
Michel

Re: Hierarchical tree in form 6i [message #573245 is a reply to message #573234] Mon, 24 December 2012 23:09 Go to previous messageGo to next message
irfankundi786@yahoo.com
Messages: 224
Registered: February 2009
Location: pakistan
Senior Member

i want to show the tree in this shape
Manager.
jone
blake
clark

Analyst
Ford
Clerk
James
Miller

departments and employees in each depatment
Re: Hierarchical tree in form 6i [message #573248 is a reply to message #573245] Tue, 25 December 2012 04:47 Go to previous messageGo to next message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this?
SQL> with tab as
  2    (select empno, ename ime, deptno from emp
  3     union
  4     select deptno, dname, null from dept
  5    )
  6  select lpad(' ', 2*level-1) || ime result
  7  from tab
  8  connect by prior empno = deptno
  9  start with deptno is null
 10  order siblings by ime;

RESULT
--------------------
 ACCOUNTING
   CLARK
   KING
   MILLER
 OPERATIONS
 RESEARCH
   ADAMS
   FORD
   JONES
   SCOTT
   SMITH
 SALES
   ALLEN
   BLAKE
   JAMES
   MARTIN
   TURNER
   WARD

18 rows selected.

SQL>
Re: Hierarchical tree in form 6i [message #573251 is a reply to message #573248] Tue, 25 December 2012 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
or:
SQL> select decode(grouping(ename),
  2                0, '  '||ename,
  3                job||': '||count(*)||' employees') data
  4  from emp
  5  group by rollup(job, ename)
  6  having grouping(job) = 0
  7  order by job, decode(grouping(ename), 1, ' ', ename)
  8  /
DATA
-------------------------------------------------------------
ANALYST: 2 employees
  FORD
  SCOTT
CLERK: 4 employees
  ADAMS
  JAMES
  MILLER
  SMITH
MANAGER: 3 employees
  BLAKE
  CLARK
  JONES
PRESIDENT: 1 employees
  KING
SALESMAN: 4 employees
  ALLEN
  MARTIN
  TURNER
  WARD

Regards
Michel
Re: Hierarchical tree in form 6i [message #622000 is a reply to message #573251] Wed, 20 August 2014 06:48 Go to previous message
prasanthi.582
Messages: 1
Registered: August 2014
Location: BANGALORE
Junior Member
Thanks Michel
Previous Topic: Cant able to display the content of oracle forms
Next Topic: Update trigger
Goto Forum:
  


Current Time: Thu Nov 27 19:00:48 CST 2014

Total time taken to generate the page: 0.08353 seconds