All Forums Database
20 Jul 2010
Volatile table V/S Perminent table

I have created a volatile table with table name TEMP1
And a permanent table DATABASENAME.TEMP1

When I do a show or help or select on table TEMP1
I am able to fetch volatile table TEMP1 results as expected

But when I am doing an update or insert like

Update DATABASENAME.TABLE2
SET PT_COL1 = VT_COL1
FROM TEMP1

I am getting an ambiguous error on the TABLE , ambiguous between permanent table DATABASENAME.TEMP1 and volatile table TEMP1
This error is resolved only if I drop the DATABASENAME.TEMP1 or reframe my update statement

In a session where I have create the VT and using it in a SQL which is having update or insert with other databasename.tablenames Why the TD is first pointing to the database name given in the SQL even if I haven’t specified the DATABASENAME qualifier

dnoeth 4628 posts Joined 11/04
20 Jul 2010

If you use a qualified tablename, the parser adds that databasename to the list of default databases for that query.

Now you got two default databases, your "default" default DB and DATABASEME and both got the same table1.

The solution for that kind of error is to qualify *all* objects, thus qualify the VT with your username.

Dieter

Dieter

suresh1219 2 posts Joined 07/10
21 Jul 2010

hi any body is there for help me in teradata

vishu 2 posts Joined 04/13
24 Apr 2013

HI all,
i am very new to teradata, please help me with your valuable suggestions
i want to know in which case we have to choose a (volatile table,global temp table,derived table)
please help me

KS42982 137 posts Joined 12/12
24 Apr 2013

These all tables that you mentioned keep the data temporarily with different purpose. If you are looking to store the data permanently then you should not use either of these kind. Let us know what is your purpose and will guide what would be the best for you.

You must sign in to leave a comment.