Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Order and Level
From:   Michael Larkins

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>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016