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


DeptNo must be fully qualified in every reference to avoid ambiguity and an extra set of parentheses is needed to group the

6-86

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Conditional Expressions

ORed IN conditions. Without them, the result is a Cartesian product.

SELECT EmpNo,Name,JobTitle,Employee.DeptNo,Loc FROM Employee,Department

WHERE (Employee.DeptNo=Department.DeptNo)

AND ((Employee.DeptNo

IN (SELECT Department.DeptNo FROM Department WHERE MgrNo=10007))

OR (Employee.DeptNo IN (SELECT Department.DeptNo FROM Department WHERE MgrNo=10012))) ;

Assuming that the Department table contains the following rows:

DeptNo Department Loc MgrNo
100 Administration NYC 10005
600 Manufacturing CHI 10007
500 Engineering ATL 10012
300 Exec Office NYC 10018
700 Marketing NYC 10021
join statement returns:
EmpNo Name JobTitle DeptNo Loc
10012 Watson L Vice Pres 500 ATL
10004 Smith T Engineer 500 ATL
10014 Inglis C Tech Writer 500 ATL
10009 Marston A Secretary 500 ATL
10006 Kemper R Assembler 600 CHI
10015 Omura H Programmer 500 ATL
10007 Aguilar J Manager 600 CHI
10010 Reed C Technician 500 ATL
10013 Regan R Purchaser 600 CHI
10016 Carter J Engineer 500 ATL
10019 Newman P Test Tech 600 CHI

The following example uses a conditional expression in a HAVING Example 3 clause to select from the Employee table those departments with the

number 100, 300, 500, or 600, and with a salary average of at least $35,000 but not more than $55,000:

SELECT AVG(Salary)

FROM Employee

WHERE DeptNo IN (100,300,500,600)

GROUP BY DeptNo

HAVING AVG(Salary) BETWEEN 35000 AND 55000 ;

Teradata RDBMS for UNIX SQL Reference

6-87
SQL Expressions

CASE Expression

CASE Expression

CASE is used to specify alternate values based on conditions.

CASE provides an efficient and powerful method for application developers to change the representation of data. It allows data conversion without requiring host program intervention.

For example, you could code employee status as 1 or 2, meaning full-time or part-time, respectively.

For efficiency, the system will store the numeric code but print or display the corresponding word in reports for readers. This storage and conversion is managed by the Teradata RDBMS.

In addition, CASE allows applications to generate null values based on information derived from the database, again without host program intervention. Conversely, CASE can be used to convert a null into a more concrete value, such as a zero (0).

CASE expressions are specified in two different forms: Valued and Searched.

6-88

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Valued CASE Expression

Valued CASE Expression

This section discusses the Valued form of CASE. CASE returns one Introduction of a specific set of values, depending on a condition.

Syntax CASE — value_expression-------------------------------------------------(A)

WHEN — value_expression —THEN — value_expression -I-----(B)

®—³-------------------------------------------------------------1-END—H

I— ELSE— value_expression —I

HH01A050

The WHEN clauses are processed sequentially. When a WHEN clause’s condition is true, the value of the associated scalar expression is used as the overall result. The process ends. If no condition expressions are true, the ELSE scalar expression is used as the overall result.

Another way of explaining the Valued CASE syntax is:

CASE — expr1 J-WHEN — expr2— THEN — result1J----------------(a)

®—³----------------------1--------------------------------END—H

L ELSE— result_n—I

HH01A051

If ELSE is omitted the clause is evaluated as ELSE NULL.

WHEN expri equals this value . . . The result of the CASE expression is . . .
expr2 result1
result2 result2
none of the indicated expressions result_n

The ELSE clause is optional. If it is left out and none of the WHEN Usage Notes conditions is TRUE, the default value of NULL is returned.

The data type of the CASE operand must be comparable with the data types of all of the WHEN operands. Otherwise, an error is returned to the user.

Teradata RDBMS for UNIX SQL Reference

6-89
SQL Expressions

Valued CASE Expression

Example

The following example uses a Valued CASE expression to calculate the fraction of cost in the total cost of inventory represented by parts of type 1:

SELECT SUM(

CASE part WHEN '1' THEN cost ELSE 0

END)/SUM(cost)

FROM t;

6-90

Teradata RDBMS for UNIX SQL Reference
SQL Expressions

Searched CASE Expression

Searched CASE Expression

Introduction

This section discusses the second form of CASE expression, Searched.

Syntax

Example 1

CASE —L WHEN — search_condition —THEN — value_expression —I—(X)

®—³------------------------------------------------------1-END—H

I—ELSE—

ELSE— value_expression

ion —I

HH01A052

One way of explaining the ‘Searched’ CASE syntax is:
Previous << 1 .. 82 83 84 85 86 87 < 88 > 89 90 91 92 93 94 .. 241 >> Next