| Hierarchical Tree [message #448753] |
Thu, 25 March 2010 02:09  |
Akshita
Messages: 22
Registered: February 2010
|
Junior Member |
|
|
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?
I am attaching the form for your reference.
Thanks for your time and concern.
Akshita.
[EDITED by LF: applied format preserving codes]
[Updated on: Sat, 27 March 2010 02:21] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Hierarchical Tree [message #448842 is a reply to message #448753] |
Thu, 25 March 2010 06:33  |
sinida1984
Messages: 83
Registered: September 2007
Location: India
|
Member |
|
|
Hi Akshitha...
In the record group try the below query...this one is somewhat complicated.. Because in the node you need to see the data from department name in parent and employee name in the child . Hierarchical tree is a parent-child relation ...(Eg:- Boss - Employee relation can be easily made to hierarchical tree using the table EMP alone)
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)
Regards
Sinida
|
|
|
|
| Re: Hierarchical Tree [message #449131 is a reply to message #448842] |
Sat, 27 March 2010 02:19 |
Akshita
Messages: 22
Registered: February 2010
|
Junior Member |
|
|
@ Sinida
Thanks for your reply...
But this code is not working for me.
The Error message which appeared was:
FRM-12001: Cannot create the record group (Check your query).
|
|
|
|
| Re: Hierarchical Tree [message #449132 is a reply to message #448816] |
Sat, 27 March 2010 02:26 |
Akshita
Messages: 22
Registered: February 2010
|
Junior Member |
|
|
@ Cookiemonster
No, not exactly wrong data...
But when I use that code, the output is different from what I want. The whole tree is displaying the Employee names, no department number is displayed as a node. The output appearing is as shown below:
[-]CLARK
JAMES
GREEN
MARTIN
[-]KING
[-]JONES
HANS
OLIVE
|
|
|
and so on...
Akshita.
[EDITED by LF: applied [pre] tags to preserve formatting. Please, don't forget to do that next time]
[Updated on: Sat, 27 March 2010 13:16] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Hierarchical Tree [message #449258 is a reply to message #449257] |
Sun, 28 March 2010 23:19 |
sinida1984
Messages: 83
Registered: September 2007
Location: India
|
Member |
|
|
Hi Cookiemaster
I have attached the form where i put the above query and it worked fine for me.. Only difference is that i tested in Oracle forms 10g (scott/tiger).
Thanks and regards
Sinida
|
|
|
|
| Re: Hierarchical Tree [message #449309 is a reply to message #449258] |
Mon, 29 March 2010 02:45 |
Akshita
Messages: 22
Registered: February 2010
|
Junior Member |
|
|
Hi Sinida,
The form which u attached is not working in my system. It says:
"PDE-PEP006: Encoded Program Unit has an unknown format" when I try to open the form. And when I run the form, its says:
" FRM-30351: No list elements defined for list item.
List HT_DEPTNO
Item: HT_DEPTNO
Block: BL_EMP
Form: XXFEMP_HTREE
FRM-30085: Unable to adjust form for output. "
Regards & Thanks,
Akshita.
|
|
|
|
|
|
|
|
|
|
| Re: Hierarchical Tree [message #449324 is a reply to message #449320] |
Mon, 29 March 2010 03:54 |
sinida1984
Messages: 83
Registered: September 2007
Location: India
|
Member |
|
|
Hi...
I forgot to mention that my oracle database was too Oracle 10g Version. Which version of "database" you are using Akshita? If oracle 10g this query should work fine in SQL plus
Regards
Sinida
|
|
|
|
|
|
|
|
|
|
| Re: Hierarchical Tree [message #449333 is a reply to message #449324] |
Mon, 29 March 2010 04:07 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I ran that query; here are the results (not what it is doing, but how):
10.2.0.1.0 - OK
9.2.0.7.0 - OK
8.1.7.0.0 - ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUPBY, etc.
I don't have other databases here, but I guess that 8i - 10g is quite a valid set to test it.
Cookiemonster, what "error" are you talking about, exactly? Is it a syntax error (or similar), or query runs but returns wrong results, or ...?
|
|
|
|
| Re: Hierarchical Tree [message #449335 is a reply to message #449330] |
Mon, 29 March 2010 04:17 |
Akshita
Messages: 22
Registered: February 2010
|
Junior Member |
|
|
All I did was that I replaced my code in record group query with your code and ran the form. It worked.
I am attaching the form for your reference.
Once again, Thanks Sinida.
Regards,
Akshita
|
|
|
|
| Re: Hierarchical Tree [message #449338 is a reply to message #449333] |
Mon, 29 March 2010 04:24 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Littlefoot wrote on Mon, 29 March 2010 10:07
Cookiemonster, what "error" are you talking about, exactly? Is it a syntax error (or similar), or query runs but returns wrong results, or ...?
No idea, was just going off the reported error message. So I asked OP to run it in sqlplus to find out. I'm guessing the problem was a typo.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
