All Forums Database
alvinying 4 posts Joined 07/06
06 Jul 2006
Problem with Recursive View

I've created a recursive view (A) using the "REPLACE RECURSIVE VIEW" command. It's working fine.The problem comes when I tried to create a non-recursive view (B) as following:Create View B (id) As(Select idFrom A)When I execute this statement I keep getting the following error "6926: WITH [RECURSIVE] clause or recursive view is not supported within WITH [RECURSIVE] definitions, views, triggers or stored procedures. "It's known that one can't create recursive view which includes another recursive view in its definition. But what I was trying to create was a normal view with a recursive view embedded (and some other tables included once it's proven to be working ok). Anyone has any idea about how to make this work?Alvin

alvinying 4 posts Joined 07/06
21 Jul 2006

As a workaround I've created a volatile tableCreate volatile Table B_Temp As (Select *From B) With dataON COMMIT PRESERVE ROWS;And then created the View A based on the B_Temp table.This workaround is working ok so far. But I'm still looking for a more straightforward approach. Anybody with idea please share.

j355ga 100 posts Joined 12/05
22 Jul 2006

Could you include the DDL for the recursive view?


alvinying 4 posts Joined 07/06
24 Jul 2006

Below is the recursive view. It's created and running fine. The problem is with the outer view encapsulating this recursive view. Teradata simply won't let me create it (the outer view). Keep getting 6926 error metioned in the first post.REPLACE Recursive VIEW CFDW2_HFSEF_AVWS.A_SRG_CUSTOMER_RCRSV (child_prty_id, parent_prty_id, isparent) AS(SELECT root.child_prty_id, root.parent_prty_id, 'N'FROM CFDW2_HFSEF_AVWS.A_CUST_PARENT root Inner JOIN CFDW2_HFSEF_AVWS.CUSTOMER_PROSPECT PARENT_PROSPECT ON ( root.parent_prty_id = PARENT_PROSPECT.cust_prspct_prty_id) LEFT OUTER JOIN CFDW2_HFSEF_AVWS.A_CUST_SEG PARENT_CAT_CUST_SEG ON (PARENT_PROSPECT.cust_prspct_prty_id=PARENT_CAT_CUST_SEG.CUST_PRSPCT_PRTY_ID AND PARENT_CAT_CUST_SEG.PRIM_IND = 'Y' AND PARENT_CAT_CUST_SEG.SEG_TYPE_CD = '####')WHERE PARENT_CAT_CUST_SEG.SEG_CD = '##########'UNION ALLSELECT indirect.child_prty_id, indirect.parent_prty_id, 'N'FROM A_SRG_CUSTOMER_RCRSV direct, CFDW2_HFSEF_AVWS.A_CUST_PARENT indirectWHERE direct.child_prty_id = indirect.parent_prty_id);

You must sign in to leave a comment.