Certain types of subqueries require special internal aggregates, as listed in Table 36-4. Adaptive Server generates these aggregates internally – they are not part of Transact-SQL syntax and cannot be included in user queries.
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.
