Creating a Column Alias Name Teradata

Since the name of the selected column or derived data formula appears as the heading for the column, it makes for strange looking results. To make the output look better, it is a good idea to use an alias to dress up the heading name used in the output. Besides making the output look better, an alias also makes the SQL easier to write because the new column name can be used anywhere in the SQL statement.

AS

Compliance: ANSI

The previous SELECT used salary/12, which is probably not what we wish to see in the heading. Therefore, it is preferable to alias the column within the execution of the SQL. This means that a temporary name is assigned to the selected column for use only in this statement.

To alias a column, use an AS and any legal Teradata name after the real column name requested or math formula using the following technique:

SELECT salary AS annual_salary
,salary/12 AS Monthly_salary
FROM Pay_Table ;
2 Rows returnedmath formula using the following technique

Once the alias name has been assigned, it is literally the name of the column for the life of the SQL statement.

The next request is a valid example of using of the alias in the WHERE clause:

SELECT salary (format '$$$,$$9.99') AS annual_salary
,salary/12 (format '$$$,$$9.99') AS monthly_salary
FROM Pay_Table
WHERE monthly_salary > 1000 ;
1 Row returnedusing of the alias in the WHERE clause

The math functions are very helpful for calculating and evaluating characteristics of the data. The following examples incorporate most of the functions to demonstrate their operational functionality.

The next SELECT uses literals and aliases to show the data being input and results for each of the most common business applicable operations:

SELECT 200/100 AS Div200
,204 MOD 100 AS Last2
,2 MOD 2 AS Even
,3 MOD 2 AS Odd
,ABS(1) AS Was Positive
,ABS(-1) AS Positive Now
,SQRT(4) AS SqRoot ;
1 Row returnednext SELECT uses literals and aliases

The output of the SELECT shows some interesting results. The division is easy; we learned that in elementary school. The first MOD 100 results in 4, because the result of the division is 2, but the remainder is 4 (204 − 200 = 4). A MOD 100 can result in any value between 0 and 99. In reality, the MOD 100 moves the decimal point two positions to the left. On the other hand, the MOD 2 will always be 0 for even numbers and 1 for odd numbers. The ABS always returns the positive value of any number and lastly, 2 is the square root of 4.

Many of these will be incorporated into SQL throughout this book to demonstrate additional business applications.

NAMED

Compliance: Teradata Extension

Prior to the AS becoming the ANSI standard, Teradata used NAMED as the keyword to establish an alias. Although both currently work, it is strongly suggested that an AS be used for compatibility. Also, as hard as it is to believe, I have heard that NAMED may not work in future releases.

The following is the same SELECT as seen earlier, but here it uses the NAMED instead of the AS:

SELECT salary (NAMED Annual_salary)
,salary/12 (NAMED Monthly_salary)
FROM Pay_Table ;
2 Rows returnedmnthly-sal(1)

Naming conventions

When creating an alias only valid Teradata naming characters are allowed. The alias becomes the name of the column for the life of the SQL statement. The only difference is that it is not stored in the Data Dictionary.

The charts below list the valid characters to use and then the rules (on the left) to follow when ANSI compliance is desired. Also listed are the more flexible Teradata (on the right) allowable characters and extended character sets with its rules.

valid characters to use

Ansi and teradata Naming conventions

Teradata uses all of the ANSI characters as well as the additional ones listed in the above charts.

Breaking Conventions

It is not recommended to break these conventions. However, sometimes it is necessary or desirable to use non-standard characters in a name. Also, sometimes words have been used as table or column names and then in a later release, the name becomes a reserved word. There needs to be a technique to assist you when either of these requirements becomes necessary.

The technique uses double quotes (") around the name. This technique tells the PE that the word is not a reserved word and makes it a valid name. This is the only place that Teradata uses a double quote instead of a single quote (‘).

As an example, the previous SELECT has been modified to use double quotes (") instead of NAMED:

SELECT salary "Annual salary"
,salary/12 "Monthly_salary"
FROM Pay_Table
ORDER BY "Annual Salary" ;
2 Rows returnedBreaking Conventions

Although it is not obvious due to the underlining, the column heading for the first column is Annual Salary, including the space. A space is not a valid naming character, but this is the column name and it is valid because of the double quotes. This can be seen in the ORDER BY where it uses the column name. The next section provides more details on the use of ORDER BY.


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

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

Teradata Topics