# Teradata RDBMS forUNIX SQL Reference - NCR

**Download**(direct link)

**:**

**66**> 67 68 69 70 71 72 .. 241 >> Next

An expression can consist of a:

• Column name

• Constant

• Combination of column names and constants connected by operators.

The following are examples of expressions:

'Test Tech'

Employee.Name

Salary * 12 + 100

Salary / 52 > 500

Salary > 50000 OR Age > 6 0

6-2

Teradata RDBMS for UNIX SQL Reference

SQL Expressions

Arithmetic Operators

Introduction

Binary Arithmetic Operators

Arithmetic Operators

The Teradata RDBMS uses the following arithmetic operators:

Operator Function

** exponentiate

* multiply

/ divide

MOD modulo (remainder). The MOD operator calculates the remainder in a division operation. For example, 60 MOD 7 = 4: 60 divided by 7 equals 8, with a remainder of 4. The result takes the sign of the dividend, thus: -l7 MOD 4 = -l -l7 MOD -4 = -l l7 MOD -4 = l l7 MOD 4 = l

+ add

- subtract

+ positive value

- negative value

Note: ** and MOD are Teradata RDBMS extensions to ANSI SQL. They will be flagged when the SQL flagger is enabled

The data type of the result of an arithmetic expression is a function of the data types of the two operands. Operands are converted to the result type before the operation is performed.

For example, before an INTEGER value is added to a DECIMAL(5,2) value, the INTEGER value is converted to DECIMAL(l8,2), the data type of the result.

Table 6-1 shows the result data type for the binary arithmetic operators (*, /, +, and -). Note that the result data type for the MOD operator is the same as that for the divide (/) operator; that for VARCHAR(n) the same as that for CHAR. The result data type for the exponentiation operator (**) is always FLOAT.

Teradata RDBMS for UNIX SQL Reference

6-3

SQL Expressions

Arithmetic Operators

Table 6-1

Binary Arithmetic Result Data Types

Int (1) Dec(kj) Flt (1) Date* CHAR(n)

Int INTEGER Dec(18,j) Flt + Date (2) - Date (5) * or / Int (6) Flt (4)(7)

Dec(m,n) ± Dec(18,n) * Dec(m,n) / Dec(m,n) ± Dec (min (18, (1+max (n,j) +max (m-n,k-j))), max (n,j)) * Dec (min (18,m+k), (n+j)) / Dec (18,max (n,j)) Flt + Date (3) - Date (5) * or / Dec(18,n) (6) Flt (4)(7)

Flt Flt Flt Flt Flt (6) Flt (4)(7)

Datea ± Date (2) * or / Int (6) ± Date (3) (6) * or / Dec(18,j) (6) Flt - Int + * / (6) Flt (6)(7)

CHAR(n) Flt (3)(7) Flt (3)(7) Flt (3)(7) Flt (6)(7) Flt (4)(7)

a. The DATE is not the ANSI SQL DATE.

Notes

1 Int refers to BYTEINT, SMALLINT, or INTEGER. Flt refers to FLOAT.

2 If the value of a date result is not in the range of values allowed for the DATE type, an error is reported. The range is any date on the Gregorian calendar from year 1 to year 3500.

3 Fractions of decimal values are truncated when added to or subtracted from date values (note 2 also applies).

4 The character string is converted to a floating value. If the string does not represent a single numeric value, an error is reported.

5 Error

6 These operations on DATE will not report an error, but results are in general, not meaningful.

7 If an argument of an arithmetic operator is a character string, the first action is to attempt a conversion of the characters to a numeric value. If this conversion fails, an error is reported.

6-4

Teradata RDBMS for UNIX SQL Reference

SQL Expressions

Arithmetic Operators

An error is reported if:

• Division by zero is attempted

• The numeric range is exceeded

• The exponentiation operator is used with a negative left argument and a right argument that is not a whole number

Arithmetic operations cannot be applied to GRAPHIC operands.

When computing an expression, decimal results that are not exact are rounded, not truncated. Integer division yields whole results, truncated toward zero.

The format of an arithmetic expression is the same as the default

Arithmetic Expression: format of the result data type.

Format

Note: FORMAT is relevant only in FIELD Mode BTEQ applications.

Assume that you are to raise the salary for each employee in Example department 600 by $200 for each year spent with the company (up

to a maximum of $2500 per month).

To determine who is eligible, and the new salary, enter the following statement:

SELECT name, (salary+(yrsexp*200))/12 AS projection FROM employee

WHERE deptno = 6 00 AND projection < 2500 ;

This statement returns the following response:

Name Projection

Newman P 24 83.3 3

In the statement, parentheses are used to cause the operation YrsExp * 200 to be performed first. Its result is then added to salary and the total is divided by 12.

In all cases, SQL performs

• Arithmetic expressions from left to right

• Exponentiation, multiplication and division before addition and subtraction in any expression

Therefore, the parentheses enclosing the dividend are not strictly necessary, but if no parentheses were used in this expression, the operation yrsexp * 200 would be divided by 12 and the result added to Salary, producing an erroneous value.

**66**> 67 68 69 70 71 72 .. 241 >> Next