Check your default database, there might be an object with the same name.
>>"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.
@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
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..
CHEERS!
Dipanjan Mukherjee
Wipro Technologies
Technical Lead
There can be more than one default database :-)
When you use a qualified tablename like DB2.T2, DB2 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
Dieter
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)?
CHEERS!
Dipanjan Mukherjee
Wipro Technologies
Technical Lead
Yes and Yes :-)
Dieter
Dieter
@Dieter.. Thanks a Ton
CHEERS!
Dipanjan Mukherjee
Wipro Technologies
Technical Lead
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.
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