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>';

If you enjoyed this post please leave the comment below or share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!