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 .. 75 76 77 78 79 80 < 81 > 82 83 84 85 86 87 .. 241 >> Next

SQL Expressions

Japanese Character Site Comparison Operators

Non-Equality Comparison: Collation Sequences

Collation Settings

Non-equality comparisons involve the collation in effect for the session. Three collations are available: EBCDIC, ASCII, and MULTINATIONAL.

Collation can be set at the user level with the COLLATION option of the CREATE USER or MODIFY USER statements, and at the session level with the [[.]SET] SESSION COLLATION statement or the CLIv2 CHARSET call.)

If the MULTINATIONAL collation sequence is in effect, the collation sequence of a Japanese character site is determined by the collation setting installed during start-up. For further information on collation sequences, please refer to Appendix G, “International and Japanese Character Support”.

To make sure that sorting and comparison of character data will be identical with the same operations performed by the client, users on a Japanese character site should set collation as follows:

IF you are using this character set . . . THEN you shoud use this collation sequence . . .
KanjiEUC ASCII
KanjiShift-JIS
KATAKANAEBCDIC MULTINATIONAL
KANJIEBCDIC5026_0I
KANJIEBCDIC5035_0I

Teradata RDBMS for UNIX SQL Reference

6-63
SQL Expressions

Japanese Character Site Comparison Operators

Comparing Non-Equality Character StringS

A non-equality compariSon operation iS handled aS explained below.

Stage Process
1 If expr1 and expr2 are of different lengthS, the Shorter String iS padded with the <Single byte Space> character.
2 UnleSS the CASESPECIFIC qualifier iS applied to one or both of the expreSSionS, any Simple Latin letterS in both expr1 and expr2 are converted to uppercaSe before compariSon beginS.
3 CharacterS identified aS Single byte characterS under the current character Set are converted according to the collation Sequence in effect for the SeSSion.
4 Note that under the KanjiEUC character Set, the ss3 0x8F character is converted to 0xFF. This means that a user-defined KanjiEUC codeset 3 will not be properly ordered with respect to other KanjiEUC code SetS. The ordering of other KanjiEUC codeSetS iS proper; that iS, ordering iS the Same aS the binary ordering on the client SyStem.
5 CharacterS identified aS multibyte characterS remain in the client encoding and are collated baSed on their binary valueS.
6 The prepared StringS are compared byte-by-byte; any trailing SpaceS are ignored.

Comparing Graphic StringS BaSed on Logical CharacterS.

The following ruleS apply to graphic String compariSonS baSed on logical characterS:

• If expr1 and expr2 are of different lengthS, the Shorter String iS padded with <double byte zero> characterS.

• The prepared StringS are compared byte-by-byte; any trailing zeroS are included in the compariSon

6-64

Teradata RDBMS for UNIX SQL Reference
Introduction

Form 1: expr IN and NOT IN an expr or constants

SQL Expressions

Logical Expressions: IN and NOT IN Operator

Logical Expressions: IN and NOT IN Operator

The IN and NOT IN operators have two forms, which allow you to:

• Compare the value of an expression with values in an explicit list of constants.

• Compare values in a list of expressions with values and in a set of corresponding expressions in a subquery.

Note: The IN operator can be substituted by IN ANY or = ANY, and the NOT IN operator can be substituted by ë= ALL or ALL.

The first form of IN and NOT IN is:

expr

Lnot-I

IN

expr

— (j- Constj-)

St -----------

HH01A001

where const may be a constant, a macro parameter, or a built-in value such as TIME or DATE.

• If IN is used with a single-term operator, that operator may be a constant or an expression.

If a multiple-term operator is used, that operator must consist of constants; expressions are not allowed.

• The expr data types and the const values must be compatible. Implicit conversion rules are the same as for the comparison operators.

• If the value of expr is null, then the result is considered to be unknown.

• If the set of constants consists of 70 or fewer members, the system uses hashing to retrieve the rows. If the set consists of more than 70 members, the system does a full-table scan. Note that full-table scans are much more time consuming than row hashed accesses.

Using this form, the IN search condition is satisfied if the expression is equal to any of the values in the list of constants; the NOT IN

Teradata RDBMS for UNIX SQL Reference

6-65
SQL Expressions

Logical Expressions: IN and NOT IN Operator

Example (Form 1 of IN and NOT IN)

Form 2: expr IN and NOT IN Subquery

condition is satisfied if the expression is not equal to any values in the list of constants.

THIS form . . . Is valid in this mode . . . AND means . . .
expr1 IN expr2 Teradata pr2 xp e = 1 expr1 e
Expr1 NOT IN expr2 both pr2 expr e > < 1 expr1 e

This Expression . . . Is Equivalent To . . .
SELECT DeptNo FROM Department WHERE DeptNo IN (500, 600) ; SELECT DeptNo FROM Department WHERE (DeptNo = 500) OR (DeptNo = 600) ;
Previous << 1 .. 75 76 77 78 79 80 < 81 > 82 83 84 85 86 87 .. 241 >> Next