Archives of the TeradataForum

Message Posted: Sat, 07 Aug 2004 @ 19:27:23 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

The DEMO CD has a nice example of extracting a Bill Of Materials from a Parts database using a recursive stored procedure and a temp table where it loops until all the levels of the Part expolosion have been covered. you might be able to adapt it

     CREATE SET GLOBAL TEMPORARY TABLE manufacturing.PCLevel ,NO FALLBACK ,
          NO LOG
          (
           Level INTEGER NOT NULL,
           Parent INTEGER NOT NULL,
           Child INTEGER,
           Name CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC)
     PRIMARY INDEX ( Parent )
     ON COMMIT PRESERVE ROWS;

     Replace Procedure GetChildren(Parent Integer)
       Begin
         Declare Level Integer;
         Set Level = 1;
         Del from PCLevel All;
         Insert Into PcLevel
            Sel :Level,Parent,Child,Name
            From BofM
            Where Parent = :Parent;
        While Activity_Count > 0 Do
            Insert Into PCLevel
             Sel :Level + 1
                ,bom.Parent
                ,bom.Child
                ,bom.Name
           From BofM    bom
               ,PCLevel pcl
           Where pcl.Level  = :Level
             and bom.Parent = pcl.Child;
           Set Level = Level+1;
          End While;
       End;

You submit a

     "Call manufacturing.GetChildren(1234);"

followed by

     "select * from PCLevel
     order by Level, ..."

	<Prev	Next>		<<First	<Prev	Next>	Last>>