Home » Developer & Programmer » Forms » Hierarchical Tree (Oracle Forms 6i)
Hierarchical Tree [message #448753] Thu, 25 March 2010 02:09 Go to next message
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 #448816 is a reply to message #448753] Thu, 25 March 2010 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
What problem are you having?
Are getting an error message?
Wrong data?
Re: Hierarchical Tree [message #448842 is a reply to message #448753] Thu, 25 March 2010 06:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #449139 is a reply to message #448753] Sat, 27 March 2010 04:41 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's an error in sinida1984's query.
Try running the query in sqlplus to see what the error is.
Re: Hierarchical Tree [message #449257 is a reply to message #449139] Sun, 28 March 2010 23:12 Go to previous messageGo to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
Hi All

It worked fine for me. I tried it in oracle forms 10g as I am working in that. I have attached the screenshot also. Please look into it. I don't know whether it will work in forms 6i.

Thanks and regards
Sinida sherin.
  • Attachment: output.JPG
    (Size: 71.92KB, Downloaded 323 times)
Re: Hierarchical Tree [message #449258 is a reply to message #449257] Sun, 28 March 2010 23:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #449315 is a reply to message #449309] Mon, 29 March 2010 03:22 Go to previous messageGo to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
Okay..So this query should not be possible with lower version of form. I tried in forms 10g.

Thanks and regards
Sinida
Re: Hierarchical Tree [message #449320 is a reply to message #448753] Mon, 29 March 2010 03:48 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
@Akshita - try running sinida's query in sqlplus.
Re: Hierarchical Tree [message #449322 is a reply to message #449320] Mon, 29 March 2010 03:53 Go to previous messageGo to next message
Akshita
Messages: 22
Registered: February 2010
Junior Member
@Cookiemonster

The code is working well in SQL Plus. but not in Forms 6i
Re: Hierarchical Tree [message #449324 is a reply to message #449320] Mon, 29 March 2010 03:54 Go to previous messageGo to next message
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 #449328 is a reply to message #449322] Mon, 29 March 2010 03:56 Go to previous messageGo to next message
Akshita
Messages: 22
Registered: February 2010
Junior Member
@Sinida and Cookiemonster

hey its working... Thanks Laughing

Regards,
Akshita
Re: Hierarchical Tree [message #449329 is a reply to message #449328] Mon, 29 March 2010 03:58 Go to previous messageGo to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
Hi Akshita

Can you please post the solution how it worked in Forms 6i..

Thanks in advance Smile
Sinida
Re: Hierarchical Tree [message #449330 is a reply to message #449258] Mon, 29 March 2010 04:00 Go to previous messageGo to next message
Akshita
Messages: 22
Registered: February 2010
Junior Member
I donno wat was wrong previously, but now i got it.

Thanks Sinida
Re: Hierarchical Tree [message #449333 is a reply to message #449324] Mon, 29 March 2010 04:07 Go to previous messageGo to next message
Littlefoot
Messages: 20827
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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.
Re: Hierarchical Tree [message #449354 is a reply to message #449338] Mon, 29 March 2010 04:56 Go to previous message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, I see ... I thought that you actually "saw" an error but wanted Akshita to find it for himself.
Previous Topic: JavaBeans can interface with OpenOffice
Next Topic: Unique constraint violated
Goto Forum:
  


Current Time: Tue Sep 27 12:48:01 CDT 2016

Total time taken to generate the page: 0.12769 seconds