15.10 - Naming Columns and Expressions - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

Each column in a SELECT result has a name that is derived from the list of specified column names (expressions) that generated the data. The name is typically the column name from which the data came.

For example, the columns in the result

   SELECT EmpNo, Name 
   FROM Employee; 

are labeled EmpNo and Name by default.

In some cases, it is necessary to associate a column name that is different from the default column name with a result. Teradata Database provides two ways to name a column.

 

This phrase for naming …

Is …

NAMED

a Teradata extension to the ANSI SQL:2011 standard.

AS

ANSI SQL:2011 compliant.

Teradata Database allows for naming derived columns and renaming existing columns. For every expression that is given a name, that name is entered in a NAMED list that associates the expression with the name. This is true for both forms of naming columns.

The AS and NAMED phrases are documented individually on subsequent pages.

If a column is associated with an explicit table name, the table is searched for the column name. Based on the result of the search, one of the following occurs:

 

IF this …

IS …

THEN …

a matching column

found in the table

the requested information is returned.

not found in the table

the NAMED list is searched for the column name.

“Named” names are not valid when used to fully qualify a table name.

a column name

not found in the explicit table name or in the NAMED list

a nonexistent column error is reported.

If a column is not associated with an explicit table name, tables named in the SQL statement are searched, and one of the following occurs.

 

IF this …

IS …

THEN …

a matching column

found in only one table

the requested information is returned.

found in two or more tables

an ambiguous column name error is reported.

not found

the NAMED list is searched for the column name.

Named names are not valid when used to fully qualify a table name.

a column name

not found in the SQL statement or in the NAMED list

a nonexistent column error is reported.

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.