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.
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
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
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
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.
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)
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.
Teradata uses all of the ANSI characters as well as the additional ones listed in the above charts.
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"
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.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.