Archives of the TeradataForum

Message Posted: Wed, 02 Nov 2005 @ 15:19:12 GMT

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

This may work using pure SQL:

You may have to test performance though because of the enforced product LIKE-join

     WITH RECURSIVE ALL_HIER
     ( Level
      ,ID
      ,ID_CHAR
      ,PARENT_ID
      ,HIER_HIST
      ) as
     (
     select
       0
      ,id
      ,id_char (format'9(10)') (CHAR(10))
      ,parent_id
      ,'' (VARCHAR(250))
     from lu_hier
     where parent_id is null
     UNION ALL
     select
       p.Level + 1
      ,c.id
      ,c.id (format'9(10)') (CHAR(10)) as c_id_char
      ,c.prod_hier_code
      ,c.prod_hier_desc
      ,c.parent_prod_hier_code
      ,p.HIER_HIST||'-'||c_id_char||'-'
     from ALL_HIER p
     inner join lu_hier c
        on p.id = c.parent_id
     )
     sel * from ALL_HIER r1
     where r1.hier_hist NOT like '%-'||1
     /* replace here the id you would like to exclude or join against a
     lookup table */
     (FORMAT '9(10)') (CHAR(10))||'-%'
     order by 3,1
     ;

Regards,

Georg

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