Teradata SQL recognizes the quantifiers ANY (or SOME) and ALL. A quantifier allows one or more expressions to be compared with one or more constants (see above) or with one or more values returned by a subquery.

If a quantifier is used with a subquery, the subquery may return none, one, or several values.

The results are determined as follows:

The following expression is TRUE . . . WHEN expression is greater than . . . OR takes this value when subquery returns no values . . .
expression > ALL subquery every value in the set of values returned by subquery. It is also TRUE if subquery returns no values TRUE
expression > ANY subquery at least one value of the set of values returned by subquery FALSE

the Subquery muSt Select the Same number of expreSSionS aS are Specified in the expreSSion liSt.

For example:

SELECT EmpNo,Name,JobTitle,Salary,YrsExp FROM Employee

WHERE (Salary,YrsExp) >= ALL (SELECT Salary,YrsExp FROM Employee) ;

A Subquery iS a SELECT Statement that returnS valueS uSed to SatiSfy the compariSon operation. The Subquery muSt be encloSed in parentheSeS; it doeS not end with a Semicolon.

The Subquery muSt refer to at leaSt one table.

A table that iS in the WHERE clauSe, but that iS not referred to in any other partS of the Subquery, iS not applicable.

A compariSon operation may be uSed with a Subquery whether or not a quantifier iS uSed. If a quantifier iS not uSed, however, then an error condition reSultS if the Subquery returnS more than one value.

If a Subquery returnS no valueS, and if a quantifier iS not uSed, then the reSult of the compariSon iS falSe. Therefore, if the following form iS uSed, the Subquery muSt return either no valueS (in which caSe the compariSon evaluateS to falSe), or it returnS one value.

expression > (subquery)

With the following form, Subquery muSt Select the Same number of expreSSionS aS are Specified in the expreSSion liSt.

The two expreSSion liStS are equal if each of the reSpective expreSSionS are equal.

If the reSpective expreSSionS are not equal, then the reSult of the compariSon iS determined by comparing the firSt pair of expreSSionS (from the left) for which the compariSon iS not true.

HH01A041

HH01A041

6-60

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

CASE Blind Comparisons

The following statement uses the ALL quantifier to compare two Example expressions with the values returned from a subquery to find the

employee(s) with the most years of experience in the group of employees having the highest salary:

SELECT EmpNo, Name, DeptNo, JobTitle, Salary, YrsExp FROM Employee

WHERE (Salary,YrsExp) >= ALL

(SELECT Salary,YrsExp FROM Employee) ;

Teradata RDBMS for UNIX SQL Reference

6-61
SQL Expressions

Japanese Character Site Comparison Operators

Japanese Character Site Comparison Operators

Introduction

On a Japanese character site, the Teradata RDBMS can perform comparison operations on graphic data. Comparison of Japanese character or graphic strings is described in the following passages.

Equality Comparison

The handling of an equality comparison depends on whether the expressions are character or graphic strings, as explained below.

• Comparison of character strings, which can contain mixed

single byte character/multibyte character data, is handled as

follows:

• If expr1 and expr2 are of different lengths, the shorter string is padded with one or more <single byte space> characters. (The single byte space is always used, regardless of the character set.)

• 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.

• The prepared strings are compared byte-by-byte; any trailing spaces are ignored.

• Comparison of graphic strings is based on logical characters.

The operation is handled as follows:

• A graphic expression cannot be converted; it can be compared only to another graphic expression.

• If expr1 and expr2 are of different lengths, the shorter string is padded with one or more <double byte zero> characters. (Note that a double byte zero is always used, regardless of the current character set.)

• The prepared strings are compared byte-by-byte; any trailing zeros are included in the comparison. For example, AB<double byte space> is not equal to AB.

• Two graphic strings are considered equal if their byte representation is the same. (This is true on any site and under any character set.)