Query Problem

[Oracle8ii]

I have a table "COAcc" and the following are the data

AccID AccTitle PAccId AccStatus OpenBal
001 Asset Null G 0
002 Liability Null G 0
003 Capital Null G 0
004 Expense Null G 0
005 Revenue Null G 0
001-001 Current Asset 001 G 0
001-001-0001 Cash Account 001-001 L 50000
001-001-0002 Checking Account 001-001 L 25000
001-002 Fixed Asset 001 G 0
001-002-0001 Computers 001-002 L 75000
001-002-0002 Machinery 001-002 L 125000

How a Look at data:

Scennario
Accid 001 is Parent/Group Account and that's why its PAccid is null.
The SubAccounts/Child Accounts of 001 are 001-001 and 001-002 and its Parent Account is 001.
Similarly, SubAccounts/Child Account of 001-001 are 001-001-0001 and 001-001-0002 and its Parent Account is 001-001

I think you understand the Parent/Child relation.

Now I come to point. I want to retreive the data through query like this.

AccID AccTitle PAccId AccStatus OpenBal
001 Asset Null G 275000
001-001 Current Asset 001 G 75000
001-001-0001 Cash Account 001-001 L 50000
001-001-0002 Checking Account 001-001 L 25000
001-002 Fixed Asset 001 G 200000
001-002-0001 Computers 001-002 L 75000
001-002-0002 Machinery 001-002 L 125000

Look at the openbal
Original Data
Accid Paccid OpenBal
001 Null 0
001-001 001 0
001-001-0001 001-001 50000
001-001-0002 001-001 25000

Queried Data
Accid Paccid OpenBal
001 Null 275000 sum of Paccid (001)
001-001 001 75000 sum of Paccid (001-001)
001-001-0001 001-001 50000
001-001-0002 001-001 25000
001-002 001 200000 sum of Paccid (001-002)
001-002-0001 001-002 75000
001-002-0002 001-002 125000

I hope you better understand. Please give me reply as soon as possible.

 

[SantaMufasa]

Oracle8ii,

Welcome to Tek-Tips...I noticed that you just became a member within the last few hours.

We are all eager to help you here, but we typically assist with syntax issues rather than logic issues. Where you have not posted code that you have attempted already, we can neither assist with syntax or logic and we certainly should not be building your entire code set for you.

Therefore, could you please post a draft of your best attempt at a SQL query to do the above, then we can offer suggestions that build upon your work.

Also, we can give you a hint about an Oracle SQL code feature that is extremely powerful when processing hierarchical data similar to your example (called "START WITH...CONNECT BY"). It simplifies hierarchical data access enormously.

May I suggest that you do not build parent account values into the account numbers of child accounts. Currently, your data requires the expansion of your account codes by 3-4 digits for every a new level of sub accounts. If your accounts need to go 6 levels deep, then your account numbers must become 18-24 digits in length and your access logic becomes an absolute nightmare.

May I suggest that you re-structure your account numbering to follow this simple construct:

[tt]
AcctID (other columns) PAcctID
------ --------------- -------
1
2
3
4 1
5 1
6 2
7 3
8 4
9 4
10
11 2
[/tt]
...in other words, do not attempt to embed ParentIDs (PAcctID) in the child accounts.

Once you post your code assertion, we can/will post assistence to help you achieve what you need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

select AccID, PAccId, OpenBal
from  "COAcc"
start with PAccId is null
connect by AccId = prior (PAccID)

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Sem, I was hoping for Oracle8ii to post his own initial code attempt before we handed him a solution. But, absent that, your code is obviously stellar, as usual.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

Actually I didn't see your post. Moreover I'm about to be sure that my answer was the first, but due to some odd reason (timezone?) mine are always behind even in such rare cases.

P.S. This time you was more severe than me

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Sem,

No severity intended (especially with you!)...You are my 'Oracle Hero' and I would never do or say anything intentionally critical of you. My post was just to explain why I didn't explicitly post a "SELECT...START WITH...CONNECT BY" solution myself.

I hope no harm done. If there was any offence taken, please accept my apologies and my retraction.

Cheers, friend !

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

Severe not with me but rather with Oracle8ii as in most cases when I try to make an inquirer prove that he's not just a lasy student you (a bit faster) provide an exhaustive solution

But after your reply I'm :~/ from top to toe

Regards, Dima

http://seminihin.narod.ru

 

[Oracle8ii]

I have tried to get data through below query.

select substr(accid,1,3), sum(openbal)
from coacc
group by substr(accid,1,3)
union
select substr(accid,1,7), sum(openbal)
from coacc
group by substr(accid,1,7)
union
select substr(accid,1,12), sum(openbal)
from coacc
group by substr(accid,1,12)

There is a problem with above query. It gets some duplicate records.
Please help me