17.10 - Unqualified 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)

An unqualified object name is a reference to an object such as a table, column, trigger, macro, or stored procedure that does not include parent objects. For example, the WHERE clause in the following statement uses “DeptNo” as an unqualified column name:

SELECT *
FROM Personnel.Employee
WHERE DeptNo = 100 ;

Unqualified Column Names

You can omit database and table name qualifiers when you reference columns as long as the reference is not ambiguous.

For example, the WHERE clause in the following statement:

SELECT Name, DeptNo, JobTitle
FROM Personnel.Employee
WHERE Personnel.Employee.DeptNo = 100 ;

can be written as:

WHERE DeptNo = 100 ;

because the database name and table name can be derived from the Personnel.Employee reference in the FROM clause.

Omitting Database Names

When you omit the database name qualifier, Vantage looks in the following databases to find the unqualified object name:
  • The default database
  • Other databases, if any, referenced by the SQL statement
  • The login user database for a volatile table, if the unqualified object name is a table name
  • The SYSLIB database, if the unqualified object name is a C or C++ UDF that is not in the default database

The search must find the name in only one of those databases. An ambiguous name error message results if the name exists in more than one of those databases.

For example, if your login user database has no volatile tables named Employee and you have established Personnel as your default database, you can omit the Personnel database name qualifier from the preceding sample query.