Get Child Data From Parent Data

Hi,

I have Chart Of Account Table:

1. AccNo
2. Name
3. GroupAcc   --> Refer to AccNo

I want to get all child value by passing parent AccNo.

It could be done using recursive CTE in SQL2005.

How to solve it using SQL 7 syntax?

Thank you.
LVL 1
emi_sastraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
some looping t-sql
create table #children ( accno int , lev int )
set nocount on 
declare @lev int
declare @root_accno int 
set @root_accno = 123
set @lev = 0 
insert into #children (accno, lev ) values ( @root_accno, @lev )
while @@rowcount > 0
begin
  set @lev = @lev + 1
  insert into #children (accno, lev) select t.accno, @lev from #children c join yourtable t on t.groupacc = c.accno and c.lev = @lev - 1
end 
select * from #children

Open in new window

0
emi_sastraAuthor Commented:
Hi angelIII,

Let me try first.

Thank you.
0
emi_sastraAuthor Commented:
Please see my code, some change from your code.

Incorrect syntax near the keyword 'SET'.

Why SET @root_accno = '123' ?

Thank you.
CREATE TABLE #children(accno Char, lev int)
SET nocount ON 
    DECLARE @lev int DECLARE @root_accno Char,
SET @root_accno = '123'
SET @lev = 0
        INSERT 
      INTO #children(accno, lev)
VALUES (@root_accno, @lev) 
    WHILE @@rowcount > 0 BEGIN
SET @lev = @lev + 1
        INSERT 
      INTO #children(accno, lev)
              SELECT t .accno, @lev
            FROM #children c LEFT JOIN
                TMGLACCNO AS t ON 
                t .GlobalAcc = c.accno AND 
                c.lev = @lev - 1 END
                    SELECT *
                  FROM #children

Open in new window

0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you had the CHAR, in the declare...
CREATE TABLE #children(accno varChar(100), lev int)
SET nocount ON 
 DECLARE @lev int 
DECLARE @root_accno varChar(100)
SET @root_accno = '123'
SET @lev = 0
        INSERT 
      INTO #children(accno, lev)
VALUES (@root_accno, @lev) 
    WHILE @@rowcount > 0 BEGIN
SET @lev = @lev + 1
        INSERT 
      INTO #children(accno, lev)
              SELECT t .accno, @lev
            FROM #children c LEFT JOIN
                TMGLACCNO AS t ON 
                t .GlobalAcc = c.accno AND 
                c.lev = @lev - 1 END
                    SELECT *
                  FROM #children

Open in new window

0
emi_sastraAuthor Commented:
Time out expired

SET @root_accno = '101.000.00' is the global account to find.

Thank you.
SET nocount ON 
    DECLARE @lev int DECLARE @root_accno varChar(100)
SET @root_accno = '101.000.00'
SET @lev = 0
        INSERT 
      INTO #children(accno, lev)
VALUES (@root_accno, @lev) 
    WHILE @@rowcount > 0 BEGIN
SET @lev = @lev + 1
        INSERT 
      INTO #children(accno, lev)
              SELECT t .accno, @lev
            FROM #children c LEFT JOIN
                TMGLACCNO AS t ON 
                t .GlobalAcc = c.accno AND 
                c.lev = @lev - 1 END
                    SELECT *
                  FROM #children

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
not LEFT JOIN. JOIN
also, do you have a index on globalAcc on the table?
SET nocount ON 
    DECLARE @lev int DECLARE @root_accno varChar(100)
SET @root_accno = '101.000.00'
SET @lev = 0
        INSERT 
      INTO #children(accno, lev)
VALUES (@root_accno, @lev) 
 
WHILE @@rowcount > 0
BEGIN
  SET @lev = @lev + 1
  INSERT 
    INTO #children(accno, lev)
   SELECT t .accno, @lev
            FROM #children c 
            JOIN  TMGLACCNO AS t ON 
                t .GlobalAcc = c.accno AND 
                c.lev = @lev - 1 
END
                    SELECT *
                  FROM #children

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor Commented:
do you have a index on globalAcc on the table?
No.

Some accounts appear more than once? To be exactly 3 times.

Thank you.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>>do you have a index on globalAcc on the table?
>No.
please create one with globalAcc + Accno on that table. will boost performance of that extraction query


>Some accounts appear more than once? To be exactly 3 times.
could it be that your structure is "incorrect"?

can you provide test data?
0
emi_sastraAuthor Commented:
Sure.

Thank you.
CHART-OF-ACCOUNT.xls
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
based on your test data, I got this, which seems just perfect, no duplicates ...
please double check your data/output.

for example:

101.001.01      1
101.001.02      1
101.001.03      1
101.002.01      1
101.003.01      1
101.003.03      1

is no duplicates, as the middle part changes...
accno           lev
--------------- -----------
101.000.00      0
101.001.01      1
101.001.02      1
101.001.03      1
101.002.01      1
101.003.01      1
101.003.03      1
101.004.01      1
101.005.01      1
101.005.02      1
101.005.03      1
101.005.06      1
101.005.07      1
101.005.08      1
101.005.09      1
101.006.02      1
101.006.03      1
101.007.01      1
101.009.01      1
101.101.01      1

Open in new window

0
emi_sastraAuthor Commented:
I see now.

May I executed it more than once.

How to delete the #children before create it?

Thank you.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>How to delete the #children before create it?
DELETE #children
0
emi_sastraAuthor Commented:
Hi angelIII,

My problem solved.

Thank you very much for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.