All Forums Database
CCSlice 36 posts Joined 03/14
10 Apr 2014
Conversion of Oracle's CONNECT BY...STARTING WITH to Teradata

Hi,
I am looking for the equivalent function to Oracle's CONNECT BY clause to generate an organization hierarchy chart....

SELECT DISTINCT
 ORGUNIT_CODE
 FROM ORG_UNT
 WHERE ID IN 
                          ( 
                                                     SELECT DISTINCT CHILD_ID
                                                     FROM ORG_LNK
                                                     CONNECT BY PRNT_ID = PRIOR CHILD_ID 
                                                     START WITH PRNT_ID
   IN (SELECT DISTINCT
          A.ORG_ID

FROM

          GODRR A

INNER JOIN

          USR_GRPS B
ON B.GRP_ID = A.GRP_ID
INNER JOIN
          USERS U
ON U.USRID = B.USR_ID

INNER JOIN
          GROUPS G
ON G.GRP_ID = A.GRP_ID
WHERE
U.EXT_ID LIKE '529435489%'
AND
                      TRIM(UPPER(G.NAME)) != 'REPORTING'
)
UNION 
                                               SELECT DISTINCT PRNT_ID 
                                               FROM ORG_LNK WHERE  PARNT_ID
 IN (SELECT DISTINCT
          A.ORG_ID

FROM
          GODRR A
INNER JOIN
          USR_GRPS B
ON B.GRP_ID = A.GRP_ID
INNER JOIN
          USERS U
ON    U.USRID = B.USR_ID
INNER JOIN
          GROUPS G
ON G.GRP_ID = A.GRP_ID
WHERE
          U.EXT_ID LIKE 'User id%'
AND
          TRIM(UPPER(G.NAME)) != 'REPORTING'

 ));

I am new to Teradata and I am finding out these subtleties in syntax challenging and I would appreciate your help.
 
Many thanks,
CCSlice

Qaisar Kiani 337 posts Joined 11/05
10 Apr 2014

You can use RECURSIVE queries to get this sort of result.

CCSlice 36 posts Joined 03/14
11 Apr 2014

Recursive queries?  Where can I find this on here?  Thanks.

Adeel Chaudhry 773 posts Joined 04/08
11 Apr 2014

You can learn about recursive views from below link:
 
http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1144_111A/Create_Procedure-Syntax.023.56.html
 
Or can learn about RECURSIVE queries from below:
 
http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1146_111A/ch01.032.061.html

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.