ISSUE:
You may see this error when trying to query a view or create a view on another view.
ERROR: Recursion detected in view expansion
CAUSE:
Netezza does not allow creating a view pointing to itself or any object with same name in a database. This is a departure if you are coming from other databases such as Oracle or Teradata. But there is a bug in Netezza which allows you to create a view pointing to itself if the view already exists.
To resolve the issue you have to recreate a view with correct definition for the query to run successfully.
MORE EXPLANATION:
1) If you create a brand new view pointing to itself you will get error:
TESTDB.ADMIN(ADMIN)=> create or replace view MyView1 as select * from MyView1; ERROR: relation does not exist TESTDB.ADMIN.MYVIEW1
2) Now, if you create this view as valid view it will run fine:
TESTDB.ADMIN(ADMIN)=> create or replace view MyView1 as select * from T1; CREATE VIEW
3) Run select on view and it will run fine:
TESTDB.ADMIN(ADMIN)=> select * from MyView1; <Runs Successfully>
4) Now, again try to recreate this view as we tried to create in step 1. It will work fine:
TESTDB.ADMIN(ADMIN)=> create or replace view MyView1 as select * from MyView1; CREATE VIEW
5) Run select on view and it will throw error:
TESTDB.ADMIN(ADMIN)=> select * from MyView1;
ERROR: Recursion detected in view expansion