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 .. 95 96 97 98 99 100 < 101 > 102 103 104 105 106 107 .. 241 >> Next


Aggregating a Join in a columns from two or more tables.

Having Clause

The columns named price and sales_qty are from two different Example: tables t1, UnitPriceCost, and t2, SalesHist. To find which category of

items is sold for a profit margin greater than $1000:

SELECT t1.category,

(t2.price - t2.cost) * SUM (t1.sales_qty)(NAMED margin)

FROM SalesHist t1, UnitPriceCost t2 WHERE t1.prodno=t2.prodno GROUP BY t1.category,t2.price, t2.cost HAVING margin > 1000;

A subquery can have a join on a view with an aggregate operation and a HAVING clause which references more than one table.

Example 2: HAVING

Example 1: HAVING

Teradata RDBMS for UNIX SQL Reference

7-33
Queries: The SELECT Statement

ORDER BY Clause

ORDER BY Clause

The ORDER BY clause of the SELECT statement specifies how result

Function data is to be sorted.

Syntax

-ORDER BY

expr
-col_name - -ASC-
col_pos Ldesc-I

FF06A017

where:

Syntax Element . . . Specifies . . .
ORDER BY the order in which result rows are to be sorted.
col_name the name(s) of columns used in the ORDER BY clause in the SELECT statement. These can be ascending or descending.
col_pos the numeric position of the column(s) used in the ORDER BY clause. This can be ascending or descending.
expr an expression on which to sort. If the sort field is a character string, the expr used in the ORDER BY phrase can include a type modifier to force the sort to be either CASESPECIFIC or NOT CASESPECIFIC. See “Specifying Case for Character Data” in Chapter 5, “Data Definition.”
ASC ascending sort order. The default order is ASC. 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.
DESC descending sort order.

Use of expr in ORDER BY is a Teradata RDBMS extension to ANSI SQL.

7-34

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

ORDER BY Clause

Each col_name in an ORDER BY clause is the name of a column in a CoIumn Name table or view referenced in the SELECT expression list. The columns

named do not have to match the columns in the SELECT expression list.

The column position (col_pos) is a positive integer that refers to the CoIumn p°siti°n numeric position of a column in the SELECT expression list. The

column position cannot be passed as a parameter; attempts to do so cause it to be interpreted as an expression, and the data to be sorted by a constant.

If a SELECT statement includes multiple parameters such as:

Øå parentheses f°r • FORMAT or NAMED, or if it involves

Required Sort Order

• type conversion,

and if an ORDER BY clause is part of the query, use parentheses to group the parameters so that they are processed in the proper sequence. Without parentheses, the parameters will be processed in left to right order.

Sorting and Default Sort Order

The Teradata RDBMS sorts nulls to follow all other data in a column. IBM’s DB2 and SQL/DS sort nulls to appear before other data in a column. This difference may cause an error in an application that expects nulls to be handled identically to DB2 or SQL/DS.

By default, the result values of a character expr are sorted in ascending order, using the collating sequence in effect for the session.

The sort key created by the ORDER BY clause is constructed by concatenating the expr values. The maximum size of the sort key is limited to 4096 bytes. If the sort key is greater than 4096 bytes, the key is truncated and the data may or may not come back in the desired order.

Collation can be specified as an attribute of the user via the CREATE SPecifying C°llation USER or MODIFY USER statement, and can be requested at the

session level via the SET SESSION COLLATION statement. Otherwise, collation for a session is determined by the logon client system. The direction of the sort can be controlled by including the DESC (descending) or ASC (ascending) sort option in the SQL request.

The Teradata RDBMS supports ASCII, EBCDIC, and MULTINATIONAL collating sequences. If MULTINATIONAL is in effect, your collation will be one of the European (diacritical) or Kanji sort sequences described later in this section under the passage titled “International Sort Orders”.

Teradata RDBMS for UNIX SQL Reference

7-35
Queries: The SELECT Statement

ORDER BY Clause

Case Sensitivity

NOT CASESPECIFIC Sorts With Japanese Character Data

International Sort Orders

The following subsections explain the results of ORDER BY as affected by whether character string expressions have the CASESPECIFIC or NOTCASESPECIFIC attribute.

For details on Case Sensitivity, see Chapter 5, “Data Definition” in the section “Specifying Case for Character Data”.

On a Japanese character site, if the character strings to be sorted have the NOT CASESPECIFIC attribute, only lowercase simple Latin letters (a...z) are converted to uppercase before a comparison or sorting operation is performed.
Previous << 1 .. 95 96 97 98 99 100 < 101 > 102 103 104 105 106 107 .. 241 >> Next