Internal subquery aggregates

Certain types of subqueries require special internal aggregates, as listed in Table 5-4. Adaptive Server generates these aggregates internally – they are not part of Transact-SQL syntax and cannot be included in user queries.

Table 5-4: Internal subquery aggregates

Subquery type

Aggregate

Effect

Quantified predicate

ANY

Returns TRUE or FALSE to the outer query.

Expression

ONCE

Returns the result of the subquery. Raises error 512 if the subquery returns more than one value.

Subquery containing distinct

ONCE-UNIQUE

Stores the first subquery result internally and compares each subsequent result to the first. Raises error 512 if a subsequent result differs from the first.

Messages for internal aggregates include “Grouped” when the subquery includes a group by clause and computes the aggregate for a group of rows, otherwise the messages include “Ungrouped”; the subquery the aggregate for all rows in the table that satisfy the correlation clause.