Problem with Recursive View

Database

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 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?

Alvin
3 REPLIES

Re: Problem with Recursive View

As a workaround I've created a volatile table

Create volatile Table B_Temp As (
Select *
From B
) With data
ON 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.
Teradata Employee

Re: Problem with Recursive View

Could you include the DDL for the recursive view?

Re: Problem with Recursive View

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
);