Informix Error -595: Incorrect use of an aggregate function.

Reasons / Common Causes

-595 fires when an aggregate function (SUM, COUNT, MAX, and similar) appears somewhere it can't be evaluated — per the official guidance, inside an SPL routine expression, as an argument to an iterator table function, or inside a CHECK constraint. Per the official guidance's explanation, these contexts all deal with a single value at a time, so an aggregate — which is defined over a group of rows — is meaningless there.

1. An aggregate function used directly in an SPL routine expression (an IF condition, a variable assignment outside a SELECT INTO), per the official guidance.
2. An aggregate function passed as an argument to an iterator table function, per the official guidance.
3. An aggregate function used inside a CHECK constraint's expression, per the official guidance — a check constraint evaluates against one row's values, not a group.

Solutions / Resolution

1. Compute the aggregate via a SELECT ... INTO first, in an SPL routine, then use the resulting single value in subsequent expressions:

   DEFINE v_total DECIMAL;
   SELECT SUM(order_total) INTO v_total FROM orders WHERE customer_id = p_customer_id;
   IF v_total > 1000 THEN ...
   

2. For a check constraint, express the rule in terms of the row's own columns only, per the official guidance's underlying point — a check constraint can't reference an aggregate over other rows at all; if the intended rule genuinely needs one, it isn't expressible as a CHECK constraint and needs a trigger or application-level enforcement instead.
3. For an iterator table function argument, compute the aggregate separately beforehand and pass the resulting scalar value in, rather than the aggregate expression itself.

Examples

The disallowed use inside an SPL routine

CREATE PROCEDURE flag_big_spenders(p_customer_id INT)
  IF SUM(SELECT order_total FROM orders WHERE customer_id = p_customer_id) > 1000 THEN
    ...
  END IF;
END PROCEDURE;
-- -595: aggregate used directly in an IF expression

Corrected — compute it first

CREATE PROCEDURE flag_big_spenders(p_customer_id INT)
  DEFINE v_total DECIMAL;
  SELECT SUM(order_total) INTO v_total FROM orders WHERE customer_id = p_customer_id;
  IF v_total > 1000 THEN
    ...
  END IF;
END PROCEDURE;

Diagnostic Checks

1. Identify which of the three contexts (SPL expression, iterator table function argument, check constraint) the aggregate appears in, and restructure it to compute the aggregate separately first.

Related Errors / Related Topics

• -544 — "Cannot have aggregates within aggregates." A related aggregate-usage restriction, about nesting aggregates rather than using one in a context that can't evaluate aggregates at all.

Compute the aggregate via SELECT ... INTO first, then use the resulting scalar value — none of the three restricted contexts can evaluate an aggregate directly.