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)
SELECT DATA_ELMNT_ID, DATA_ELMNT_NT_SEQ_NBR, DATA_ELMNT_NT_TXT, 1
WHERE (DATA_ELMNT_ID, DATA_ELMNT_NT_SEQ_NBR)
(SELECT DATA_ELMNT_ID, MIN(DATA_ELMNT_NT_SEQ_NBR) FROM TMM2.DATA_ELMNT_NT GROUP BY 1)
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
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
Explanation: A WITH clause or a recursive view is
being used inside a WITH definition, view, or stored
You seem to use a WITH in the DATA_ELMNT_NT view.