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 .. 81 82 83 84 85 86 < 87 > 88 89 90 91 92 93 .. 241 >> Next

MN<AB>P %P Character True True
MN<AB>P %< C>% Character; represents an <underscore double byte> False False

Table 6-16

KanjiEUC - (expr LIKE pattern-string)

exp pattern-string Data Type Return Value, ANSI mode Return Value, Teradata mode
SS3A SS2B SS3C SS2D % ss2B% Character True True
M SS2B N SS2D M __ % Graphic; represents an <underscore double byte> True True
SS3A SS2B SS3C SS2D __ % Character; represents an <underscore double byte> True True
SS3A SS2B SS3C SS2D _ % Character; _ represents an <underscore single byte> Truea False

a. The underscore in the pattern-string matches a SBC in Teradata mode, but a SBC or MBC in ANSI mode.

Table 6-17

KanjiShift-JIS - (expr LIKE pattern-string)

expr pattern-string Data Type Return Value, ANSI mode Return Value, Teradata mode
ABCD __B% Graphic; represents an <underscore double byte> True True
mnABC/ %B% Character True True
mnABC/ %/ Character True True

Teradata RDBMS for UNIX SQL Reference

6-83
SQL Expressions

Logical Expressions: Japanese Character Site, LIKE Operator

expr pattern-string Data Type Return Value, ANSI mode Return Value, Teradata mode
mnABCl mn_%! Character; _ represents an <underscore single byte> Truea False
mnABCl mn %I Character; represents an <underscore double byte> True True

a. The underscore in the pattern-string matches a single byte character in Teradata mode, but a single byte character or multibyte character in ANSI mode.

LIKE ... ESCAPE is an extended form of LIKE, and follows the same Japanese Character, LIKE.. rules as discussed above for ANSI and Teradata modes, with the

ESCAPE 6 exception that the escape_character is also allowed.

The ESCAPE feature functions as follows:

FOR this type of character string . . . This character can function as the escape_character . . .
mixed single and double byte any
graphic any multibyte

6-84

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Conditional Expressions

Introduction

Order of Evaluation

Conditional Expressions

A conditional expression specifies the criteria for selecting a row or summary information. A conditional expression may be used as the operand in a WHERE or HAVING clause.

A conditional expression consists of one or more logical expressions connected by the logical operators AND and OR.

For example:

----expri OR expr2-----------OR---expr3------

HH01A048

The NOT logical operator can be used to negate an expression, as follows:

----expri AND NOT expr2----------------

HH01A049

When used in a HAVING clause, a logical expression may be used with an aggregate operator (SUM, AVERAGE, COUNT, MINIMUM, or MAXIMUM).

For example:

WHERE a=c1

GROUP BY a HAVING AVG(b)

BETWEEN c2 AND c3 ;

The following rules apply to evaluation order for conditional expressions:

If an expression contains more than one of the same operator, the evaluation precedence is left to right.

If an expression contains a combination of operators, NOT is evaluated before AND, and AND is evaluated before OR.

Parentheses may be used to establish the desired evaluation precedence.

The logical expressions in a conditional expression are not always evaluated left to right. A conditional expression should be avoided if its accuracy depends on the order in which its logical expressions are evaluated.

For example, compare the following two expressions:

F2/(NULLIF(F1,0))>500 F1 <> 0 AND F2/F1 > 500

Teradata RDBMS for UNIX SQL Reference

6-85
SQL Expressions

Conditional Expressions

The first expression guarantees exclusion of division by zero. The second allows the possibility of error, because the order of its evaluation determines the exclusion of zeros.

Evaluation Results

Each logical expression in a conditional expression is evaluated as to whether it is TRUE, FALSE, or UNKNOWN.

As a final Boolean result, UNKNOWN and NOT UNKNOWN both translate to FALSE.

Table 6-18 and Table 6-19 illustrate the OR and AND logic used in evaluating conditional expressions.

Table 6-18

AND Truth Table (x AND y)

Table 6-19

OR Truth Table (x OR y)

x False x Unknown x True
y False False False False
y Unknown False Unknown Unknown
y True False Unknown True
x False x Unknown x True
y False False Unknown True
y Unknown Unknown Unknown True
y True True True True

Examples of Conditional Expressions

Example 1

The following examples illustrate the use of conditional expressions:

The following example uses a conditional expression to select from a user table named Profile the names of applicants who have either more than two years of experience or at least twelve years of schooling with a high school diploma:

SELECT Name FROM Profile

WHERE YrsExp > 2 OR (EdLev >= 12 AND Grad = 'Y') ;

Example 2

The following statement requests a list of all the employees who report to manager number 10007 or manager number 10012. The manager information is contained in the Department table, while the employee information is contained in the Employee table. The request is processed by joining the tables on DeptNo, their common column.
Previous << 1 .. 81 82 83 84 85 86 < 87 > 88 89 90 91 92 93 .. 241 >> Next