Recursive Order by

2010-03-23T06:45:54Z

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

UPDATE from recursive query

2009-11-11T13:21:57Z

Is it possible to do an update from a recursive query in one SQL statement without using a temporary table?

I am trying to set the very top boss id for each employee and I get the error

3707: Syntax error, expected something like a name or '(' between '(' and the 'with' keyword.

CREATE TABLE EMP(ID INTEGER, BOSS_ID INTEGER, TOP_BOSS_ID INTEGER);
INSERT INTO EMP VALUES(1,NULL,NULL);
INSERT INTO EMP VALUES(2,NULL,NULL);
INSERT INTO EMP VALUES(3,1,NULL);
INSERT INTO EMP VALUES(4,2,NULL);
INSERT INTO EMP VALUES(5,1,NULL);
INSERT INTO EMP VALUES(6,2,NULL);
INSERT INTO EMP VALUES(7,3,NULL);

UPDATE EMP
FROM
(
  WITH RECURSIVE EH(ID,BOSS_ID,SUPER_BOSS_ID,LEVEL) AS
  (
   SELECT ID,BOSS_ID,BOSS_ID,0
   FROM EMP
   UNION ALL
   SELECT E.ID,E.BOSS_ID,P.BOSS_ID,E.LEVEL+1
   FROM EH E JOIN EMP P ON E.SUPER_BOSS_ID = P.ID
  )
  SELECT * FROM EH
) X
SET TOP_BOSS_ID = X.BOSS_ID
WHERE X.SUPER_BOSS_ID IS NULL
AND X.BOSS_ID IS NOT NULL
;

recursive Questions - TeradataQuestions

Recursive Order by

UPDATE from recursive query