All Forums Database
Hari Srinivasan 3 posts Joined 06/06
22 Aug 2006
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,22,32,44,54,6where the first column being the parent and the second- its child.I need 1,21,31,41,51,62,32,42,52,64,54,6Can anybody suggest a method to achieve this using ANSI/teradata SQL.Thanks,Hari

vinod_sugur 22 posts Joined 04/05
28 Aug 2006

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_RecursiveValues(1);INSERT INTO Test_RecursiveValues(2);INSERT INTO Test_RecursiveValues(3);INSERT INTO Test_RecursiveValues(4);INSERT INTO Test_RecursiveValues(5);INSERT INTO Test_RecursiveValues(6);WITH RECURSIVE temp_table (A,B) AS( SELECT root.I A, root.I B FROM Test_Recursive rootUNION ALLSELECT X.A,Y.IFROM temp_table X , Test_Recursive YWHERE X.A > Y.I)SELECT * FROM temp_table ORDER BY A,B;Please try out the above query and let me know the results.

You must sign in to leave a comment.