Hi everybody...

I'm facing a strange situation, at least to me..
I have a query like the following one:

select field1, field2, field3
from table
where field1 = 1100004
and field2 = '226630'
order by field3

This query doesn't return any record, however if I replace

and field2 = '226630'

by

and field2 = 226630

, i.e., the compared value without quotes, it already returns a record. The weird thing is that field2 is a char(11) field.
Any tip? I really don't know what it could be.

Best Regards.

Hi.

The problem is that your field is CHAR(11) and when you queries for '226630',
you´re looking exactly for that chain but item in fact is stored as '226630 ' (with 5 trailing blanks).
When you queries for 226630 (as numeric value), Informix autocast the value to match CHAR(11) and converts it to '226630 ".

Kind regards.

I´m sorry but in my last post, text editor has cutted trailin blanks in fields. Stored value is "226630.....", where each "." means a space.