Creating a view that uses "WITH RECURSIVE" getting error 6926

Database

Creating a view that uses "WITH RECURSIVE" getting error 6926

I have a recursive query that begins with the text "WITH RECURSIVE".

The query works fine. When I try to use the exact query within a view, while creating the view I receive an error that says "REPLACE VIEW Failed 6926: definitions, views, triggers or stored procedures". I simply prefix the query with this text: REPLACE VIEW DB.VIEW_NAME AS LOCKING ROW FOR ACCESS and I get the error.

I can successfullly get the recursive view created if I use this text instead: REPLACE RECURSIVE VIEW DB.VIEW_NAME, and if I also remove the syntax "WITH RECURSIVE".

Is there a way to create my recursive view keeping the syntax "WITH RECURSIVE" (since this is ansi standard)?

Thanks
1 REPLY
Junior Supporter

Re: Creating a view that uses "WITH RECURSIVE" getting error 6926

Teradata uses specific syntax for RECURSIVE VIEWS: "CREATE RECURSIVE VIEW rec_view (col1,col2…) AS..." You cannot simply wrap the WITH RECURSIVE query inside the CREATE/REPLACE VIEW AS...

HTH.

Cheers.

Carlos.