Home » SQL & PL/SQL » SQL & PL/SQL » Hierarical query (9i )
Hierarical query [message #583057] Thu, 25 April 2013 04:15 Go to next message
glmjoy
Messages: 122
Registered: September 2011
Location: KR
Senior Member
I want Hierarical query to display my Chart_Of_Account. I want to make a tree Form in 6i
error i am getting is connect by nocycle prior account_code=parent_code
*
ERROR at line 4:
ORA-00920: invalid relational operator


Table

Create Table Chart_Of_Account (Account_Code Char(19), Account_Title varchar2(70), Parent_Code Char(19))
insert into Chart_Of_Account ('DGHOA01010101000001','TEST','DGHOA01010100000000')
insert into Chart_Of_Account ('DGHOA01010101000002','TEST1','DGHOA01010100000000')
insert into Chart_Of_Account ('DGHOA01010101000003','TEST2','DGHOA01010100000000')


select -1,level,account_code||' - '||ACCOUNT_Title,'NULL',to_char(ACCOUNT_CODE)
connect_by_iscycle from chart_of_account
start with Substr(account_code,13,7)='0000000'
connect by nocycle prior account_code=parent_code
Re: Hierarical query [message #583060 is a reply to message #583057] Thu, 25 April 2013 04:27 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Invalid test case (INSERT doesn't work).

As of your query, it returns nothing because no ACCOUNT_CODE equals PARENT_CODE.

As of Forms 6i problems, its PL/SQL engine might not "understand" syntax you used (i.e. CONNECT_BY_ISCYCLE).
Re: Hierarical query [message #583062 is a reply to message #583057] Thu, 25 April 2013 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
nocycle


This is a 10g feature.

Regards
Michel
Re: Hierarical query [message #583072 is a reply to message #583062] Thu, 25 April 2013 07:06 Go to previous message
glmjoy
Messages: 122
Registered: September 2011
Location: KR
Senior Member
Thanks A Lot
Previous Topic: difference between oracle 9i and 10g
Next Topic: Complex batch allocation logic + summary of alternate rows [2 merged by jd]
Goto Forum:
  


Current Time: Wed Apr 16 15:12:23 CDT 2014

Total time taken to generate the page: 0.10128 seconds