All Forums Database
Georg Fischer 1 post Joined 07/11
13 Dec 2011
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

);

 

dnoeth 4628 posts Joined 11/04
14 Dec 2011

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

Dieter

You must sign in to leave a comment.