Resolving Table References in Volatile Tables | VantageCloud Lake - Resolving Table References - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
When encountering an unqualified table name, the system looks for a table by that name in all of the following databases:
  • The default database
  • Any databases referenced by the SQL request
  • The logon user database for a volatile table by that name

The search must find the table name in only 1 of those databases. The database returns the “ambiguous table name” error message if the table name exists in more than 1 of those databases.

The following examples show the consequences of using unqualified table names with volatile tables.
  • If the unqualified table name is a volatile table in the logon user database, but not a table in any other database, the reference defaults to the volatile table in the logon user database.
    .LOGON u1

CREATE VOLATILE TABLE volatile_table_1 (
  f1 INTEGER,
  f2 INTEGER)
ON COMMIT PRESERVE ROWS;

DATABASE db1;

INSERT volatile_table_1 (1,1);


    The reference here is to the volatile table, u1.vt1, in the logon user database, not the current default database, db1.

  • If the unqualified table name is a volatile table in the logon user database and also a table in another database, an ambiguous table name error results, for example:
    .LOGON u1

CREATE VOLATILE TABLE volatile_table_1 (
  f1 INTEGER,
  f2 INTEGER)
 ON COMMIT PRESERVE ROWS;

 DATABASE db1;

 CREATE TABLE volatile_table_1 (
   g1 INTEGER,
   g2 INTEGER);

INSERT INTO volatile_table_1 VALUES (1,1);

    The INSERT operation returns an error to the requestor because a table named volatile_table_1 already exists in the logon user database, u1.

  • If the unqualified table name matches a volatile table and a permanent table in the logon user database, the reference defaults to the volatile table.

    This can occur if a user creates a volatile table in session 1 and then creates a permanent table by the same name in the user database in session  2. An unqualified reference to the table name in session 1 defaults to the volatile table. Session 2 does not see the volatile table in session 1, so an unqualified reference to the table name in session 2 defaults to the permanent table.