Data type conversion in query

Does anyone out there know how to force the optimizer/engine to convert data types in a query? I have a query that is joining a CHAR(20) column to an INTEGER column. Right now it is trying to convert the CHAR to an INTEGER which won't work. If I could make it convert the INTEGER to a CHAR it would work. Thanks for any suggestions.

I believe - but am not running to the books right now, but probably depending on your version, you can force the datatype to be converted by using a casting operator like :: as in mycharfield::integer would force a field defined as a character to an integer. obviously this will not work if the char field contains non-integer characters, or the field is longer than what an integer will hold... in 9.20 this would work for example... select * from customer where '501'::integer = 501 Anthony Judish wrote: > Does anyone out there know how to force the optimizer/engine to convert data > types in a query? I have a query that is joining a CHAR(20) column to an > INTEGER column. Right now it is trying to convert the CHAR to an INTEGER > which won't work. If I could make it convert the INTEGER to a CHAR it would > work. > > Thanks for any suggestions.

It would work if you do something like this: Select ... from t1, t2, ... where (t1.c1 || "") = (t2.c2 || "") .......... This will convince informix to convert both to CHAR type. The same will work for "<", ">" or "<>" comparison as well. Regards, Carl Y. Wu Anthony Judish wrote in message ... >Does anyone out there know how to force the optimizer/engine to convert data >types in a query? I have a query that is joining a CHAR(20) column to an >INTEGER column. Right now it is trying to convert the CHAR to an INTEGER >which won't work. If I could make it convert the INTEGER to a CHAR it would >work. > >Thanks for any suggestions. > >