Oninit
Talk to an expert →

Informix Error -4385: Report aggregates cannot be nested.

Cause and resolution

Report aggregates cannot be nested.

Aggregate functions cannot be nested, primarily because the value of the inner aggregate is not known at the time the outer aggregate is being accumulated. Rewrite aggregate expressions to refer only to columns and simple expressions on columns. In an AFTER GROUP OF clause, you can save the aggregate value from one group of rows in a program variable, so as to use it in computing an aggregate over a subsequent group.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-4385 fires when one aggregate function is nested inside another — per the official guidance, the inner aggregate's value isn't known at the time the outer aggregate is being accumulated.

  1. An aggregate function nested inside another aggregate function's argument, per the official guidance — the direct, only cause.
Solutions / Resolution
  1. Rewrite aggregate expressions to refer only to columns and simple expressions on columns, per the official guidance.
  2. In an AFTER GROUP OF clause, save one group's aggregate value in a program variable to use it in computing an aggregate over a subsequent group, per the official guidance.
Examples
A nested aggregate
PRINT SUM(AVG(qty))
-- -4385: AVG's result isn't known while SUM accumulates
Corrected
AFTER GROUP OF rec.customer
  LET group_avg = AVG(rec.qty)
AFTER GROUP OF rec.region
  LET region_total = SUM(group_avg)
Diagnostic Checks
  1. Check every aggregate expression for a nested aggregate function, and rewrite it to use a saved program variable from an earlier AFTER GROUP OF clause instead.
Related Errors / Related Topics
  • -4361 — "Group aggregates can occur only in AFTER GROUP clauses." A related aggregate-usage restriction, about where group aggregates can appear rather than nesting.

An aggregate function is nested inside another — save the inner value via AFTER GROUP OF instead.