ANSI Compliance - Teradata Database

The WITH clause is a Teradata extension to the ANSI SQL:2011 standard.

The rules and restrictions for the use of the WITH clause are:

You cannot specify UDT columns in a WITH clause.

You can specify a scalar subquery as a column expression or parameterized value in the BY specification of a WITH … BY clause in a query.

Note that you cannot specify a scalar subquery as the argument of a WITH clause because only aggregate expressions are valid as WITH clause arguments. You can, however, specify a scalar subquery as an argument of an aggregate expression in a WITH clause.

You cannot specify LOB columns in a WITH clause.

A SELECT statement that specifies the TOP n operator cannot also specify a WITH statement modifier.

You can specify more than one WITH clause in a SELECT statement to specify different kinds of summaries. Each succeeding WITH clause refers to an ever broader grouping of rows as follows.

The BY phrase in the first WITH clause defines the least important sort key.

The BY phrase in the next WITH clause defines the next‑to‑least important sort key.

The final WITH clause defines the major sort key.

See “Example 2: Specifying Multiple WITH Clauses in a Single SELECT Statement” on page 222.

You can specify an ORDER BY clause before or after any WITH clause. Also see “ORDER BY Clause” on page 202.

WITH and ORDER BY clauses in the same SELECT statement function together as follows:

The WITH clause defines the major sort key.

The ORDER BY clause defines the minor sort key.

This is true regardless of the structure of the query or the number of WITH clauses.

See “Example 3: Combining WITH and ORDER BY Clauses” on page 223.

You should not combine WITH and GROUP BY clauses in a SELECT statement.

Specifying WITH and GROUP BY clauses in the same SELECT statement can produce unintended results.

See “Example 3: Combining WITH and ORDER BY Clauses” on page 223.

Also see “GROUP BY Clause” on page 127.

The expression_2 you specify determines where summary lines are generated. For example, BY dept_no specifies a summary for each value in the dept_no column; a summary line is generated following a listing of the values for each department number.

If you do not specify a BY phrase, the summary line applies to the entire result as specified by the SELECT expression list.

Like the ORDER BY clause, the values of any expression specified by expression_2 can be sorted in either ascending or descending order. For example:

     WITH SUM(salary) BY divno ASC, dept_no DESC

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

     WITH SUM(salary) BY 2 ASC, 3 DESC

However, an expression that is specified in expression_1 or expression_2 need not be specified in the SELECT expression list.

You can specify the following expressions in a WITH clause.

Expressions operated on by aggregate operators (for example, 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 column values of an expression contained in the BY phrase, for example, WITH dept_no, SUM(salary) BY dept_no.

You cannot specify expressions that include LOB or UDT columns in a WITH clause.

You can use a TITLE phrase to specify a title for any valid expression contained in expression_1 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.

This clause lists the title Subtotal at each summary row:

     WITH SUM(salary)(TITLE 'Subtotal')

This clause specifies a blank title:

     WITH SUM(salary)(TITLE ' ')

See “Example 1: Specifying a Detail Line For Each Employee and a Subtotal Line For Each Department” on page 222.