WITH Clause | SQL SELECT Statements | Teradata Vantage - 17.05 - WITH Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

Specifies summary lines and breaks (also known as grouping conditions) that determine how selected results are returned.

The typical use of a WITH clause is with subtotals.

The WITH clause has a different function than the WITH statement modifier. See WITH Modifier.

ANSI Compliance

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

UDT Columns and the WITH Clause

You cannot specify UDT columns in a WITH clause.

Scalar Subqueries in the BY Specification of a WITH … BY 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.

LOB columns and the WITH Clause

You cannot specify LOB columns in a WITH clause.

TOP Operator and WITH Statement Modifier

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

Multiple WITH Clauses in a SELECT Statement

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 WITH Clause Examples.

ORDER BY Clause and WITH Clause

You can specify an ORDER BY clause before or after any WITH clause. Also see ORDER BY Clause.
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 WITH Clause Examples.

WITH and GROUP BY clauses in a SELECT Statement

Do 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 WITH Clause Examples.

Also see GROUP BY Clause.

Expressions and the WITH Clause

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.

TITLE Phrase and the 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 WITH Clause Examples.