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

ANSI CASESPECIFIC
Teradata NOT CASESPECIFIC

6-56

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

CASE Blind Comparisons

The compariSon rule for two character argumentS CHARA and CompariSon Rule for Two CHARB iS aS followS Character ArgumentS 6

IF this argument . . . IS definined in this way . . . THEN the comparison is . . . FOR .. .
either CASESPECIFIC caSe Specific all caSeS.
both NOT CASESPECIFIC caSe blind <Simple Latin LetterS>

Note: BecauSe of the long perSiStence of the CASESPECIFIC attribute for columnS, and the Shorter perSiStence for attributeS carried in the Source text of querieS, the Same Statement can have different reSultS for a uSer logged on in ANSI or Teradata mode.

The following example illuStrateS the poSSible reSultS for different caSe compariSonS.

SELECT * FROM STUDENTS WHERE FIRSTNAME = 'George'

IF column FIRSTNAME is . . . AND the session is in this mode . . . THEN the match succeeds for rows with FIRSTNAME containing . . .
CASESPECIFIC either ‘George’
NOT CASESPECIFIC ANSI ‘George’
NOT CASESPECIFIC Teradata ‘george’, ‘George’, ‘GEORGE’,
and anything elSe that matcheS.

If you want to aSSure caSe blind compariSonS and have an ANSI compliant application, the recommended approach iS to define FIRSTNAME with the ANSI default. (CASESPECIFIC).

USe a Statement Similar to the following to do thiS.

SELECT * FROM STUDENTS WHERE UPPER(FIRSTNAME) = 'GEORGE';

A DATE operand muSt be Submitted in the proper form in order to achieve a correct compariSon.

Proper formS for Submitting a DATE operand are:

• An integer in the form (year-1900)*10000 + month*100 + day. The form YYMMDD iS only valid for the yearS 1900 - 1999.

• The Same form aS the field againSt which the compare iS being done

• An arbitrary form that iS qualified with a Data Type phraSe defining the appropriate data converSion, and a FORMAT phraSe defining the output format

expr: DATE Operand

Example

Teradata RDBMS for UNIX SQL Reference

6-57
SQL Expressions

CASE Blind Comparisons

const

The following examples use a comparison operator on a value in the Employee.DOB column (defined as DATE FORMAT ’MMMbDDbYYYY’) to illustrate correct forms for a DATE operand.

In the first example, the operand is entered as an integer.

In the second example, the operand is entered in a form that agrees with the output format of the DOB column.

In the third example, the value is entered in an arbitrary form, and so is qualified with both a data type phrase (DATE) and a FORMAT phrase.

SELECT * FROM Employee WHERE DOB = 420327 ;

SELECT * FROM Employee WHERE DOB = 'Mar 27 1942' ;

SELECT * FROM Employee

WHERE DOB = ('03/27/42' (DATE, FORMAT 'MM/DD/YY')) ;

Note that arithmetic on DATE operands causes an error if a created value is not a valid date. Therefore, although a date value may be submitted in integer form for comparison purposes, a column that contains date data should be defined as data type DATE, not INTEGER.

Note: Because of backward compatibility with earlier Teradata RDBMS releases, the default format for a DATE field is ‘YY/MM/DD’. As the year 2000 nears, it is clear that this is not an ideal format. The recommended format is ‘YYYY-MM-DD’.

If an integer is used for input to DATE (this is not recommended), the way to enter the first date of the year 2000 is 1000101.

If the field containing the value has the default date format, a display of the value will be ‘00/01/01’, which would also be returned if the value entered was 000101.

See also “Date and Time Expressions”.

The comparison operation may compare expr against a list of explicit constants. A constant may be a defined value, a macro parameter, or a built-in value such as TIME, DATE, or USER.

The data types of expr and const must be compatible.

Implicit conversion rules are the same as for the comparison operators shown in Table 6-14. If the value of expr is null, the result is considered unknown.

IF this quantifier is specified . . . THEN the search condition is satisfied in the following is true for any of the constants in the list . . .
ALL expr1 comparison operator const
ANY expr comparison operator const

6-58

Teradata RDBMS for UNIX SQL Reference
Example

quantifier

SQL Expressions

CASE Blind Comparisons

The following table explains the consequences of this.

This Expression . . . Is Equivalent to . . .
x < ALL ( : a, : b, : c) (x < :a) AND (x < :b) AND (x < :c)
x > ANY (:a,:b,:c) (x > :a) OR (x > :b) OR (x > :c)

The following statement uses a comparison operator with the ANY quantifier to select the employee number, name, and department number of anyone in departments 100, 300, and 500:

This Expression . . . Is Equivalent to . . .
SELECT EmpNo, Name, DeptNo FROM SELECT EmpNo, Name, DeptNo FROM
Employee Employee
WHERE DeptNo = ANY WHERE (DeptNo = 100)
(l00,300,500) ; OR (DeptNo = 300)
OR (DeptNo = 500) ;

The preceding statements are also equivalent to:
Previous << 1 .. 73 74 75 76 77 78 < 79 > 80 81 82 83 84 85 .. 241 >> Next