Oninit
Talk to an expert →

Informix Error -293

-293 IS [NOT] NULL predicate may be used only with simple

columns.

The test for a null value can be applied only to a column name (not to a subscripted character column or an expression, for example).

Review the use of these keywords in the WHERE clause and in any CASE statements.

This message does not apply to 8.21 and later 8.x database servers, which allow expressions as arguments of the IS [NOT] NULL operator.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-293 is a specific restriction with an important version-gated exception worth checking first: IS NULL/IS NOT NULL could only be applied to a bare column name — not a subscripted character column or a general expression — on versions before 8.21.

  1. Applying IS [NOT] NULL to something other than a simple column name — a subscripted character column (substring notation) or a general expression, on an affected version.
  2. Using IS [NOT] NULL against an expression inside a WHERE clause or a CASE statement, rather than a bare column.
  3. Version confusion — assuming a current version's more permissive behavior applies universally, when the restriction is still real on versions before 8.21.
Solutions / Resolution
  1. Restrict IS [NOT] NULL usage to simple column names on affected versions, per the official guidance.
  2. Check the actual server version — 8.21 and later 8.x database servers allow expressions as arguments to IS [NOT] NULL directly, so this restriction may simply not apply.
  3. If genuinely needing to test an expression for null on an older version, restructure the query to test the underlying column directly, or compute the expression into a variable/temp column first and test that.
Examples
The restricted usage, pre-8.21
SELECT * FROM customer WHERE name[1,5] IS NULL;
-- -293 on versions before 8.21: a subscripted expression, not a
-- simple column name
Confirming version support
onstat -
-- check the server version; 8.21+ allows expressions directly
Testing the base column instead, on affected versions
SELECT * FROM customer WHERE name IS NULL;
-- valid — a simple column name
Diagnostic Checks
  1. Review the WHERE clause or CASE statement for IS [NOT] NULL applied to something other than a bare column name.
  2. Confirm the server version in use — 8.21 and later relax this restriction.
Related Errors / Related Topics
  • -219 — "Wildcard matching may not be used with non-character types." Another operator-specific restriction on what kind of column or expression it can apply to.
  • -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.

Check the server version first — this restriction may simply not apply on 8.21 and later.