Teradata Error: 3807 - Updating a View

Database
Enthusiast

Teradata Error: 3807 - Updating a View

Hi All

I am trying to update a view which is built on a table and I am receiving the following error. But my table and view is properly setup...

Before updating, I am setting the database to my default in my SQL assistant. This statement fails both when I run manually in SQL assistant and also when I run it in a stored procedure

Statement: Updating all values for a column

Update Viewname

SET COLUMNNAME = Value;

Error: 'UPDATE Failed. 3807: Object 'DATABASE._f does not exist.

Details Below

1. The View is built as Select All Columns from the base table. I checked the view definition for spelling mistakes and none are there.. I did check the table name and column names and everything looks fine.. The table is referred with an Alias '_f' and it looks fine everywhere too..

2. All the permissions are also set to the database, table and view correctly

3. I am able to Insert records into the View and eventually to the base table but just the update is failing - it says the 'UPDATE Failed. 3807: Object 'DATABASE._f does not exist.

4. I am able to do the following operations correctly.

    a. Select * from the view and check the data in the base table. Select * from the table and see the data inserted thru the view.

    b. Insert into the view which is successfully inserting into the table.

    c. Update the table directly. Update any column or the same column which error's out when doing thru the view. check the changes in the View 

    d. Replaced the view definition and it works fine. I also tried to drop the table and View and recreate them once.. No luck

5. I tried to modify my View DDL to SELECT * from <Databasename>.<tablename>. The UPDATE work FINE with this DDL

6. But if i change the DDL to have the column names in the select statement, i am getting the following Update Error..

Any help would really be helpful :) am really not sure what’s wrong here...

Thanks !

Anand

1 REPLY
Enthusiast

Re: Teradata Error: 3807 - Updating a View

Its very strange... I removed the Alias name from the Select statement in the View DDL and it worked...

Previously my DDL was like

REPLACE VIEW <VIEWNAME>

(Col1, Col2, Col3....) as

SELECT

(f.Col1, f.Col2, f.Col3...)

FROM <TableName> as f

Now

REPLACE VIEW <VIEWNAME>

(Col1, Col2, Col3....) as

SELECT

(Col1, Col2, Col3...)

FROM <TableName>

What difference did this make so i was not able to UPDATE the column

Can some one please help me understand why it was not working with the Alias Name in the select...

Thanks !

Anand