In some cases, we write database name explicitly in our queries however most of the time we rely on default database to select the view/table used in queries. However do you know in Teradata , you can have more than one default database for your query.
Let’s look at a query below :
DATABASE DB_A;
SELECT a.col1 FROM table_a a
INNER JOIN DB_B.table_b b
ON a.col1 = b.col1
INNER JOIN table_c c
ON b.col1 = c.col1;
The above query will fail with error:
E(3806):Table/view/trigger name 'table_a' is ambiguous.
This is because table_a exists in DB_A as well as in DB_B. So what exactly is causing this error ?
Before the query starts, default database is set to DB_A.
Now in the query when we explicitly mention DB_B, it is also added in default database list. So now TD will search for db objects used in query in both the databases i.e. search for table/views in DB_A and in DB_B.
Since table_a exists @ both places, hence the error.
Possible solution:
a) Either write database name for all the tables/views explicitly if it resides in both databases.
b) Create the one which is in other database as derived table:
SELECT a.col1 FROM table_a a
INNER JOIN (SEL * FROM DB_B.table_b) b
ON a.col1 = b.col1
INNER JOIN table_c c
ON b.col1 = c.col1;