Informix Error -940: The statement failed, becuase the WITH CHECK OPTION keywords are not supported in Union, Intersect, or Minus views.

Reasons / Common Causes

-940 fires when CREATE VIEW defines a view based on UNION, INTERSECT, or MINUS and includes WITH CHECK OPTION — per the official guidance, this combination isn't supported.

1. A UNION/INTERSECT/MINUS view definition including WITH CHECK OPTION, per the official guidance — the direct, only cause; check options require a well-defined mapping back to a single underlying table's rows, which set-operation views don't have.

Solutions / Resolution

1. Redefine the view without the check options clause, per the official guidance — this is the documented, only resolution, since the combination isn't supported under any circumstances.
2. If row-validation logic was the actual goal of the check option, enforce it via a trigger on the underlying base table(s) instead — the same general workaround pattern used elsewhere in this reference when a declarative mechanism doesn't cover a needed case.

Examples

The disallowed combination

CREATE VIEW combined_orders AS
  SELECT * FROM orders_us
  UNION
  SELECT * FROM orders_eu
  WITH CHECK OPTION;
-- -940: WITH CHECK OPTION isn't supported on a UNION view

Corrected — remove the check option

CREATE VIEW combined_orders AS
  SELECT * FROM orders_us
  UNION
  SELECT * FROM orders_eu;

Diagnostic Checks

1. Check whether the view's definition uses UNION/INTERSECT/MINUS, and remove WITH CHECK OPTION if so.

Related Errors / Related Topics

• -767 — "Cannot UPDATE/INSERT a remote table using views with check options." A related check-option restriction, about remote tables rather than set-operation views.

WITH CHECK OPTION is never supported on UNION/INTERSECT/MINUS views — remove it, or enforce the validation via a trigger on the base tables instead.