Conversion of Oracle's CONNECT BY...STARTING WITH to Teradata

Database

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

3 REPLIES

Re: Conversion of Oracle's CONNECT BY...STARTING WITH to Teradata

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

Re: Conversion of Oracle's CONNECT BY...STARTING WITH to Teradata

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

Teradata Employee

Re: Conversion of Oracle's CONNECT BY...STARTING WITH to Teradata