Rules for COMPUTE Clauses

These are the rules for COMPUTE clauses:

• The DISTINCT keyword is not allowed with row aggregate functions.
• The columns in a COMPUTE clause must appear in the statement's select list.
• You cannot use SELECT INTO in the same statement as a COMPUTE clause because statements that include COMPUTE do not generate normal rows.
• If you use COMPUTE BY, you must also use an ORDER BY clause. The columns listed after COMPUTE BY must be identical to or a subset of those listed after ORDER BY. They must be in the same left-to-right order, start with the same expression, and not skip any expressions.

  For example, if the ORDER BY clause is:

  order by a, b, c

  The COMPUTE BY clause can be any of these:

  compute row_aggregate (column_name) by a, b, c
  compute row_aggregate (column_name) by a, b
  compute row_aggregate (column_name) by a

  The COMPUTE BY clause cannot be any of these:

  compute row_aggregate (column_name) by b, c
  compute row_aggregate (column_name) by a, c
  compute row_aggregate (column_name) by c

• You must use a column name or an expression in the ORDER BY clause; you cannot sort by a column heading.
• The COMPUTE keyword can be used without BY to generate grand totals, grand counts, and so on. ORDER BY is optional if you use the COMPUTE keyword without BY. (COMPUTE without BY is discussed later in this chapter.)