Question

0

Hi,

I need help in With Recursive query order by,

In Oracle, the query has rownum in the select list for connect by start with hierarchical view and was ordered by with rownum. So, the data displayed according to rownum based on levels. For Eg:

select a.mylevel, a.company_id,a.parent_company_id  
from ( select level mylevel, company_id, parent_company_id, rownum bbb 
    from tablea 
    connect by parent_company_id = prior company_id 
    start with company_id = 1111
    and parent_company_id is null ) a   
    order by a.bbb

Data:
Level   Company_ID     Parent_Company_id
1        1111           ?
1        1111           ?
2        1211           1111
3        1311           1211
3        1312           1211
4        1411           1312
2        2222           1111
3        2322           2222

Here the levels are displayed based on rownum like 1-2-2-4 again 2 - 3. Could you please help me how to implenment the same rownum in Teradata using With REcursive.

Teradata query:

WITH RECURSIVE company_recursive(mylevel, company_id,parent_company_id) as
(Select (1(INTEGER))as mylevel,a.company_id, a.parent_company_id
 From sharpbvdb_ts3.csm_csc_company_hier a
 Where 1 = 1 
 AND a.company_id = '1111'
 UNION ALL
  Select cr.mylevel+1 mylevel,a1.company_id, a1.parent_company_id
   FROM sharpbvdb_ts3.csm_csc_company_hier a1,
        company_recursive cr
 Where 1 =1 
 AND cr.company_id = a1.parent_company_id
 )
 Select mylevel , cr.company_id ,parent_company_id 
 from company_recursive cr 
 order by 1,2,3

With this query the data is displaying as

Data:
Level   Company_ID     Parent_Company_id
1        1111           ?
1        1111           ?
2        1211           1111
2        2222           1111
3        1311           1211
3        1312           1211
4        1411           1312

I want to display as 1-2-3-4-5 etc. Please help me in this.

Regards, TDHelp

flag	asked Mar 23 at 6:45 TDHelp 15●2

Rob Paller · Answer 1 · 2010-03-23 14:21:28Z UTC

Running on Teradata 12 here is how I accomplished what you wanted to do with the Row_Number() window aggregate function:

SQL

CREATE VOLATILE TABLE MyCompany, NO LOG
  (Company_id SMALLINT NOT NULL,
   Parent_Company_Id SMALLINT
  )
PRIMARY INDEX (Company_Id)
ON COMMIT PRESERVE ROWS;

INSERT INTO MyCompany VALUES (1111, NULL);
INSERT INTO MyCompany VALUES (1211, 1111);
INSERT INTO MyCompany VALUES (2222, 1111);
INSERT INTO MyCompany VALUES (1311, 1211);
INSERT INTO MyCompany VALUES (1312, 1211);
INSERT INTO MyCompany VALUES (1411, 1312);



WITH RECURSIVE company_recursive(mylevel, company_id,parent_company_id) as
(Select (1(INTEGER))as mylevel,a.company_id, a.parent_company_id
 From MyCompany a
 Where 1 = 1 
 AND a.company_id = '1111'
 UNION ALL
  Select cr.mylevel+1 mylevel,a1.company_id, a1.parent_company_id
   FROM MyCompany a1,
        company_recursive cr
 Where 1 =1 
 AND cr.company_id = a1.parent_company_id
 )
 Select mylevel 
      , cr.company_id 
      , parent_company_id 
      , Row_Number()
        OVER(order by mylevel, company_id) AS RowNum
 from company_recursive cr 
 order by 1,2,3

Results:

mylevel company_id  parent_company_id   RowNum
1           1111            ?                 1
2           1211            1111              2
2           2222            1111              3
3           1311            1211              4
3           1312            1211              5
4           1411            1312              6

sam · Answer 2 · 2010-03-24 17:46:46Z UTC

0

Hi I checked the data .Here we need the parent child relation here .The rownum logic applied to the resultset .Can anybody tell how to acheive parent child relationship in a recursive query

answered Mar 24 at 17:46

sam
11●1

Recursive Order by

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged recursive or ask your own question.

Welcome to TeradataQuestions

Useful links