Informix Error -19820: Informix OUTER JOIN and ANSI JOIN in the same query block.

Cause and resolution

Informix OUTER JOIN and ANSI JOIN in the same query block.

You cannot have Informix OUTER JOIN and ANSI JOIN in the same query block. Informix OUTER JOIN and ANSI JOIN are two different mechanisms and should not be combined in a single query. You can use only one of these.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-19820 fires when a query block mixes the Informix OUTER join syntax with ANSI JOIN syntax — per the official guidance. These are two different mechanisms and can't be combined in a single query.

Both Informix OUTER and ANSI JOIN syntax used in the same query block, per the official guidance — the direct, only cause.

Solutions / Resolution

Use only one of the two join mechanisms per query block, per the official guidance.

Examples

Mixing OUTER and ANSI JOIN

SELECT * FROM t1, OUTER t2, t3 JOIN t4 ON t3.id = t4.id;

Corrected — ANSI JOIN throughout

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
  JOIN t3 ON t1.id = t3.id
  JOIN t4 ON t3.id = t4.id;

Diagnostic Checks

Check the query block for both OUTER and ANSI JOIN syntax, and convert to one consistently.

Related Errors / Related Topics

-19819 — "An ON-clause has an invalid table reference." A related JOIN-syntax error, about an ON clause's table scope rather than mixing join mechanisms.

A query block mixes Informix OUTER and ANSI JOIN syntax — use only one mechanism.