Qualifying Table Names and Creating a Table Alias Teradata

This section provides techniques to specifically reference table and columns throughout all databases and to temporarily rename tables with an alias name. Both of these techniques are necessary to provide specific and unique names to the optimizer at SQL execution time.

Qualifying Column Names

Since column names within a table must be unique, the system knows which data to access simply by using the column name. However, when more that one table is referenced by the FROM in a single SELECT, this may not be the case. The potential exists for columns of the same domain to have the same name in more than one table. When this happens, the system does not guess which column to reference. The SQL must explicitly declare which table to use for accessing the column.

This declaration is called qualifying the column name. If the SQL does not qualify the column name appearing in more than one table, the system displays an error message that indicates too much ambiguity exists in the query. Correlated subqueries, addressed next, and join processing, both make use of more than one table at the same time. Therefore, many times it is important to make sure the system knows which table's columns to use for all portions of the SQL statement.

To qualify a column name, the table name and column name are connected using a period or sometimes referred to as a dot (.). The dot connects the names without a space to make the two names work as a single reference name. However, if the column has different names in the multiple tables, there is no confusion within the system and therefore, no need to qualify the name.

To illustrate this concept, lets consider people instead of tables. For instance, Mike is a common name. If two Mikes are in different rooms and someone uses the name in either room, there is no confusion. However, if both Mikes are in the same room and someone uses the name, both Mikes respond and therefore confusion exists. To eliminate the conflict, the use of the first and last names makes the identification unique.

The syntax for qualification levels follow:

Whenever all 3 levels are used, the first name is always the database, the second is the table and the last is the column. However, when two names appear in a 2-level qualification, the location of the names within the SQL must be examined to know for sure their meaning. Since the FROM names the tables, the first name of the qualified names is a database name and the second is a table. Since columns are referenced in the SELECT list and WHERE clause, the first name is a table name and the second is an * (all columns) or a single column.

In Teradata, the following is a valid statement, including the abbreviation for SELECT and missing FROM:

SEL DBC.TABLES.* ;

This technique is not ANSI standard, however, the PE has everything needed to get all columns and rows out of the TABLES table in the DBC database.

Creating an Alias for a Table

Since table names can be up to 30 characters long, to save typing when the name is used more than once, a commonly used technique is to provide a temporary name for the table within the SELECT. The new temporary name for a table is called an alias name.

Once the alias is created for the table, it is important to use the alias name throughout the request. Otherwise the system looks at the use of the full table name as another table and it causes undesirable results. To establish an alias for a table, in the FROM, simply follow the name of the table with an AS: FROM <table-name> AS <table-alias-name>.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics