Resolving Table References in Volatile Tables | CREATE TABLE | Teradata Vantage - Resolving Table References - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
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.