Qualifying Column Names & Named Objects | Teradata Vantage - Qualifying Column Names and Named Objects - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
zsn1556242031050.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™

When you qualify a column with a table name, the qualifying column cannot be a named object. The following examples demonstrate why this does not work.

Begin with table t, defined as:

CREATE TABLE T (a INT, b INT);

Using this base table, note how the SQL resolver parses and resolves the following statement, creating a view based on the base table:

CREATE VIEW V AS
SELECT T.a (NAMED X), X (NAMED Y);

Name order and resolution are extremely important because of the way the resolver treats this statement.

Step IF Column x is … THEN … ELSE …
1 in table t proceed  
2 not in table t find column x in view v display an appropriate error message

The following example explains why named objects can be tricky to use in statements such as this:

CREATE VIEW V AS
SELECT a*5+3 (NAMED X), x*2 (NAMED Y)
FROM T;

The Resolver analyzes this statement as follows:

  1. Look for X as a column in table T.
  2. If X is not found, then try to locate column X in view V (the named object).

Usage of named objects is important, as shown in the following example:

CREATE VIEW V AS
SELECT a (NAMED b), b (NAMED y)
FROM T;

Note that the phrase b (NAMED Y) resolves to T.b.

The following example does not work because the table named T does not have a column named X. X is a named object, not a column name. The erroneous example is:

CREATE VIEW v AS
SELECT t.a (NAMED X), t.x (NAMED Y);

Because table T does not have a column named X, the statement fails. X is a named object. The rule is that when you qualify a column with a table name, the qualifying column cannot be a named object.