How to use CURSOR for recurse 
Author Message
 How to use CURSOR for recurse

I think that the SQL statement below will do what you're trying to do,
but without using recursion. There is one difference though, in that
your code will not copy over items after a gap in the p_ia_no, while my
code will. If you don't want those copied then don't use the code below.

INSERT INTO tree
   ( gen_no,
     supper_ia_no ,
     p_ia_no,
     ia_no )

       p_ia_no,
       p_ia_no,
       ia_no
FROM mbr

   HTH,
    -Tom.



Quote:
> How to use CURSOR for recurse ,

> I call  below Example  code, but to show up an error message:

> Line:2
> A cursor with the name 'cur_tree' alreadly exists.
> Continue ?

> please help me , thanks your!
> I am used MS-SQL 7.0

> Example  code:



> AS


> SET NOCOUNT ON

> DECLARE cur_tree CURSOR FOR
>   SELECT mbr.ia_no
>     FROM mbr

> OPEN cur_tree
>   FETCH NEXT FROM cur_tree


> begin
>      INSERT INTO tree
>          ( gen_no,
>            supper_ia_no ,
>            p_ia_no,
>            ia_no )





>   FETCH NEXT FROM cur_tree

> end
> CLOSE cur_tree
> DEALLOCATE cur_tree
> GO

Sent via Deja.com http://www.***.com/
Before you buy.


Fri, 04 Apr 2003 03:00:00 GMT
 How to use CURSOR for recurse

How to use CURSOR for recurse ,

I call  below Example  code, but to show up an error message:

Line:2
A cursor with the name 'cur_tree' alreadly exists.
Continue ?

please help me , thanks your!
I am used MS-SQL 7.0

Example  code:



AS


SET NOCOUNT ON

DECLARE cur_tree CURSOR FOR
  SELECT mbr.ia_no
    FROM mbr

OPEN cur_tree
  FETCH NEXT FROM cur_tree


begin
     INSERT INTO tree
         ( gen_no,
           supper_ia_no ,
           p_ia_no,
           ia_no )





  FETCH NEXT FROM cur_tree

end
CLOSE cur_tree
DEALLOCATE cur_tree
GO



Sat, 05 Apr 2003 02:03:16 GMT
 How to use CURSOR for recurse
Thomas R.
  thanks your responsion, but that are not  i  to want.
  i  to want below Result ( that are the tree structure ).
  please help me , thanks your!

For Example:
Before:
  child       parent
  ------     -------
   A2           A1
   A3           A2
   A4           A1
   A5           A2
   A6           A3
   A7           A4
   A8           A6

After:
 parent      child
 ------      ------
  A1           A2
  A1           A4
  A2           A3
  A2           A5
  A3           A6
  A4           A7
  A6           A8

Result:
Level        Code
----        -----------
 0            A1
 1                A2
 2                      A3
 3                          A6
 4                                A8
 2                      A5
 1                A4
 2                      A7



Quote:
> I think that the SQL statement below will do what you're trying to do,
> but without using recursion. There is one difference though, in that
> your code will not copy over items after a gap in the p_ia_no, while my
> code will. If you don't want those copied then don't use the code below.

> INSERT INTO tree
>    ( gen_no,
>      supper_ia_no ,
>      p_ia_no,
>      ia_no )

>        p_ia_no,
>        p_ia_no,
>        ia_no
> FROM mbr

>    HTH,
>     -Tom.



> > How to use CURSOR for recurse ,

> > I call  below Example  code, but to show up an error message:

> > Line:2
> > A cursor with the name 'cur_tree' alreadly exists.
> > Continue ?

> > please help me , thanks your!
> > I am used MS-SQL 7.0

> > Example  code:



> > AS


> > SET NOCOUNT ON

> > DECLARE cur_tree CURSOR FOR
> >   SELECT mbr.ia_no
> >     FROM mbr

> > OPEN cur_tree
> >   FETCH NEXT FROM cur_tree


> > begin
> >      INSERT INTO tree
> >          ( gen_no,
> >            supper_ia_no ,
> >            p_ia_no,
> >            ia_no )





> >   FETCH NEXT FROM cur_tree

> > end
> > CLOSE cur_tree
> > DEALLOCATE cur_tree
> > GO

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Sat, 05 Apr 2003 03:00:00 GMT
 How to use CURSOR for recurse
Instead of:

try:



Sat, 05 Apr 2003 03:00:00 GMT
 How to use CURSOR for recurse
Ok, I think that I see what you're trying to do now. What I've done for
a similar situation is to use a temporary table to build up the tree.
Something like this:



CREATE TABLE #MyTempTable (Level int, Code varchar(2))

INSERT INTO #MyTempTable (Level, Code)

FROM MyTable T1
WHERE NOT EXISTS (SELECT * FROM MyTable T2 WHERE T2.Child = T1.Parent)

WHILE (1=1)
BEGIN

  INSERT INTO #MyTempTable (Level, Code)

  FROM #MyTempTable T1
  INNER JOIN MyTable T2 ON T2.Parent = T1.Code


END

INSERT INTO MyNewTable (Level, Code)
SELECT Level, Code
FROM #MyTempTable

I just typed that in from scratch, so you might need to fiddle with it
a little, but hopefully it will give you the general idea. You just
keep joining back to your temporary table to get the next level from
your main table. Once you add that level you are then using that to
join and get the next level.

Looking back on it, you could actually do it all in place without the
temporary table. I used a temp table because I was just trying to get
the result set.

   Hope this helps,
       -Tom.



Quote:
> Thomas R.
>   thanks your responsion, but that are not  i  to want.
>   i  to want below Result ( that are the tree structure ).
>   please help me , thanks your!

> For Example:
> Before:
>   child       parent
>   ------     -------
>    A2           A1
>    A3           A2
>    A4           A1
>    A5           A2
>    A6           A3
>    A7           A4
>    A8           A6

> After:
>  parent      child
>  ------      ------
>   A1           A2
>   A1           A4
>   A2           A3
>   A2           A5
>   A3           A6
>   A4           A7
>   A6           A8

> Result:
> Level        Code
> ----        -----------
>  0            A1
>  1                A2
>  2                      A3
>  3                          A6
>  4                                A8
>  2                      A5
>  1                A4
>  2                      A7



> > I think that the SQL statement below will do what you're trying to
do,
> > but without using recursion. There is one difference though, in that
> > your code will not copy over items after a gap in the p_ia_no,
while my
> > code will. If you don't want those copied then don't use the code
below.

> > INSERT INTO tree
> >    ( gen_no,
> >      supper_ia_no ,
> >      p_ia_no,
> >      ia_no )

> >        p_ia_no,
> >        p_ia_no,
> >        ia_no
> > FROM mbr

> >    HTH,
> >     -Tom.



> > > How to use CURSOR for recurse ,

> > > I call  below Example  code, but to show up an error message:

> > > Line:2
> > > A cursor with the name 'cur_tree' alreadly exists.
> > > Continue ?

> > > please help me , thanks your!
> > > I am used MS-SQL 7.0

> > > Example  code:



> > > AS


> > > SET NOCOUNT ON

> > > DECLARE cur_tree CURSOR FOR
> > >   SELECT mbr.ia_no
> > >     FROM mbr

> > > OPEN cur_tree
> > >   FETCH NEXT FROM cur_tree


> > > begin
> > >      INSERT INTO tree
> > >          ( gen_no,
> > >            supper_ia_no ,
> > >            p_ia_no,
> > >            ia_no )





> > >   FETCH NEXT FROM cur_tree

> > > end
> > > CLOSE cur_tree
> > > DEALLOCATE cur_tree
> > > GO

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Sat, 05 Apr 2003 03:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Recurse subdirectories ...

2. recurse stored procedure, how?

3. Recurse call of Stored Procedure

4. Is this possible using T-SQL instead of using cursor

5. Record is used by another (109) error using REMOTE VIEW CURSORS

6. Cursor Cursor Cursor

7. Running loops using cursors...

8. Using a Variable as Column name in Select statement in a Cursor

9. Need your advice: Strange output using REPLACE function and CURSOR to loop variable

10. using cursors and tables in a UDF

11. Using variables in from clause of cursor...

12. Using Cursors


 
Powered by phpBB® Forum Software