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 id From 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?
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 ALL
SELECT indirect.child_prty_id, indirect.parent_prty_id, 'N' FROM A_SRG_CUSTOMER_RCRSV direct, CFDW2_HFSEF_AVWS.A_CUST_PARENT indirect WHERE direct.child_prty_id = indirect.parent_prty_id );