Oninit
Talk to an expert →

Informix Error -800: Corresponding data types must be compatible in CASE expression or DECODE function.

Cause and resolution

Corresponding data types must be compatible in CASE expression or DECODE function.

All the result values in all the WHEN clauses in the CASE expression should be of compatible data types. In the linearized use of the CASE expression, the value-expression that follows the CASE keyword should be compatible with the value-expressions that follow all the WHEN keywords in the CASE expression. Reissue the query after modifying the CASE expression so that all related expressions are of compatible data types.

This error can also occur when the expressions for the DECODE function do not have compatible data types. The DECODE function has four possible expressions: expr, when_expr, then_expr, and else_expr. All instances of when_expr must have the same or a compatible data type as expr. All instances of then_expr must have the same or a compatible data type as else_expr. Reissue the query after modifying the DECODE function so that all related expressions are of same or compatible data types.

This error can also occur when calling functions that use implicit casts and comparisons between data types, such as the NVL function. In this case, revise your program logic (for example, by adding an explicit cast). so that the expressions return the same or compatible data types.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-800 fires when a CASE expression's WHEN/result values, or a DECODE function's arguments, don't all resolve to compatible data types — per the official guidance, several related-but- distinct rules apply depending on the exact form used.

  1. A CASE expression's result values (after each WHEN ... THEN) of incompatible types — per the official guidance, all result values must be mutually compatible.
  2. A linearized (searched) CASE's leading value-expression incompatible with the expressions following WHEN, per the official guidance — the specific form comparing one value against several WHEN expressions.
  3. A DECODE function where the when_expr arguments don't all match expr's type, or the then_expr arguments don't all match else_expr's type, per the official guidance — two separate compatibility requirements within one DECODE call.
  4. A function using implicit casts, such as NVL, per the official guidance's specific note — these can also trigger this error when their arguments aren't compatible, even though they don't use CASE/DECODE syntax directly.
Solutions / Resolution
  1. Review each WHEN/result pair for type compatibility, per the official guidance, for ordinary CASE expressions.
  2. For DECODE, check when_expr against expr's type and then_expr against else_expr's type separately, per the official guidance — these are two distinct checks.
  3. Add explicit casts, per the official guidance's recommendation — this resolves ambiguous or borderline-incompatible type combinations across all these forms (CASE, DECODE, and implicit-cast functions like NVL), rather than relying on implicit conversion.
Examples
Mismatched CASE result types
SELECT CASE WHEN status = 'shipped' THEN 1 ELSE 'pending' END FROM orders;
-- -800: one result is INT, the other is a character literal
Corrected — explicit casts for compatible types
SELECT CASE WHEN status = 'shipped' THEN '1'::VARCHAR(10) ELSE 'pending' END FROM orders;
Diagnostic Checks
  1. Identify the specific form involved (CASE, DECODE, or an implicit-cast function like NVL), and check the corresponding compatibility rule for that form.
Related Errors / Related Topics

No closely related error codes are cross-referenced for -800 in this set yet.

Several related but distinct compatibility rules share this one message — identify which form (CASE, DECODE, implicit-cast function) is involved, then add explicit casts.