In Teradata we can encounter an error [Error 3807] [SQLState 42s02] Object ‘XYZ’ does not exists or Failed [3807: 42s02] Object ‘XYZ’ does not exists, where ‘XYZ’ is the name of the object we specified in the query . The cause of this error can be diagnosed very quickly. In this short post I will explain the reason for the error and how to solve it.
Problem
The problem may occur during many DDL / DML operations run on a database. The below is the simplest example:
SELECT TOP 10 * FROM XYZ;
Cause
There are several reasons for this issue:
- you are trying to run a DML / DDL operation on a table that exists in another database
- you are trying to run a DML / DDL operation on a table that does not exist
- you are trying to run a SELECT / INSERT / UPDATE / DELETE etc. operation on a table that you do not have access to
Solution
You are trying to run a DML / DDL operation on a table that exists on a different database
If you execute operations on Teradata without specifying the database, all operations are execute on the default database for the user. Let’s check what database you are currently using:
SELECT DATABASE;
Next, let’s check in which database the table you are looking for is available:
SELECT DataBaseName, TableName, CreateTimeStamp, LastAlterTimeStamp FROM dbc.TablesV WHERE TableName='<your table name>';
If the table you need is available in another database, change the default database using a simple command:
DATABASE <your database name>;
or specify database in query:
SELECT TOP 10 * FROM <your database name>.XYZ;
You are trying to run a DML / DDL operation on a table that does not exist
Please check if the table you are referring exists in your database. You can check it with the following query:
SELECT DataBaseName, TableName, CreateTimeStamp, LastAlterTimeStamp FROM dbc.TablesV WHERE DataBaseName='<your database name>' AND TableName='<your table name>';
You are trying to run a SELECT / INSERT / UPDATE / DELETE etc. operation on a table that you do not have access to
In Teradata, you can quickly check which objects you have access to. To do this, use two views from the dbc database: AllRightsV and UserRightsV. The AllRightsV view stores permissions for all system users:
SELECT * FROM dbc.AllRightsV WHERE UserName='<your user name>';
Whereas UserRightsV stores all the permissions granted to your user:
SELECT * FROM dbc.UserRightsV WHERE UserName='<your user name>';