Archives of the TeradataForum

Message Posted: Thu, 24 Jan 2002 @ 19:02:25 GMT

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

Karen,

Assuming (big assumption) you know what the maximum number of levels are, I would recommend the follow format (if you plan on building a view or storing this information in a 'Staging' table with statistics for faster reporting):

- note: assuming the parent of top level 'is null'

(I went ahead and added a 5th level, like you user will as soon as you create this)

Select    a.Chld_Cd as Level1
     b.Chld_Cd as Level2
     c.Chld_Cd as Level3
     d.Chld_Cd as Level4
     e.Chld_Cd as Level5
From StructureTable a
     left outer join Structure Table b
          on   a.Chld_Cd =    b.Prnt_Cd
          and  a.Prnt_Cd is   null
          left outer join Structure Table c
               on   b.Chld_Cd =    c.Prnt_Cd
               left outer join Structure Table d
                    on   c.Chld_Cd =     d.Prnt_Cd
                    left outer join Structure Table e
                         on   d.Chld_Cd  =    e.Prnt_Cd
-- Group By 1,2,3,4,5 (if many to many relationships exist)

Hope this helps,

Claybourne Barrinea

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