Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 61 62 63 64 65 66 < 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:
Previous << 1 .. 61 62 63 64 65 66 < 67 > 68 69 70 71 72 73 .. 241 >> Next