3806: Table/view/trigger name is ambiguous

Database
Enthusiast

3806: Table/view/trigger name is ambiguous

All,

One of my bteq failed due to the error :"3806: Table/view/trigger name tbl1  is ambiguous".

My script looks like this:

==================<bteq starts>===================

create volatile table tb1 as( sel * from db.tbl1 )with data ;     ----- 1 st statement

create volatile table tb2 as ( sel * from db.tbl2) with data ;    ------ 2 nd statement

create volatile table tb3 as (sel * from db.tbl3 )with data ;     ------  3 rd statement

create volatile table tbl4                                                           ----- 4th statement

as ( sel * from tbl1 join db.tbl5 on tbl1.col1=tbl4.col1) with data;

===================<bteq ends>===================

Issue: My statement #4 in the bteq failed due to above mentioned ambiguous error.

i was wondering 'tbl1' is volatile table available only to my session, how will it result in ambiguous error.

Any help to fix this pls.

Thanks

Sen

14 REPLIES
Enthusiast

Re: 3806: Table/view/trigger name is ambiguous

Check your default database, there might be an object with the same name.
Junior Supporter

Re: 3806: Table/view/trigger name is ambiguous

>>"create volatile table tbl4 as

( sel * from tbl1 join db.tbl5 on tbl1.col1=tbl4.col1) with data;"

You are making the join between tbl1 and tbl4 instead of tbl5.

HTH.

Cheers.

carlos.

Enthusiast

Re: 3806: Table/view/trigger name is ambiguous

@TeraAbe, Its exactly the same reason. There was a table with the same name exists in my default database.

@Carlos, Thats a typo as i cannot paste the complete query here. Sorry about that!

Issue resolved now. Thanks a lot for you both!

Thanks

Sen

Enthusiast

Re: 3806: Table/view/trigger name is ambiguous

All,

One of my bteq failed due to the error :"3806: Table/view/trigger name T1  is ambiguous".

Query

DATABASE DB1;

SEL A,B

FROM T1 S,DB2.T2 T

WHERE S.PTY_ID = T.PTY_ID;

Now, for your information, T1 and T2 are two views. Both are present in DB1 as well as DB2. My understanding is once I set default database as DB1, if I present any unqualified view as T1, that must be taken from DB1 and if I qualify any view by any specific database(DB2.T2), that is going to be taken from DB2. But my query failed with above error. To just run it succesfully, I changed the query as below.

DATABASE DB1;

SEL A,B

FROM DB1.T1 S,DB2.T2 T

WHERE S.PTY_ID = T.PTY_ID;

AND IT RAN FINE. I was happy but unhappy too as I couldn't understand why. Could you please explain..

Junior Contributor

Re: 3806: Table/view/trigger name is ambiguous

There can be more than one default database :-)

When you use a qualified tablename like DB2.T2DB2 will be added to the "search path" and now the parser finds T1 in both DB1 and DB2 resulting in a 3806.

The recommendation is to fully qualified names for all objects or no qualification at all.

Dieter

Enthusiast

Re: 3806: Table/view/trigger name is ambiguous

Thanks a lot Dieter for your answer.

As I am a learner everyday, trying to dig into somewhat more. 

"now the parser finds T1 in both DB1 and DB2 resulting in a 3806."

But my query is why the parser will try to search T1 in both. Say, the same query is written in totally unqualified fashion like below.

DATABASE DB1;

SEL A,B

FROM T1 S,T2 T

WHERE S.PTY_ID = T.PTY_ID;

In this case, Parser will pickup views from DB1 without a doubt rite even if tables are also present in DB2?

Does this signify that, when it checks the 'from clause' and finds one table qualified and another unqualified, it starts searching unqualified one in both DBs i.e. the default one and qualified tables' DB (DB2)?

Junior Contributor

Re: 3806: Table/view/trigger name is ambiguous

Yes and Yes :-)

Dieter 

Enthusiast

Re: 3806: Table/view/trigger name is ambiguous

@dieter.. Thanks a Ton

Enthusiast

Re: 3806: Table/view/trigger name is ambiguous

I know this is a little late, However, I thought I would ask anyway.

What if you accidentally or purposfully make a volatile table the same name as a table in your default database?  How can you fully Qualify the Volatile table when you want to use it? Example:

DB.MEMBER

create volatile table MEMBER as () with data on commit preserve rows;

select * from MEMBER (SELECT Failed.  [3806] Table/view/trigger name 'MEMBER' is ambiguous.)

How do you select from the new volatile table explicitly? 

Select * from Volatile.MEMBER???

Thanks in advance.