WITH Clause - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Purpose

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

Syntax



Syntax Elements

WITH expression_1
An introduction to the condition to be fulfilled by the SELECT statement. Specifies a summary line, such as a total, for the values in a column of the select result. expression_1 can contain one or more aggregate expressions that are applied to column values.
You cannot include LOB columns in the WITH expression_1 list.
You cannot include a WITH clause with NORMALIZE.
BY expression_2
One or more result expressions for which expression_1 is provided. BY is valid only when used with WITH.
expression_2 can 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 expression list. It can also refer to a scalar subquery.
You cannot include LOB columns in the BY expression_2 list.
ASC
Results are to be ordered in ascending sort order.
If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.
The default order is ASC.
DESC
Results are to be ordered in descending sort order.
If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

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.

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 Example: Combining WITH and ORDER BY Clauses.

WITH and GROUP BY clauses in a SELECT Statement

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: Combining WITH and ORDER BY Clauses.

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 Example: Specifying a Detail Line For Each Employee and a Subtotal Line For Each Department.

Example: Specifying a Detail Line For Each Employee and a Subtotal Line For Each Department

You can use the following statement to generate a departmental salary report that contains a detail line for each employee and a subtotal line for each department:

     SELECT name, dept_no, salary
     FROM employee
     WITH SUM(salary) BY dept_no;

The result returned is as follows:

     name         dept_no       salary
     ----         -------       ----------
     Peterson J     100         25,000.00
     Moffit H       100         35,000.00
     Jones M        100         50,000.00
     Chin M         100         38,000.00
     Greene W       100         32,500.00
                                ----------
                   Sum(Salary) 180,500.00
     Leidner P       300        34,000.00
     Phan A          300        55,000.00
     Russell S       300        65,000.00
                               ----------
                   Sum(Salary)  154,000.00

Example: Specifying Multiple WITH Clauses in a Single SELECT Statement

The following statement generates a report of employee salaries ordered by department, with a summary line of total salaries for each department and a final summary line of total salaries for the entire organization:

     SELECT name, dep_tno, salary
     FROM employee
     WITH SUM(salary) BY dept_no
     WITH SUM(salary);

The result returned is as follows:

     name        dept_no        salary
     ----------  -------   -----------
     Peterson J      100     25,000.00
     Moffit H        100     35,000.00
     Jones M         100     50,000.00
     Chin M          100     38,000.00
     Greene W        100     32,000.00
                            ----------
                Sum(Salary) 180,000.00
     Leidner P       300     34,000.00
     Phan A          300     55,000.00
     Russell S       300     65,000.00
                           ----------
                Sum(Salary) 154,000.00
     Smith T         500       42,000.00
     Clements D      700     38,000.00
                            ----------
                Sum(Salary) 113,000.00
                            ----------
                Sum(Salary) 851,000.00

Example: Combining WITH and ORDER BY Clauses

Both of the following statements use an ORDER BY clause to sort employee names in ascending order in each dept_no group.

     SELECT name, dept_no, salary
     FROM employee
     ORDER BY name
     WITH SUM(salary) BY dept_no
     WITH SUM(salary);
     SELECT name, dept_no, salary
     FROM employee
     WITH SUM(salary) BY dept_no
     WITH SUM(salary)
     ORDER BY name;

The result returned is as follows.

     name        dept_no        salary
     ----------  -------   -----------
     Chin M          100     38,000.00
     Greene W        100     32,500.00
     Jones M         100     50,000.00
     Moffit H        100     35,000.00
     Peterson J      100     25,000.00
                            ----------
                 Sum(Salary)   180,500.00
        .             .        .
        .             .        .
        .             .        .
     Brangle B       700     30,000.00
     Clements D      700     38,000.00
     Smith T         700     45,000.00
                            ----------
                 Sum(Salary)   113,000.00
                            ----------
                 Sum(Salary)   851,100.00

If any sort key column contains character data that was entered in mixed case, the results produced from WITH...BY or ORDER BY can be unexpected, depending on whether the CASESPECIFIC option was defined on the column and the collation in effect for the session. See “ORDER BY Clause” and SQL Data Definition Language .

You must code your statements carefully to avoid returning unintended result sets from combining GROUP BY and WITH clauses. The next example illustrates a possible problem with careless coding of combined GROUP BY and WITH clauses.

The following statement is intended to generate a report of salary totals by dept_no with a grand total of employee salaries:

     SELECT dept_no, SUM(salary)
     FROM employee
     GROUP BY dept_no
     WITH SUM(salary);

The result returned is as follows:

     dept_no    Sum(Salary)
     -------   -----------
         100    180,500.00
         300    143,000.00
         500    268,000.00
         600    146,600.00
         700    113,000.00
               -----------
      Sum(Sa    851,100.00

As would be expected, the WITH clause produces a summary line of total salaries for all departments. Note that the summary title is truncated because of the narrow width of the dept_no column.

If a WITH clause contains an unnecessary BY phrase, then a useless summary line is generated following each department salary total as seen in the report following this example query:

     SELECT dept_no, SUM(salary)
     FROM employee
     GROUP BY dept_no
     WITH SUM(salary) BY dept_no;
     dept_no   Sum(Salary)
     -------   ----------
     100       180,500.00
               ----------
     Sum(Sa    180,500.00
               .
               .
               .
     700        113,000.00
               -----------
     Sum(Sa     113,000.00

Example: Scalar Subquery in the WITH … BY Clause of a SELECT Statement

The following example specifies a scalar subquery (SELECT prod_name…) in its BY clause.

     SELECT SUM(amount)
     FROM sales_table AS s
     WITH AVG(amount) BY (SELECT prod_name
                          FROM prod_table AS p
                          WHERE p.prod_no = s.prod_no);