Get Child Data From Parent Data

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

Select allOpen in new window

Hi angelIII,

Let me try first.

Thank you.

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

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

do you have a index on globalAcc on the table?
No.

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

Thank you.

>>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?

Sure.

Thank you.
CHART-OF-ACCOUNT.xls

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

Select allOpen in new window

I see now.

May I executed it more than once.

How to delete the #children before create it?

Thank you.

>How to delete the #children before create it?
DELETE #children

Hi angelIII,

My problem solved.

Thank you very much for your help.