You are currently viewing [SOLVED] Teradata Error 3807 SQLState 42s02 Object ‘XYZ’ does not exists – easy solution!
Could You Please Share This Post? I Appreciate It And Thank YOU! :) Have A Nice Day!
4.9
(1033)

In Teradata we can encounter an error [ Teradata Error 3807 SQLState 42s02 ] [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 -> Teradata Error 3807 SQLState 42s02

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 -> Teradata Error 3807

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 => SQLState 42s02

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

That’s all about: Teradata Error 3807 SQLState 42s02 Object ‘XYZ’ does not exists!

Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!

BigData-ETL: image 7YOU MIGHT ALSO LIKE

How useful was this post?

Click on a star to rate it!

Average rating 4.9 / 5. Vote count: 1033

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?