REPLACE RECURSIVE VIEW - ERROR 6926

Database

REPLACE RECURSIVE VIEW - ERROR 6926

Hi everyone,

I am trying to create a recursive view and get the ERROR

REPLACE VIEW Failed. 6926:  definitions, views, triggers or stored procedures. 

Added the code, has anybody any suggestions?

Thank you in advance.

REPLACE RECURSIVE VIEW TMM2.ELMNT_NOTES (DATA_ELMNT_ID, DATA_ELMNT_NT_SEQ_NBR, DATA_ELMNT_NT_TXT, SEQ_NUM)

AS

(

SELECT DATA_ELMNT_ID, DATA_ELMNT_NT_SEQ_NBR, DATA_ELMNT_NT_TXT, 1

FROM DATA_ELMNT_NT

WHERE (DATA_ELMNT_ID, DATA_ELMNT_NT_SEQ_NBR)

IN

(SELECT DATA_ELMNT_ID, MIN(DATA_ELMNT_NT_SEQ_NBR) FROM TMM2.DATA_ELMNT_NT GROUP BY 1)

UNION ALL

SELECT den.DATA_ELMNT_ID, den.DATA_ELMNT_NT_SEQ_NBR,

CASE WHEN en.SEQ_NUM < 2 THEN '1) ' || en.DATA_ELMNT_NT_TXT || ', ' || TRIM(en.SEQ_NUM + 1) || ' ) ' || den.DATA_ELMNT_NT_TXT

ELSE en.DATA_ELMNT_NT_TXT || ', ' || TRIM(en.SEQ_NUM + 1) || ' ) ' || den.DATA_ELMNT_NT_TXT END, en.SEQ_NUM + 1 AS SEQ_NUM2

FROM TMM2.ELMNT_NOTES en

INNER JOIN TMM2.DATA_ELMNT_NT den

ON en.DATA_ELMNT_ID = den.DATA_ELMNT_ID

WHERE SEQ_NUM2 = en.SEQ_NUM + 1 AND en.DATA_ELMNT_NT_SEQ_NBR < den.DATA_ELMNT_NT_SEQ_NBR

);

1 REPLY
Senior Apprentice

Re: REPLACE RECURSIVE VIEW - ERROR 6926

You (or probably the forum software due to square brackets) didn't show the full error message:

6926 WITH (RECURSIVE) clause or recursive

view is not supported within WITH

(RECURSIVE) definitions, views, triggers or

stored procedures.

Explanation: A WITH clause or a recursive view is

being used inside a WITH definition, view, or stored

procedure.

You seem to use a WITH in the DATA_ELMNT_NT view.

Dieter