# Teradata RDBMS forUNIX SQL Reference - NCR

**Download**(direct link)

**:**

**69**> 70 71 72 73 74 75 .. 241 >> Next

The error here is not in EXTRACT, but in assuming that arithmetic operations on Teradata RDBMS time values are supported. They are not.

EXTRACT (SECOND FROM TIME + 50)

If the argument is a character literal formatted as a time value, it is Example 3 converted to REAL and processed. In this example, 59 is returned.

EXTRACT (MINUTE FROM '23:59:17.3')

The following example returns the seconds as DECIMAL(8,2). This Example 4 choice of precision is based on the fact that system time on the

Teradata RDBMS is measured in centiseconds.

EXTRACT (SECOND FROM TIME)

Teradata RDBMS for UNIX SQL Reference

6-15

SQL Expressions

ADD_MONTHS Function

ADD MONTHS Function

Introduction

As noted previously, days can be added or subtracted from a Date value and the operation carries out processes to provide a correct result. A common need, however is to add or subtract a number of months or years to a date, and the peculiarity of the Gregorian Calendar is such that adding 90 days for 3 months, or 730 days for 2 years is not quite right. The addition of a function to add or subtract a number of months resolves this, directly, for months, and indirectly, for years, since a year is unambiguously 12 months.

Syntax

The syntax for the ADD_MONTHS function is:

----ADD_MONTHS — (date_expr, integer_expr)-

HH01A071

One issue on month intervals is that of handling a target month with fewer days than the month in the start date.

The result of the following is a date in February, 1996, and if 31 were Example 1 a valid day in February, there would be no problem.

The result, when the day value in the source date is higher than the last day value for the target month, is the last day value for the target month.

ADD_MONTHS ('1995-12-31', 2)

In the example above, the result is ‘1996-02-29’. Note the leap year, if the interval was 14, the result would be ‘1997-02-28’.

• If either argument of ADD_MONTHS is NULL, the result is NULL.

• If the result would not be in the range ‘0000-01-01’ to ‘3500-1231’, an error is reported.

To get the current date plus 13 years:

Example 2

´ ADD_MONTHS (DATE, 12*13)

To get the date 6 months ago:

Example 3

´ ADD_MONTHS (DATE, -6)

6-16

Teradata RDBMS for UNIX SQL Reference

SQL Expressions

Aggregate Operators

Introduction

Table 6-2

Aggregate Operators

Aggregate Operator Rules

Aggregate Operators

The aggregate operators listed in Table 6-2 can be used in arithmetic expressions.

An aggregate operator is used to perform a computation for the values in a group. The group used is the entire table unless a GROUP BY clause is specified.

Operator Syntax Abbreviation

SUM SUM([DISTINCT] numeric)

AVERAGE AVG ([DISTINCT] numeric) AVE or AVG

COUNT COUNT([DISTINCT] value) count(*)

MINIMUM MINIMUM(expr) MIN

MAXIMUM MAXIMUM(expr) MAX

The aggregate operator rules are listed below:

• It is legal to apply AVERAGE, MINIMUM, MAXIMUM, or COUNT to Date, but it is not legal to do SUM(Date).

• All aggregate operators can be applied to numeric data. For example, the following SELECT statement can be used to calculate the average salary, and minimum and maximum number of years of experience for employees in each department.

SELECT DeptNo, AVG(Salary), MIN(YrsExp), MAX(YrsExp)

FROM Employee GROUP BY DeptNo ORDER BY DeptNo;

The result returned is:

DeptNo Average (Salary) Minimum (YrsExp) Maximum (YrsExp)

100 36,100.00 3 15

300 47,666.67 12 25

500 38,285.71 4 20

600 36,650.00 6 11

700 37,666.67 5 10

Teradata RDBMS for UNIX SQL Reference

6-17

SQL Expressions

Aggregate Operators

• MIN, MAX, and COUNT operate on character as well as numeric data; MIN may be used to return the lowest sort order of a character expression, MAX the highest. For example:

SELECT MIN(Name), MAX(Name) FROM Employee;

returns the following result:

Minimum(Name) Maximum(Name)

Aguilar J Watson L

• Aggregate operators are normally used in the expression list of a SELECT statement and in the summarylist of a WITH clause.

• If an aggregate operation is used on an expression that represents zero elements, the following results are returned:

Aggregate Type Returned

COUNT(expr) WHERE... 0

All except COUNT(expr) WHERE... Null All(expr)...GROUP BY,HAVING.... No Record Found

• Aggregate operations can not be nested, for example:

AVG(MAXIMUM (Salary))

Aggregates can be used in the following types of clauses: Aggregates Used in • The WHERE clause of an ABORT statement, to specify an abort WHERE and HAVING condition. An aggregate operator can not be used in the WHERE

Qauses clause of a SELECT statement.

• A HAVING clause, to specify a group condition.

The MINIMUM (MIN), MAXIMUM (MAX), and COUNT operators GraPhics can be applied to graphic columns. MIN and MAX return the

minimum or maximum value of a character or graphic expression, based on the rules of comparison discussed under Logical Expressions.

**69**> 70 71 72 73 74 75 .. 241 >> Next