Derived Columns Teradata

The majority of the time, columns in the SELECT statement exist within a database table. However, sometimes it is more advantageous to calculate a value than to store it.

An example might be the salary. In the employee table, we store the annual salary. However, a request comes in asking to display the monthly salary. Does the table need to be changed to create a column for storing the monthly salary? Must we go through and update all of the rows (one per employee) and store the monthly salary into the new column just so we can select it for display?

The answer is no, we do not need to do any of this. Instead of storing the monthly salary, we can calculate it from the annual salary using division. If the annual salary is divided by 12 (months per year), we "derive" the monthly salary using mathematics.

Derived Columns

These math functions have a priority associated with their order of execution when mixed in the same formula. The sequence is basically the same as their order in the chart. All exponentiation is performed first. Then, all multiplication and division is performed and lastly, all addition and subtraction is done. Whenever two different operands are at the same priority, like addition and subtraction, they are performed based on their appearance in the equation from left to right.

Although the above is the default priority, it can be over-ridden within the SQL. Normally an equation like 2+4*5 yields 22 as the answer. This is because the 4*5 = 20 is done first and then the 2 is added to it. However, if it is written as (2+4)*5, now the answer becomes 30 (2+4=6*5=30).

The following SELECT shows these and the results of an assortment of mathematics:

SELECT 2+4*5, (2+4)*5
,2+4/5, (2+4)/5
,2+4.0/5, (2+4.0)/5 ,10**9 ;
1 Row ReturnedSELECT shows these and the results of an assortment of mathematics

Note: starting with integer values, as in the above, the answer is an integer. If decimals are used, the result is a decimal answer. Otherwise, a conversion can be used to change the characteristics of the data before being used in any calculation. Adding the decimal makes a difference in the precision of the final answer. So, if the SQL is not providing the answer expected from the data, convert the data first.

The next SELECT shows how the SQL can be written to implement the earlier example with annual and monthly salaries:

SELECT salary (format 'ZZZ,ZZ9.99')
,salary/12 (format 'Z,ZZ9.99')
FROM Pay_Table ;
2 Rows returned SELECT shows how the SQL can be written to implement the earlier example

Since the column name is the default column heading, the derived column is called salary/12, which is not probably what we wish to see there.

Derived data can be used in the WHERE clause as well as the SELECT. The following SQL will only return the columns when the monthly salary is greater than $1,000.00:

SELECT salary (format 'ZZZ,ZZ9.99')
,salary/12 (format 'Z,ZZ9.99')
FROM Pay_Table
WHERE salary/12 > 1000 ;
1 Row returned


operator and operation performed

Some of these functions are demonstrated below and throughout this book. Here they are also using alias names for the columns. Their application will be specific to the type of application being written. It is not the intent of this book to teach the meaning and use in engineering and trigonomy, but more to educate regarding their existence.

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

Teradata Topics