Archives of the TeradataForum

Message Posted: Thu, 08 May 2003 @ 16:13:22 GMT

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

Haven't tried this but,

If the position_code values possess the hierarchic rule (i.e. highest level = MIN or MAX number and there is a sequential direction toward lowest) then you might be able to use the OLAP rank function in a CASE statement like - "...when rank() over (partition by reports_to_position_code order by reports_to_position_code desc)=1"

Other than that, this is like a bill-of-materials" relation and this SP code could work - replacing the example BofM table with you org chart table.

/***** A simple Bill of Materials structure ************/
CREATE SET TABLE BofM
  ,NO FALLBACK
  ,NO BEFORE JOURNAL
  ,NO AFTER JOURNAL
   (
     Parent    INTEGER     NOT NULL
    ,Child     INTEGER
   ) PRIMARY INDEX ( Parent );

/******** Some Sample Data to work with ****************/
Insert into BofM Values (1,20)
;Insert into BofM Values (1,10)
;Insert into BofM Values (1,30)
;Insert into BofM Values (2,50)
;Insert into BofM Values (2,40)
;Insert into BofM Values (3,60)
;Insert into BofM Values (10,120)
;Insert into BofM Values (10,110)
;Insert into BofM Values (10,100)
;Insert into BofM Values (20,112)
;Insert into BofM Values (110,220)
;Insert into BofM Values (110,210)
;Insert into BofM Values (110,200)
;

/*****The Temporary Table to hold the result set *******/
CREATE SET GLOBAL TEMPORARY TABLE PCLevel
  ,NO FALLBACK
  ,No LOG
   (
     Level  INTEGER   NOT NULL
    ,Parent INTEGER   NOT NULL
    ,Child  INTEGER
   )PRIMARY INDEX ( Parent )
ON COMMIT PRESERVE ROWS;

/**** Returns all generations of children for        ***/
/**** a given parent into PCLevel.                   ***/
Replace Procedure GetChildren (Parent Integer)
 Begin
   Declare Level Integer;

   Set Level = 1;

   Del from PCLevel All;

   Insert Into PcLevel
      Sel :Level,Parent,Child
      From BofM
      Where Parent = :Parent;

  While Activity_Count > 0 Do
      Insert Into PCLevel
       Sel :Level + 1
          ,bom.Parent
          ,bom.Child
     From BofM    bom
         ,PCLevel pcl
     Where pcl.Level  = :Level
       and bom.Parent = pcl.Child;

     Set Level = Level+1;
    End While;

 End;

/*** Read Final Result of GetChildren ***/
sel * from pcLevel order by 1,2

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