Teradata RDBMS forUNIX SQL Reference - NCR

Typically, five columns or less can be compressed without Achieving Maximum impacting access time. However, maximum benefit is achieved

Benefit With COMPRESS when the COMPRESS phrase is applied to a column under the

following conditions:

 The majority of rows contain a compressible value (nulls, zeros, blanks, or constantvalue) in the compressed field.

 The column with the highest rate of access and the highest percentage of non-compressible values has the lowest column id (relative to other compressed columns).

 The shortened row length results in the elimination of one or more data blocks.

COMPRESS can be applied to a fixed-length GRAPHIC column that comprEss with does not exceed 127 logical characters (254 bytes). The value to be

GRAPHIC Columns compressed is saved internally as character data.

To review the compressed value of a GRAPHIC column, apply the CHAR2HEXINT function to the CompressValue column of the DBC.Columns view.

For example, assume that the following table was defined during a KanjiEBCDIC session:

CREATE TABLE Test_Gcomp

(Col1 INTEGER, Col2 GRAPHIC(2) COMPRESS G'<AB>');

5-78

Teradata RDBMS for UNIX SQL Reference
Data Definition

COMPRESS Phrase

If the following statement is submitted:

SELECT ColumnName, CHAR2HEXINT(CompressValue)

FROM DBC.Columns

WHERE TableName = 'Test_Gcomp' and ColumnName = 'Col2';

the return displays the hexadecimal representation of the constant to be compressed, as follows:

ColumnName Char2HexInt(CompressValue)

Col2 42C1426C2

If the CHAR2HEXINT function is not applied to the CompressValue column, the graphic compress value is treated as character data and the result is misleading.

CHAR2HEXINT is useful for cases when shift-out and shift-in characters are not stored in the character string for KanjiEBCDIC data. For KanjiSHift-JIS or KanjiEUC, this procedure is not required.

Teradata RDBMS for UNIX SQL Reference

5-79
Data Definition

Data Type Conversions

Data Type Conversions

A data type conversion modifies the data type of an expression and Intr0ducti0n can be either implicit or explicit.

Explicit conversions can be made with the CAST function or with a data definition phrase.

Field Mode: User Response Data

In Field Mode, a report format used in BTEQ, all data is returned in character form. The alignment and spacing of columns is controlled by data formats and title information. Each row returned is essentially a character string ready for display.

In Field Mode it is unnecessary to convert numeric data to character format. If, in Field Mode, the query includes a character value that has been explicitly converted to numeric, the following rules apply:

IF the character value . . . THEN . . .
does not represent a number an error is reported.
represents a number but that number is not in the range of declared numeric type no error is reported, but a set of asterisks is placed in the response row at the point that the numeric value would be displayed

The following example demonstrates how data type conversions are Example handled for varying values of Fieldl.

T.Fieldl is CHAR(6)

SELECT Fieldl(SMALLINT) FROM Tl

The range of SMALLINT is -32768 to 32768

 The value 31000 displays normally

 The value 100000 displays as ******

 The value xyz causes an error return with no values.

The same query executed in Record or Indicator Variable Mode returns an error for the latter two cases.

5-80

Teradata RDBMS for UNIX SQL Reference
Data Definition

Data Type Conversions

Forms of Data Type Conversions

Converting Graphics and Character to Graphics

Implicit Conversion

Explicit Conversion

The Teradata RDBMS supports three forms of data conversion:

 Implicit

 Using the CAST function:

CAST-----( expression---AS data_definition )------

HH01A018

 Using a data definition phrase:

----value  (data definition )-

HH01A075

This section discusses the role of implicit and explicit conversion of character to graphic and of graphic data:

The RDBMS never performs implicit conversions between graphic and other data types. Furthermore, the standard explicit conversion syntax generates an error message. However, from a KanjiEBCDIC session, conversion from character data to graphic data is possible via the VARGRAPHIC function.

For example, from a KanjiEBCDIC session, the following syntax is allowed:

SELECT VARGRAPHIC ('string_expr');

Explicit conversion syntax can be used to change the size or nature of graphic data, for example:

SELECT CAST ('42434244'XG AS GRAPHIC(3));

See also CAST in Data Type Conversion.

Teradata RDBMS for UNIX SQL Reference

5-81
Data Definition

Implicit Type Conversion Rules

Introduction

Example

Implicit Conversion Rules

Implicit Type Conversion Rules

This form of conversion is non-ANSI and is flagged as such if the SQL flagger is enabled. The Teradata RDBMS allows the assignment and comparison of values that cannot be assigned or compared without converting values.