Skip to content

ERROR: Table name “Database.Schema.Table” not listed in the FROM-clause

SCENARIO:
Below are the objects in different databases. Everything is under ADMIN schema
DB1 -> Table1
DB2 -> Synonym1 created on DB1.ADMIN.Table1
DB3 -> View1 created on DB2.ADMIN.Synonym1

You have already migrated Table1 in DB1 and View1 in DB2 to new environment by generating dll in server1 using nz_ddl_table and nz_ddl_synonym

ERROR:
Now when you try to generate ddl for View1 and run it on DB3 in new environment you will get error:

***** Creating view: “View1”
ERROR: Table name “DB2.ADMIN.Table1” not listed in the FROM-clause

You will get this error even if the table name exists in from clause

CAUSE:

Here is the script that sysetem will generate with nz_ddl_view (which looks correct)

CREATE or replace VIEW V_Table1
(
cl1,
cl2
) AS
SELECT
DB2.ADMIN.Table1.cl1,
DB2.ADMIN.Table1.cl2
FROM
DB2.ADMIN.Table1 ;

But when you run this you will get above error. Even when you try to just run the select part in server1 it will fail with same error which you got during creation on new environment.

RESOLUTION:
Not sure on why Netezza behaves abnormally but if you remove the Databasename.SchemaName.TableName from columns in select query it will run successfully.

Below query will run fine:

CREATE or replace VIEW V_Table1
(
cl1,
cl2
) AS
SELECT
cl1,
cl2
FROM
DB2.ADMIN.Table1 ;