Results 1 to 3 of 3

Thread: Wrong resultset

  1. #1
    Join Date
    Jul 2009
    Posts
    12

    Unanswered: Wrong resultset

    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.

  2. #2
    Join Date
    Oct 2007
    Posts
    4
    Hi.

    The problem is that your field is CHAR(11) and when you queries for '226630',
    youre 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.

  3. #3
    Join Date
    Oct 2007
    Posts
    4
    Im sorry but in my last post, text editor has cutted trailin blanks in fields. Stored value is "226630.....", where each "." means a space.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •