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

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

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 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.

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.