# Teradata RDBMS forUNIX SQL Reference - NCR

**Download**(direct link)

**:**

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

Teradata RDBMS for UNIX SQL Reference

6-5

SQL Expressions

Arithmetic Operators

Note the use of

AS projection

This is the ANSI compatible alternative to

(NAMED projection)

phrase in this statement. It associates the arithmetic expression (Salary + (YrsExp * 200)/12) with Projection. This enables you to refer to the expression by this name in the WHERE clause, rather than type the entire expression again.

The result is formatted without a comma separating thousands from hundreds. To specify a comma (or other formatting characters) in such a result, include a FORMAT phrase in the SELECT statement.

6-6

Teradata RDBMS for UNIX SQL Reference

SQL Expressions

Arithmetic Functions

Introduction

Arithmetic Functions

The Teradata RDBMS provides the following arithmetic functions that require numeric arguments:

Function Result

ABS (arg) Absolute value

EXP (arg) Raises e to the power of arg (e**arg)

LOG (arg) Base l0 logarithm

LN (arg) Natural logarithm; (e = 2.71828182845905)

SQRT (arg) Square root

NULLIFZERO (arg) Returns NULL if arg is zero; else arg

ZEROIFNULL (arg) Returns zero is arg is NULL; else arg

If the argument is not numeric, it is converted to a numeric value. If it cannot be converted, an error is reported.

Arithmetic functions cannot be applied to GRAPHIC operands.

The above arithmetic functions will be flagged as not ANSI SQL, when the SQL flagger is enabled.

Teradata RDBMS for UNIX SQL Reference

6-7

SQL Expressions

Arithmetic Functions

Examples of Arithmetic Functions and Their Results

Result Data Types

Representative arithmetic function expressions and their results are:

Expression Results

ABS(-12) 12

ABS('23') 2.30000000000000E+001

EXP(1) 2.71828182845905E+000

EXP(0) 1.00000000000000E+000

LOG(50) 1.69897000433602E+000

LOG(100) 2.00000000000000E+000

LN(2.71828182845905) 1.00000000000000E+000

LN(0) Error

SQRT(2) 1.41421356237309E+000

SQRT(-2) Error

NULLIFZERO(6) 6

NULLIFZERO(0) Null

ZEROIFNULL(NULL) 0

ZEROIFNULL(6) 6

Arithmetic functions have the following result data types:

Function Result Data Type.

ABS Same as arg

EXP FLOAT

LOG FLOAT

LN FLOAT

SQRT FLOAT

NULLIFZERO Same as arg

ZEROIFNULL Same as arg

6-8

Teradata RDBMS for UNIX SQL Reference

Some Typical Error Messages

NULLIFZERO

SQL Expressions

Arithmetic Functions

Executing arithmetic functions may result in the following errors:

Function Error

EXP (arg) Numeric overflow

LOG (arg) For zero or negative arg

LN (arg) For zero or negative arg

SQRT (arg) For negative arg

If arg is a character string that represents a single numeric value, it is converted to a numeric value of the FLOAT data type before the function is executed.

If a character string arg does not represent a single number, an error is reported.

The default title for the arithmetic functions, for example, ABS, EXP, LOG, LN, or SQRT is:

functionname(arg)

The primary use of the NULLIFZERO function is to avoid problems in division by zero. For example, the expression 6/x or 6/exp produces an error if the value of x or exp is zero. The expression 6/NULLIFZERO(x) or 6/NULLIFZERO(exp) produces a null result (no error), which is not a division by zero and hence not considered an error. NULLIFZERO returns the actual value of a numeric column if the value is nonzero, or NULL if the value is NULL or zero.

The following request:

SELECT empno, NULLIFZERO(hcap) FROM employee WHERE empno = 10 019 ;

produces a null in the second column because the HCap field value for Newman is zero. In BTEQ this displays as a ‘?’.

Teradata RDBMS for UNIX SQL Reference

6-9

SQL Expressions

Arithmetic Functions

ZEROIFNULL

ZEROIFNULL provides a way of avoiding a null result in a case where it would create an error. ZEROIFNULL returns the value of the data in a field if the data is NON-NULL, or a zero if the value is NULL or zero.

In the following example, you can test the Salary column for null:

SELECT empno, ZEROIFNULL(salary) FROM employee ;

A non-zero value is returned for each employee number, indicating that no nulls exist in the Salary column.

See also CASE, NULLIF, and COALESCE for additional expressions involving checks for NULL values.

6-10

Teradata RDBMS for UNIX SQL Reference

SQL Expressions

Date and Time Expressions

Introduction

Date and Integer Arithmetic

Date and Date Arithmetic

Date and Time Expressions

Teradata SQL provides a data type for date values and provides for storage of time values as encoded numbers, stored in REAL data type.

Since both Date and Time are encoded values, not simple integers or real numbers, arithmetic operations on these values are restricted.

The following arithmetic functions can be performed with date and an integer:

• Date + Integer (integer is interpreted as a number of days)

• Integer + Date

• Date - Integer

These expressions are not processed as simple addition or subtraction. The process is as follows:

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