CODEWITHSUNDEEP
sqlsap-ase
Charbel
Charbel

Reputation: 14687

sql iteration syntax error

Can anyone help me out with the sql below that, given a table parent_child_grouping which contains two columns parent_name and child_name, where a child can be a parent for other children.

I'm trying to write a sql (sybase compatible sql- which eventually would go into a stored procedure) that given a list of parents, it would iterate and return all the children and sub children of the input.

I wrote the below:

    DECLARE @parents varchar(500)
    set @parents = "'parent1', 'parent2'"

 if  exists (select 1 from sysobjects where type='U' and name='pg_result')
     drop table pg_result
if  exists (select 1 from sysobjects where type='U' and name='temp_intermediat_res')
     drop table temp_intermediat_res


    DECLARE @sql varchar(500)

    SET @sql = 'SELECT 
                    pg.parent_name, 
                    pg.child_name 
                into pg_result
                FROM 
                    mydb..parent_child_grouping pg
                WHERE 
                    pg.parent_name IN (' +@parents+')'

execute( @sql)
go
select r.parent_name,r.child_name into temp_intermediat_res from pg_result r 
go

DECLARE @foundchildren integer
set @foundchildren = 1

while(@foundchildren > 0)
begin

        insert pg_result select pg.parent_name,pg.child_name from mydb..parent_child_grouping pg,temp_intermediat_res i
             where i.child_name=pg.parent_name

        set @foundchildren = @@rowcount

        select i.parent_name into parents_list from temp_intermediat_res i

        drop table temp_intermediat_res

        select pg.parent_name, pg.child_name into temp_intermediat_res 
            from 
                 mydb..parent_child_grouping pg,
                 parents_list p 
            where p.parent_name=pg.parent_name
        drop table parents_list


end --while

go
select pg.parent_name, pg.child_name from pg_result pg
go

That's giving me the syntax error below: 

 >[Error] Script lines: 170-196 ----------------------
 There is already an object named 'temp_intermediat_res' in the database.
 Msg: 2714, Level: 16, State: 1
 Server: SYBDEV, Line: 16

it's a bit weird as I had just dropped the table in the previous line

Lines 16 and around it being the following (in the sql above): 

 drop table temp_intermediat_res

         select pg.* into temp_intermediat_res 
             from

as if the drop table is not taking effect for some reason.

i don't really need the temp_intermediat_res and pg_result to be permanent tables, and prepending their names with a # (to make them temporary) is also causing problems.

any ideas please?

I'm using sybase (select @@ version gives: Adaptive Server Enterprise/12.5.4/EBF 16785 ESD#10/P/Sun_svr4/OS 5.8/ase1254/2159/64-bit/FBO/Mon Nov 2 13:08:08 2009)

Thanks

Upvotes: 0

Views: 285

Answers (1)

aF.
aF.

Reputation: 66687

With the following code, you can go as deep as you want. The variable @numberOfIterations define the number of childs that you want to see.

DECLARE @numberOfIterations int
DECLARE @parents varchar(500)
set @parents = "'parent1', 'parent2'"
set @numberOfIterations = 3

DECLARE @sql varchar(500)

create table #pcg_result ( parent_name varchar, child_name varchar )

create table #pcg1_result ( parent_name varchar, child_name varchar )

create table #pcg2_result ( parent_name varchar, child_name varchar )

SET @sql = 'insert into #pcg_result
            SELECT 
                    pcg.parent_name, 
                    pcg.child_name 

                FROM 
                    mydb..parent_child_grouping pcg
                WHERE 
                    pcg.parent_name IN (' +@parents+')' execute( @sql)

insert into #pcg1_result select parent_name, child_name from
#pcg_result

while(@numberOfIterations <> 0) begin
    truncate table #pcg2_result

    insert into #pcg2_result
    select pcg.parent_name, pcg.child_name
    from mydb..parent_child_grouping pcg where parent_name in (select child_name from #pcg1_result)

    insert into #pcg_result
    select parent_name, child_name
    from #pcg2_result

    truncate table #pcg1_result

    insert into #pcg1_result
    select parent_name, child_name
    from #pcg2_result

    set @numberOfIterations = @numberOfIterations - 1 end

select distinct parent_name, child_name from #pcg_result


drop table #pcg_result
drop table #pcg1_result
drop table #pcg2_result
go

Upvotes: 1

Related Questions