Archives of the TeradataForum
Message Posted: Fri, 04 Jan 2008 @ 10:36:28 GMT
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,
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|