SQLCODE -120: Aggregate function not valid in context

What it means

An aggregate function works on a set of rows and returns one value. It only makes sense where there is a set to work on, which means the SELECT list and HAVING clause of a query. An OLAP specification such as ROW_NUMBER() OVER or RANK() OVER is computed over the rows of the result, so it has the same kind of limit.

Db2 for z/OS returns -120 when one of these appears somewhere else. The Db2 12 codes list the forbidden places. They include a SET clause, a VALUES clause, a SET transition-variable statement in a trigger, an assignment statement, a RETURN statement, the key expression of an index, and the ON condition of a MERGE. In a WHERE clause they're allowed only inside a subquery of a HAVING clause.

The statement is rejected as a whole. With static SQL this happens at bind time. With dynamic SQL it happens on PREPARE.

Common causes

• Comparing a row against a total in WHERE, as in WHERE SALARY > AVG(SALARY).
• Filtering on ROW_NUMBER() or RANK() in WHERE to get the top rows of each group.
• An UPDATE that sets a column to an aggregate directly, as in SET TOTAL = SUM(AMOUNT).
• An aggregate in the VALUES list of an INSERT.
• An aggregate in a trigger's SET transition-variable statement or in a SQL routine's assignment.

How to find the problem

1. Find the aggregate or OLAP function named in the statement, and note which clause it is in.
2. For static SQL, the bind job's DSNX200I message gives the statement number in the DBRM.
3. Check whether the value is meant to be per row or per group. That decides which rewrite you need.

How to fix it

• Put the aggregate in a subquery. WHERE SALARY > (SELECT AVG(SALARY) FROM EMP) is valid because the subquery has its own SELECT list.
• In an UPDATE, set the column from a scalar subquery, such as SET TOTAL = (SELECT SUM(AMOUNT) FROM ORDER_LINE L WHERE L.ORDER_NO = O.ORDER_NO).
• To filter on ROW_NUMBER() or RANK(), compute it in a common table expression or nested table expression, then filter the outer query.
• Move a group-level test into HAVING if the query is already grouped.

What it looks like

DSNT408I SQLCODE = -120, ERROR:  AN AGGREGATE FUNCTION OR OLAP SPECIFICATION IS NOT VALID IN
         THE CONTEXT IN WHICH IT WAS INVOKED
DSNT418I SQLSTATE   = 42903 SQLSTATE RETURN CODE

Technical reviewer: Albert Joseph, WND Software.

Last reviewed 2026-10-18 by the WND Software team. Written in our own words. For the full reference, check IBM’s Db2 for z/OS documentation for your release.