CONNECT BY.. PRIOR Equivalent in Teradata?

Database

CONNECT BY.. PRIOR Equivalent in Teradata?

Hello Gurus,

Is there an equivalent statement for CONNECT BY..PRIOR in Oracle. I'm trying to simulate a hierarchy in Teradata SQL and i'm using the WITH RECURSIVE query. It is not solving my requirement as it gives me

1,2
2,3
2,4
4,5
4,6

where the first column being the parent and the second- its child.

I need

1,2
1,3
1,4
1,5
1,6
2,3
2,4
2,5
2,6
4,5
4,6

Can anybody suggest a method to achieve this using ANSI/teradata SQL.

Thanks,
Hari
1 REPLY
Enthusiast

Re: CONNECT BY.. PRIOR Equivalent in Teradata?

Hi,

I am sorry i could not test the below query as i don't have the V2R6 box avaliable in my environment.

Create Table Test_Recursive
(I Integer);

INSERT INTO Test_Recursive
Values(1);
INSERT INTO Test_Recursive
Values(2);
INSERT INTO Test_Recursive
Values(3);
INSERT INTO Test_Recursive
Values(4);
INSERT INTO Test_Recursive
Values(5);
INSERT INTO Test_Recursive
Values(6);

WITH RECURSIVE temp_table (A,B) AS
( SELECT root.I A, root.I B
FROM Test_Recursive root
UNION ALL
SELECT X.A,Y.I
FROM temp_table X , Test_Recursive Y
WHERE X.A > Y.I
)
SELECT * FROM temp_table ORDER BY A,B;

Please try out the above query and let me know the results.