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 .. 72 73 74 75 76 77 < 78 > 79 80 81 82 83 84 .. 241 >> Next


If the BETWEEN test fails, no rows are returned.

The BETWEEN test is treated as two separate logical comparisons:

exprl >= expr2 and exprl <= expr3.

This Expression . . . Is Equivalent To . . .
x BETWEEN y AND z ((x >= y) AND (x <=z))

Teradata RDBMS for UNIX SQL Reference

6-53
SQL Expressions

Comparison Operators

Comparison Operator Forms

expr

A logical expression that uses a comparison operator has the following forms:

expr2 ------------------------------

-----expri-------operator

-----expri-------operator--------quantifier — (—L const -I—

- expri-----operator

- expri-----operator-

I— quantifier —I

( —Lconst J-)-( subquery ) —

quantifier

- (-,— expri ——)------------operator-,-----------------p

quantifier

L, expri-

( subquery ) -

where:

HH0iA039

Syntax Element . . . Is . . .
operator one of the comparison operators from Table 6-13 (except for BETWEEN and NOT BETWEEN).
expr a SQL arithmetic or string expression.
quantifier one of the following quantifier keywords: • ANY • SOME • ALL
const one or more constant values.
subquery a SQL SELECT statement.

The following sections describe the use of each argument.

See Table 6-14, which lists the resultant data types in a comparison.

• If any expression in a comparison is null, the result of the comparison is unknown.

For a comparison to provide a true result when accessing fields that may contain null values, the statement must include the IS [NOT] NULL operator.

• Expression operands must be of the same data type before a comparison operation can occur.

• If operand data types differ, then an implicit conversion is performed, as shown in Table 6-14.

Comparisons between character and numeric data types require that the character field be convertible to a numeric value.

• If character strings of unequal length are being compared, the shorter of the two is padded on the right with blanks before the comparison occurs.

6-54

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Comparison Operators

If two byte StringS of unequal length are compared, the Shorter String iS padded on the right with binary zeroS.

• When comparing character StringS, data characterS are compared aS though converted to uppercaSe, unleSS otherwiSe Specified with the CASESPECIFIC option.

The valueS uSed depend on the collation in effect for the SeSSion.

• If numeric valueS of different underlying repreSentationS are being compared, they are converted to the Same repreSentation before the compariSon occurS.

For example, numeric valueS of decimal and integer repreSentationS are converted and compared aS followS:

Expression I Expression 2 Data Compared As

INTEGER DECIMAL(m,n) DECIMAL(18,n)

DECIMAL(m,n) DECIMAL(k,j) DECIMAL(18,max (n,j))

ThuS, if an INTEGER value iS compared to a DECIMAL(5,2) value, the INTEGER value iS firSt converted to DECIMAL(18,2).

Table 6-14

Implicit Conversions for Comparisons

Numeric Character Byte Date Graphic
Numeric numeric numeric error numeric error
Date numeric date error date error
Character numeric character error date error
Byte error error byte error error
Graphic error error error error graphic

Note: Implicit converSionS are flagged aS non-ANSI when the SQL flagger iS enabled.

Teradata RDBMS for UNIX SQL Reference

6-55
SQL Expressions

CASE Blind Comparisons

Introduction

Case Sensitivity in Comparisons

CASE Blind Comparisons

Case blind comparisons can be accomplished via the UPPER function, applied to any character string value that is not assured to contain no lower case latin letters.

The UPPER function is NOT the same as declaring a value UPPERCASE.

Related material is covered in the section “String Function: UPPER”.

All character data accessed in the execution of a Teradata SQL statement has an attribute of CASESPECIFIC or NOT CASESPECIFIC, either as a default or by explicit designation.

NOT CASESPECIFIC is not an ANSI-compatible attribute, as ANSI SQL does all character comparisons as the equivalent of CASESPECIFIC.

Columns of tables carry the attribute assigned, by default or explicitly, at the time the column was defined unless a type modification phrase is used in their access.

Provision is made at session logon to declare a session as operating in ANSI or Teradata mode.

If a session is in ANSI mode then the default for character data in a CREATE or ALTER statement is CASESPECIFIC. If Teradata mode, the default is NOT CASESPECIFIC.

Note: This attribute persists unless the column definition is modified by a later ALTER statement.

Character Literal strings in queries, view and macro definitions, CHECK constraints and data accessed via the USING phrase have defaults that are determined at session logon unless explicitly modified. The default attribute exists only for the life of the session.

IF a session is in this mode . . . THEN all character string literals are . . .
Previous << 1 .. 72 73 74 75 76 77 < 78 > 79 80 81 82 83 84 .. 241 >> Next