Books in black and white
 Books Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

# Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Previous << 1 .. 44 45 46 47 48 49 < 50 > 51 52 53 54 55 56 .. 241 >> Next

3 The determination as to whether A or C is the represented result is made as follows

Step Action
1 When possible, the result is the value nearest to B.
2 If A and C are equidistant (for example, the fractional part is exactly .5), the result is the even number.

The following SELECT statement returns 48 and 48.

SELECT 4 7.5(FORMAT 'zzzz'), 48.5(FORMAT('zzzz')) ;

returns 48 and 48.

A FORMAT clause can cause the number to be rounded; for example, consider the following:

SELECT 1.3451 (FORMAT 'zz.z');

The FORMAT clause operates on 1.3451 and returns 1.3.

SELECT 13451 / 10000.000

1.345 is returned as expected.

SELECT 13451 / 10000.000 (FORMAT 'zz.z');

Teradata RDBMS for UNIX SQL Reference

5-55
Data Definition

Numeric Formats

The arithmetic expression is evaluated first (yielding 1.345), then the FORMAT clause is applied, returning 1.3.

SELECT 13451 / 10000.00 (FORMAT 'zz.z');

The arithmetic expression is evaluated first (yielding 1.35), then the FORMAT clause is applied. Note that two ‘roundings’ occur; first to 1.35 when evaluating the expression, then a second rounding to 1.4 as a result of the FORMAT statement.

To avoid double ‘roundings’, extend the precision of the arithmetic expression two decimal places beyond that of the FORMAT clause.

Character Meaning
/ Insertion characters.
% Copied to output string where they appear in the FORMAT phrase
Insertion character. The comma is inserted only if a digit has already appeared.
A special insertion character in that it represents a decimal point position.
B Insertion character. A blank is copied to the output string wherever a B appears in the FORMAT phrase.
+ Sign characters.
May be placed at the beginning or end of a format string. One sign character places the edit character in a fixed position for the output string. If two or more of these characters are present on the left, the sign floats (moves to the position just to the left of the number as determined by the stated structure). The + translates to + or - as appropriate; the - translates to - or blank.

5-56

Teradata RDBMS for UNIX SQL Reference
Data Definition

Numeric Formats

Character Meaning
\$ Dollar sign. One \$ places the edit character in a fixed position for the output string. If a result is formatted using a single \$ with Z’s for zero-suppressed decimal digits (for example, \$ZZ9.99), blanks may occur between the \$ and the leftmost nonzero digit of the number. If two or more \$’s are present, the \$ floats to the right, leaving no blanks between it and the leftmost digit. If + or - is present, the dollar sign cannot precede it.
V Implied decimal point position. Internally, the V is recognized as a decimal point to align the numeric value properly for calculation. Because the decimal point is implied, it does not occupy any space in storage and is not included in the output.
Z Zero-suppressed decimal digit. Translates to blank if the digit is zero and preceding digits are also zero. If only Z’s, commas, and dots are used, the FORMAT phrase means “blank when zero.” For example, ZZZZZ, ZZ.Z, and Z,ZZZ.ZZ print only blanks if the number is zero. A Z is illegal if it is specified following a 9.
9 Decimal digit (no zero suppress).
E For exponential notation. Defines the end of the mantissa and the start of the exponent.
char(n) For more than one occurrence of the following characters: -, +, \$, Z, or 9. The (n) notation means that the character is to be repeated n number of times.

Teradata RDBMS for UNIX SQL Reference

5-57
Data Definition

Numeric Formats

Table 5-6

Numeric Format Results

Character Meaning
Dash character. Used when storing numbers such as telephone numbers, social security numbers, and account numbers. If a dash appears immediately after the first digit or before the last digit, it is taken as an embedded dash rather than a sign character. A dash is illegal if it follows a period, comma, plus sign, dollar sign, or V.
S Signed Zoned Decimal character. Defines signed zoned decimal input as a numeric data type, and displays numeric output as signed zone decimal character strings. The S must follow the last decimal digit in the FORMAT phrase. It cannot be used in the same phrase with the characters: %, +, \$, Z, or E.

The display results of various FORMAT phrases for numeric data are shown in Table 5-6.

FORMAT Phrase Data Result
(FORMAT ’\$\$9.99’) .069 \$0.07
(FORMAT ’\$\$9.99’) 1095 ******
(FORMAT ’ZZ,ZZ9.99’) 1095 1,095.00
(FORMAT ’9.99E99’) 1095 1.10E03
(FORMAT ’999V99’) 128.457 128.46
(FORMAT '\$(5).9(2)*) 1 \$1.00
(FORMAT ’999-9999’) 8278777 827-8777
(FORMAT ’ZZ,ZZ9.99-’) 1095 1,095.00
(FORMAT ’ZZ,ZZ9.99-’) -1095 1,095.00-

If the format phrase does not include a sign character, a negative value will have its sign discarded, and the data will appear as positive.

Asterisks are displayed when the defined format is too small to accommodate the integer portion of a numeric value.
Previous << 1 .. 44 45 46 47 48 49 < 50 > 51 52 53 54 55 56 .. 241 >> Next