Archives of the TeradataForum

Message Posted: Fri, 04 Jan 2008 @ 10:36:28 GMT

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

Hello yogendra-wasudev.x.gijare:

Some of your sort sequences are a bit strange and not obvious as to how or why you would want them sorted that way. If mine is not exactly what you are looking for in the sequence, you can work on that portion yourself; I have given you everything else.

Regardless, a recursive table is the best way to get what you are looking for in the output. Then, a row_number/over will provide the numbering. This also assumes that you are on V2R6 because of the recursive table being used.

     with recursive prod_dt (main_prod, prod, subprod,lvl) as
     (sel prod_cd, prod_cd, paren_prod_cd, 1 from prod_tbl where paren_prod_cd = 0
        union all
        sel main_prod, prod_cd, paren_prod_cd, lvl+1
         from prod_dt join prod_tbl on prod=paren_prod_cd)
     sel row_number() over (order by main_prod, lvl) as Ord, prod, subprod,lvl
     from Prod_dt;

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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