| Hierarchical Query for Chart of Account [message #408684] |
Wed, 17 June 2009 05:29  |
shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi
|
Senior Member |
|
|
Hellow all
I have one Table which is for chart of account
The Column in this Table is
Acc_id and Acc_name
I Insert data in This chart of account
Insert into COA
values
(01,'ASSETS');
values
(01001,'Current Assets');
values
(01002,'Fixed Assets');
values
(010010001,'Banks');
values
(010010002,'Cash');
values
(01001000100001,'Metrol Politan Bank');
values
(01001000100002,'Royal Bank');
values
(01001000100003,'Stander Charted Bank');
values
(01001000200001,'Cash in Hand');
values
(01001000200002,'Patty Cash');
That's all my record now i need to create a Hierarchical tree
Which look like this
01- Assets
------010001 Current Assets
----------010010001 Bank
--------------01001000100001 Metrol Politan Bank
--------------01001000100002 Royal Bank
--------------01001000100003 Stander Charted Bank
----------010010002 Cash
--------------01001000200001 Cash in Hand
--------------01001000200002 Patty Cash
------010002 Fixed Assets
----------010020001 Machinery Assets
--------------01002000100001 Needle Machine
--------------01002000100002 GGT Machine
----------010020002 Computer Assets
--------------01002000200001 Computer Server
--------------01002000200002 Computer other
Hope you guys understand i need the Hierarchical query for making this kind of Tree
Regards
Shahzaib
|
|
|
|
|
|
| Re: Hierarchical Query for Chart of Account [message #408702 is a reply to message #408684] |
Wed, 17 June 2009 06:23  |
shahzaib_4vip@hotmail.com
Messages: 410
Registered: December 2008
Location: karachi
|
Senior Member |
|
|
select
lpad('-',(level-1)*4,'-')|| acc_id ||
' ' || acc_name as tree
from coa
connect by prior
acc_id = rtrim(substr(acc_id,1,instr(acc_id,'0',-1)),'0')
start with rtrim(substr(acc_id,1,instr(acc_id,'0',-1)),'0') is null
its working in PL/SQL
But what is the code for Form
Any way Thanks for your reply Cookie
Regards
Shahzaib
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Hierarchical Query for Chart of Account [message #408758 is a reply to message #408711] |
Wed, 17 June 2009 11:43 |
tlananthu
Messages: 18
Registered: April 2007
Location: India
|
Junior Member |
 
|
|
Hi,
If you want this in record group try this:
select
lpad('-',(level-1)*4,'-')|| acc_id ||
' ' || acc_name,
lpad('-',(level-1)*4,'-')|| acc_id ||
' ' || acc_name as tree
from coa
connect by prior
acc_id = rtrim(substr(acc_id,1,instr(acc_id,'0',-1)),'0')
start with rtrim(substr(acc_id,1,instr(acc_id,'0',-1)),'0') is null
[EDITED by LF: applied [code] tags]
[Updated on: Wed, 17 June 2009 15:39] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Hierarchical Query for Chart of Account [message #409075 is a reply to message #409060] |
Fri, 19 June 2009 03:25 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Well, you can answer that yourself. Does your select statement for populating the tree look like
select col1 as status
, col2 as level
, col3 as label
, col4 as icon
, col5 as data
from ....
If not: the select cannot be used to populate a tree.
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
