Qualifying Column Names and Named Objects

Teradata Vantage™ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

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.