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 .. 46 47 48 49 50 51 < 52 > 53 54 55 56 57 58 .. 241 >> Next


FORMAT Phrase Data Result
FORMAT ’YY/MM/DD’ 85/09/12 85/09/12
FORMAT ’DD-MM-YY’ 85/09/12 12-09-85
FORMAT ’YYYY/MM/DD’ 85/09/12 1985/09/12
FORMAT ’YYYY-MM-DD’ 85/09/12 1985-09-12
FORMAT ’YYYY.DDD’ 85/09/12 1985.225
FORMAT ’YYBDDD’ 85/09/12 85 225
FORMAT ’DDBMMMBYYYY’ 85/09/12 12 Sep 1985
FORMAT ’MMMBDD,BYYYY’ 85/09/12 Sep 12, 1985
FORMAT ’YYYYBMMMBDD’ 85/09/12 1985 Sep 12
FORMAT ’MMM’ 85/09/12 Sep

5-62

Teradata RDBMS for UNIX SQL Reference
Data Definition

DATE Formats

How To Query When The Response Must Be Uppercase

Record Mode Import Anomaly

If all uppercase is required, the following statement can be used:

SELECT DATE (FORMAT 'MMMbdd,bYYYY') (CHAR(12), UC) ;

Using 85/09/12 for data, this statement returns:

SEP 12, 1985

The following query shows how the date is specified if purchase_date had been specified as ’DDBMMMBYYYY’:

SELECT * FROM sales WHERE purchase_date = '30 Mar 1994'...

If purchase_date had been specified as ’YYYY-MM-DD’, the query would be as follows:

SELECT * FROM sales WHERE purchase_date = '1994-03-30'...

When data is imported in Record Mode (for example, using bulkload) into a NULLABLE DATE field, and the source data is a binary integer of value zero, then the field is set to NULL (not 0).

Teradata RDBMS for UNIX SQL Reference

5-63
Data Definition

Data Default Formats

Table 5-11

Data Type Format Defaults

Table 5-12 FORMAT Displays

Data Default Formats

Table 5-11 lists the default display format that Teradata SQL provides for each data type.

Data Type Default Format
BYTEINT -(3)9
SMALLINT -(5)9
INTEGER -(10)9
DECIMAL[(n[,m])] NUMERIC[(n[,m])] The total length is n+2; the +2 represents one additional position for the sign, and one additional position for the decimal point. n = total number of digits. m = decimal portion of the number n-m = integer portion of the number.
FLOAT REAL DOUBLE PRECISION -9.99999999999999E-999
CHAR[(n)] X(n)
VARCHAR(n) X(n)
LONG VARCHAR X(32000)
BYTE[(n)] X(2n)
VARBYTE(n) X(2n)
DATE a YY/MM/DD
GRAPHIC[(n)] G(n), where n represents the number of two-byte logical characters
VARGRAPHIC(n) G(n) where n represents the number of two-byte logical characters

a. This default may be changed in future releases to ‘yyyy-mm-dd’ (the ANSI date format) to avoid the crossover problem from 1999 to 2000.

Examples of character string and numeric formats are shown in Table 5-12.

FORMAT Data Display Form
X(6) ’HELLO’ ’HELLO ’
XXXXXX ’HELLO’ ’HELLO ’

5-64

Teradata RDBMS for UNIX SQL Reference
Data Definition

Data Default Formats

FORMAT Data Display Form
X ’HELLO’ ’H’
X(5) ’HELLO’ ’HELLO’
$$9.99 .079 ’ $0.08’
$$9.99 1095 ******
ZZ,ZZ9.99 1095 ’ 1,095.00’
9.99E99 1095 ’1.09E03’
999V99 123.456 ’12346’
$(5).9(2) 1 ’$1.00’
YY.DDD 85.224 ’85.224’
MMMBDD,BYY Sep 12, 85 ’Sep 12, 85’
YYYY-MM-DD 1996-02-14 ‘1996-02-14’
YYYYBMMMBDD 1985 Sep 12 ’1985 Sep 12’
KatakanaEBCDICa with default format mN<ABC>b MN<ABC>B

a. < is Shift-Out, > is Shift-In, bold means double byte. Note that KatakanaEBCDIC uppercases single byte data.

Teradata RDBMS for UNIX SQL Reference

5-65
Data Definition

Naming Columns and Expressions

Naming Columns and Expressions

Introduction

Each column in a SELECT result has a name that is derived from the list of specified column names (expressions) that generated the data. The name is typically the column name from which the data came.

For example, the columns in the result

SELECT EmpNo,Name FROM Employee;

are labeled EmpNo and Name by default.

In some cases, it is necessary to associate a column name that is different from the default column name with a result. The Teradata RDBMS provides two ways to name a column.

Naming Columns

The Teradata RDBMS allows for naming derived columns and renaming existing columns. The syntax allowed in a query expression is of the form:

- value_expression

(expression)-(NAMED name) -

HH01A015

Note: Keyword AS is optional, in the first form above.

For every expression which is given a name, that name is entered in a NAMED list which associates the expression with the name. This is true regardless of which of the above two syntax forms is used.

Example 1

This syntax allows the ORDER BY clause to include column names rather than positionally defining the sort columns.

SEL COLUMN1 +100 AS FIRST_VALUE,

COLUMN2 SECOND_VALUE FROM TABLE1

WHERE SECOND_VALUE >10 0 ORDER BY FIRST_VALUE

The AS keyword is not included when COLUMN2 is renamed to SECOND VALUE.

name

or

5-66

Teradata RDBMS for UNIX SQL Reference
Data Definition

Naming Columns and Expressions

Example 2

Example 3

This syntax creates a temporary named column and uses it in a WHERE clause.

SELECT Name,((Salary + (YrsExp * 200))/12)

AS Projection FROM Employee

WHERE DeptNo = 600 AND Projection < 2500;

Alternatively, a NAMED clause can be used to name columns.

Previous << 1 .. 46 47 48 49 50 51 < 52 > 53 54 55 56 57 58 .. 241 >> Next