15.00 - Resolving Table References - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Resolving Table References

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. Teradata 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.
  • Thus,

         .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, the reference is ambiguous and 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 aborts and 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 applies to both a volatile table in the logon user database and to a permanent table in the logon user database (that is, the logon user database contains both a volatile table named table_name and a permanent table named table_name.), the reference defaults to the volatile table.
  • This could 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.