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 .. 43 44 45 46 47 48 < 49 > 50 51 52 53 54 55 .. 241 >> Next


Reconfigure the format of data returned:

SELECT empno(FORMAT FROM employee;

The system returns:

EmpNo

10-021

10-007

10-018

10-011

Name

Smith T Aguilar J Russell S Chin M

99-999'), name, deptno

DeptNo

700

600

300

100

5-50

Teradata RDBMS for UNIX SQL Reference
Data Definition

FORMAT Phrase

Example 5: Using FORMAT to Override Default Format

Example 6: Using FORMAT as Part of Derived Expression

Another example of overriding the column default and formatting the output:

SELECT salary(FORMAT '$$$,$$9.99')

FROM employee WHERE empno = 10019 ;

The system returns:

Salary

$28,600.00

You can use FORMAT as part of a derived expression. To determine the percent increase if employee 10019 is given a $1000 raise:

SELECT (1000/salary)*100 (FORMAT 'zz9%')

(TITLE 'Percent Incr')

FROM employee WHERE empno = 10019 ;

Percent Incr

3%

Teradata RDBMS for UNIX SQL Reference

5-51
Data Definition

Character Formats

Character Formats

Introduction

Example 1

The letter X formats the display of character data. The data is returned in left-to-right order.

For example, if a column is defined as FORMAT ’X’, only the first (leftmost) character of each field value is displayed.

Multiple character positions can be defined either by following the first X with a parenthetical number defining the length of the string, or by entering the equivalent number of X characters.

Both of the following FORMAT phrases specify a five-position character string:

FORMAT 'X(5)'

FORMAT 'XXXXX'

Character strings are padded or truncated on the right when they are shorter or longer than the positions defined in the FORMAT phrase.

Example 2

If your data is the character string ’HELLO’, then:

FORMAT 'X(6)'

displays HELLO with trailing blank, and:

FORMAT 'X'

displays H.

5-52

Teradata RDBMS for UNIX SQL Reference
Data Definition

Graphic Formats

Introduction

Graphic Formats

The letter G, which formats the display of graphic data, corresponds to one logical (double byte) character of the graphic string, and the data is returned in left-to-right order.

For example, if a column is defined as FORMAT ’G’, only the first (leftmost) logical character of each field value is returned.

To define that multiple graphic positions are to be returned, either follow the first G with a parenthetical number defining the length of the string, or enter the equivalent number of G characters.

To specify a six-position graphic string, use either of the following:

FORMAT 'G(6)'

FORMAT 'GGGGGG'

Graphic strings are padded or truncated on the right when they are shorter or longer than the positions defined in the FORMAT phrase. The padding character is <double byte zero> regardless of the current character set.

If the FORMAT phrase is not included in a CREATE or ALTER TABLE statement, the default is to return the full length specified for a fixed-length GRAPHIC column (that is, any padding characters are returned unless TRIM is included in the SELECT statement), and to return the actual graphic string for a VARGRAPHIC or LONG VARGRAPHIC column.

Under a KanjiEBCDIC session in field mode, an expression referencing a GRAPHIC data type returns a properly formatted string; that is, the Shift-Out/Shift-In characters are included in the output. This increases the length of the result expression by two bytes. The increase is automatic; it need not be accounted for in a FORMAT phrase, but it should be considered when designing a BTEQ report format.

Teradata RDBMS for UNIX SQL Reference

5-53
Data Definition

Graphic Formats

Example

then the following query, in field mode:

SELECT Coll (FORMAT 'G(3)'), Co2L2 (FORMAT 'G(3)' FROM Test_Graphic;

2

returns the following string2:

Coll Col2

<ABC> <ABC>

2 < is Shift-out, > is Shift-In

Assume that a table named Test_Graphic defines Col1 and Col2 as GRAPHIC(3). If the following insert is performed from a KanjiEBCDIC machine, as follows:

INSERT INTO Test_Graphic (Col1,Col2 )

VALUES ('42C142C242C3'XG,G'<ABC>');

5-54

Teradata RDBMS for UNIX SQL Reference
Data Definition

Numeric Formats

Introduction

Example

Numeric Formats

The result of a formatted numeric string is right-justified.

A FORMAT phrase that defines fewer positions than are required by

numeric values will cause the data to be returned as follows:

• Asterisks are displayed when the integer portion cannot be accommodated.

• When only the integer portion can be accommodated, any digits to the right of the least significant digit are either truncated (for an integer value) or rounded (for a floating or decimal value).

Rounding is based on “Round to the Nearest” mode, as explained by the following procedure.

Step Action
1 Let B represent the actual result.
2 Let A and C represent the nearest bracketing values that can be represented, such that A < B < C.
Previous << 1 .. 43 44 45 46 47 48 < 49 > 50 51 52 53 54 55 .. 241 >> Next