17.10 - Fully Qualified Object Names - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)

A fully qualified object name includes the names of all parent objects up to the level of the containing database. A common use case is a fully qualified column name, which consists of a database name, table name, and column name.

[ [ database_name. ] table_name ] column_name
database_name
A qualifying name for the database in which the table and column being referenced is stored.
Depending on the ambiguity of the reference, database_name can be required.
table_name
A qualifying name for the table in which the column being referenced is stored.
Depending on the ambiguity of the reference, table_name can be required.
column_name
One of the following:
  • The name of the column being referenced
  • The alias of the column being referenced
  • The keyword PARTITION

Name Resolution Rules and the Need to Fully Qualify a Name

  • Name resolution is performed statement by statement.
  • An ambiguous unqualified name returns an error to the requestor.
  • When an INSERT statement contains a subquery, names are resolved in the subquery first.
  • Names in a view are resolved when the view is created.
  • Names in a macro data manipulation statement are resolved when the macro is created.
  • Names in a macro data definition statement are resolved when the macro is performed using the default database of the user submitting the EXECUTE statement.

    Therefore, you should fully qualify all names in a macro data definition statement, unless you specifically intend for the user’s default to be used.

  • Names in stored procedure statements are resolved either when the procedure is created or when the procedure is executed, depending on whether the CREATE PROCEDURE statement includes the SQL SECURITY clause and which option the clause specifies.

    Whether unqualified object names acquire the database name of the creator, invoker, or owner of the stored procedure also depends on whether the CREATE PROCEDURE statement includes the SQL SECURITY clause and which option the clause specifies.