in black and white
Main menu
Share a book About us Home
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 92 93 94 95 96 97 < 98 > 99 100 101 102 103 104 .. 241 >> Next

• A “Workers” row must contain a DeptNo value that matches the DeptNo value in a “Managers” row.

• The matching “Workers” row must also contain a YrsExp value that is greater than the YrsExp value in the “Managers” row.

The following result is returned:

Name YrsExp DeptNo Name YrsExp
Greene W 15 100 Jones M 13
Carter J 20 500 Watson L 8
Aguilar J 11 600 Regan R 10
Leidner P 13 300 Phan A 12
Russell S 25 300 Phan A 12

The following example illustrates an outer join. In the example, the Example 2: frOm List - Skills table lists various skills and the associated skill number, and

OUTER Join 7 the Employee table lists employee numbers along with the

employee‘s skill.


Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

Derived Tables


SkillNo SkillName
1 baker
2 doctor
3 farmer
4 lawyer
5 mason
6 tailor


EmpNo SkillNo
6123 1
6234 1
6392 3
7281 5
7362 4
6169 1

You could use the following query to determine which skill areas do not have assigned employees:

SELECT Skills.SkillName, Employee.EmpNo FROM Skill LEFT OUTER JOIN Employee ON Skills.SkillNo = Employee.SkillNo;

The following result is returned

SkillName EmpNo
baker 6123
baker 6234
baker 6169
doctor null
farmer 6392
lawyer 7362
mason 7281
tailor null

In order to include all skills in the result, an OUTER JOIN must be used. An implicit join, using just FROM Employee, Skills does not return rows for nulls and would not list doctor or tailor in the above result.

Teradata RDBMS for UNIX SQL Reference

Queries: The SELECT Statement

WITH Clause

WITH Clause

The WITH clause of the SELECT statement specifies summary lines and breaks (grouping conditions) that determine how selected results are returned (typically used for subtotals).



expr1 -

— BY —L expr 2 -





Syntax Element . . . Specifies . . .
WITH the keyword introducing the condition to be fulfilled by the SELECT statement.
expr1 the use of expr following the WITH keyword. It implies a summary line (such as a total) for the values in a column (expr) of the select result. Expr1 can contain one or more aggregate and arithmetic operators that are applied to column values.
BY expr2 one or more result columns (expressions) for which expr1 is provided. BY is valid only when used with WITH. expr2 can be considered as a group condition. Expr2 may refer to an expression in the select expression list either by name or by means of a constant that specifies the numeric position of the expression in the expr-list.
ASC DESC the sort order. The default order is ASC. ASC specifies ascending order. DESC specifies descending order. If a sort option is not given, result values are sorted in ascending order according to the client system’s collating sequence. If ORDER BY is not specified, rows are returned unsorted.


Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

WITH Clause


TITLE Phrases

The following expressions can be used:

• Expressions operated on by aggregate operators (SUM, AVERAGE, COUNT, MIN, or MAX).

An aggregate operator must be specified directly before each column to which the operator applies, for example, WITH SUM(Salary) or MAX(YrsExp).

• Expressions associated with the field values of an expression contained in the BY phrase, for example, WITH DeptNo, SUM(Salary) BY DeptNo.

The WITH clause is a Teradata RDBMS extension to ANSI. If the SQL flagger is enabled, it will be flagged.

A TITLE phrase can be used to specify a title for any expression contained in expr1 and the SELECT expression list. The TITLE phrase must be enclosed by parentheses and follow the entire expression to which it applies.

Title is relevant only for FieldMode output for report generation and normally done only via BTEQ.

In the following statement, the title “Subtotal” is listed at each summary row:

WITH SUM(Salary)(Title 'Subtotal')

In the following statement, a blank title is specified:

WITH SUM(Salary)(Title ' ')

Expr2 is an expression that determines where summary lines are generated. For example, “BY DeptNo” specifies a summary for each value in the DeptNo column; a summary line is generated following a listing of the values for each DeptNo.

If the BY phrase is not used, the summary line applies to the entire result, as specified by the SELECT expression list.

As in the ORDER BY clause, the values of any expression specified by expr2 may be sorted in ascending (ASC) or descending (DESC) order. For example:

WITH SUM(Salary) BY DivNo ASC, DeptNo DESC

Likewise, expr2 may specify a constant that references an expression by its position in the SELECT expression list. For example:


However, an expression that is specified in expr1 or expr2 need not appear in the SELECT expression list.

Teradata RDBMS for UNIX SQL Reference
Previous << 1 .. 92 93 94 95 96 97 < 98 > 99 100 101 102 103 104 .. 241 >> Next