15.00 - Referencing Object Names in a Request - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Referencing Object Names in a Request

When you reference an object name in a request, you may need to qualify the name. The topics that follow explain the rules for use of qualified and unqualified object names.

The following example shows unqualified, qualified and a fully qualified name references in a SELECT statement that accesses the Employee table in the personnel database:

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

Fully Qualified Object Names

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.

where:

 

This Syntax element …

Specifies …

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 might or might not be required.

See “Unqualified Object Names” on page 92.

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 might or might not be required.

See “Unqualified Object Names” on page 92.

column_name

one of the following:

  • The name of the column being referenced
  • The alias of the column being referenced
  • The keyword PARTITION
  • See “Using a Column Alias” on page 93.

    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.

    Unqualified Object Names

    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, Teradata Database looks in the following databases to find the unqualified object name:

  • The default database (see “The Default Database” on page 105)
  • 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.

    Related Topics

     

    For more information on, for example…

    See …

    default databases

    “The Default Database” on page 105.

    the DATABASE statement

    SQL Data Definition Language.

    the CREATE USER statement

    the MODIFY USER statement

    the CREATE PROCEDURE statement and the SQL SECURITY clause

    Using a Column Alias

    In addition to referring to a column by name, an SQL query can reference a column by an alias. Column aliases are used for join indexes when two columns have the same name. However, an alias can be used for any column when a pseudonym is more descriptive or easier to use. Using an alias to name an expression allows a query to reference the expression.

    You can specify a column alias with or without the keyword AS on the first reference to the column in the query. The following example creates and uses aliases for the first two columns.

       SELECT departnumber AS d, employeename e, salary
       FROM personnel.employee
       WHERE d IN(100, 500)
       ORDER BY d, e ;

    Alias names must meet the same requirements as names of other database objects.

    The scope of alias names is confined to the query.

    Referencing All Columns in a Table

    An asterisk references all columns in a row simultaneously, for example, the following SELECT statement references all columns in the Employee table. A list of those fully qualified column names follows the query.

       SELECT * FROM Employee;
     
       Personnel.Employee.EmpNo 
       Personnel.Employee.Name 
       Personnel.Employee.DeptNo 
       Personnel.Employee.JobTitle 
       Personnel.Employee.Salary 
       Personnel.Employee.YrsExp 
       Personnel.Employee.DOB 
       Personnel.Employee.Sex 
       Personnel.Employee.Race 
       Personnel.Employee.MStat 
       Personnel.Employee.EdLev 
       Personnel.Employee.HCap