Home » Developer & Programmer » Forms » Hierarchical Query for Chart of Account (10g developer 6I window xp)
Hierarchical Query for Chart of Account [message #408684] Wed, 17 June 2009 05:29 Go to next message
shahzaib_4vip@hotmail.com
Messages: 376
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 #408696 is a reply to message #408684] Wed, 17 June 2009 06:09 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
read up on connect by prior - and you'll need to do some creative substr'ing or instr'ing to get it working.
I'd seriously consider normalising the data.
Re: Hierarchical Query for Chart of Account [message #408702 is a reply to message #408684] Wed, 17 June 2009 06:23 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
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 #408704 is a reply to message #408702] Wed, 17 June 2009 06:30 Go to previous messageGo to next message
tlananthu
Messages: 18
Registered: April 2007
Location: India
Junior Member

Hi,

Where are you using this query in form? LOV?
Re: Hierarchical Query for Chart of Account [message #408711 is a reply to message #408684] Wed, 17 June 2009 06:43 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Sorry i don't understand

This is SQL/PLSQL query


I want it in form


Regards

Shahzaib ismail
Re: Hierarchical Query for Chart of Account [message #408713 is a reply to message #408684] Wed, 17 June 2009 06:50 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
So put it in the form - what's the exact problem?
Re: Hierarchical Query for Chart of Account [message #408722 is a reply to message #408684] Wed, 17 June 2009 07:03 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
its not working in Record Group

Regards

Shahzaib
Re: Hierarchical Query for Chart of Account [message #408723 is a reply to message #408684] Wed, 17 June 2009 07:05 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Data used to populate tree is invalid

Regards


Shahzaib ismail
Re: Hierarchical Query for Chart of Account [message #408735 is a reply to message #408684] Wed, 17 June 2009 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
and the error messages you are getting are?
Re: Hierarchical Query for Chart of Account [message #408740 is a reply to message #408684] Wed, 17 June 2009 07:49 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Quote:
Data used to populate tree is invalid

Regards


Shahzaib ismail
Re: Hierarchical Query for Chart of Account [message #408758 is a reply to message #408711] Wed, 17 June 2009 11:43 Go to previous messageGo to next message
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 #408821 is a reply to message #408684] Wed, 17 June 2009 23:47 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Still Same Error


Regards

Shahzaib
Re: Hierarchical Query for Chart of Account [message #408828 is a reply to message #408821] Thu, 18 June 2009 00:10 Go to previous messageGo to next message
tlananthu
Messages: 18
Registered: April 2007
Location: India
Junior Member

Could you post the exact error that pops up? Also mention version of Forms you are using.
Re: Hierarchical Query for Chart of Account [message #408947 is a reply to message #408684] Thu, 18 June 2009 07:32 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
My version is Developer 6I and the error is coming

Quote:
Data used to populate tree is invalid

Regards


Shahzaib ismail
Re: Hierarchical Query for Chart of Account [message #409059 is a reply to message #408947] Fri, 19 June 2009 02:07 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The query to populate a tree in Forms has a fixed column list:
1. status: expanded or not
2. level: depth in the tree
3. label: the label as it is diplayed on the form
4. icon: the icon used for the node
5. data: the value of the node

Note: this comes from memory.

MHE
Re: Hierarchical Query for Chart of Account [message #409060 is a reply to message #408684] Fri, 19 June 2009 02:22 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Thanks for your reply Maaher


I write my Code so do you think any thing wrong in this code?



Regards


Shahzaib Ismail
Re: Hierarchical Query for Chart of Account [message #409075 is a reply to message #409060] Fri, 19 June 2009 03:25 Go to previous message
Maaher
Messages: 7062
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 ....
To be more precise: do you select five columns?

If not: the select cannot be used to populate a tree.

MHE
Previous Topic: Forms 6i Crashes - Error Code: C0000006 IN_PAGE_ERROR
Next Topic: How to deploy forms 6i
Goto Forum:
  


Current Time: Mon Dec 05 23:49:22 CST 2016

Total time taken to generate the page: 0.11607 seconds