Character to numeric conversion - Version Informix 7.30 FC3

We are using Informix 7.30.FC3 running on a HP9000 V2200 box on HPUX 11.00 The sitiuation that I have is that I need to join 2 tables on a field. The field that I need to join is defined as char on one table and integer on another. I believe there is a function called to_char which on this version of Informix works with date fields. Is there something that I can use to convert a char value to a numeric value and allow me to join these two tables? Any help would be greatly appreciated. Thanks Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

pygk@my-deja.com wrote: > > We are using Informix 7.30.FC3 running on a HP9000 V2200 box on HPUX 11.00 > > The sitiuation that I have is that I need to join 2 tables on a field. The > field that I need to join is defined as char on one table and integer on > another. > > I believe there is a function called to_char which on this version of > Informix works with date fields. Is there something that I can use to > convert a char value to a numeric value and allow me to join these two > tables? Third time this week for the same question, candidate for the FAQ Marco? Going beyond the question of how you got yourself into this situation in the first place, informix will autmatically convert the character string to an integer to make the comparison which will work fine as long as the char column actually contains only numeric strings. If any row contains an alpha string the query will return a -1213 error, character to numeric conversion error. I believe that you can use to_char to force the comparison to happen as a string compare but that may not return the results you want (since to_char(9) is NOT the same string as "0009" while they resolve to the same integer). Art S. Kagel