RE: Bug ?: Character to numeric error in SQL query

In previous versions, Informix would convert from numeric to character, then do the comparison. Now they convert from character to numeric. I have gone round and round with tech support on this. They deny it, but like you, I've seen it happen. All you can do is create stored procedures that convert the number to a character in advance instead of using the implicit conversion. It's a pain I the ass, but I've had to do it more times than I can count. Forgive my ranting, but this has become a sore issue with me. -----Original Message----- From: Christian Allaire [SMTP:callaire@ergonet.com] Posted At: Tuesday, May 25, 1999 9:58 AM Posted To: Informix Conversation: Bug ?: Character to numeric error in SQL query Subject: Bug ?: Character to numeric error in SQL query I used to be able to to the following request in SE 4.10: select w.work_num,s.sort_name,w.date_created,q.quote_date,t.date,s.inst_date, w.date_required from site s,work_h w,work_l l,quote_h q,translog t where w.work_type = "WTI" and l.work_num = w.work_num and q.ship_num = l.ship_num and q.work_num = l.work_num and s.sort_name = l.ship_num and s.inst_date > "05/01/1996" and t.doc_typ = "QUO" and t.doc_num = q.quote_num; But now in SE 7.10 I get: # 1213: Character to numeric conversion error This is because of the last line in the query: t.doc_num is a char(10) and q.quote_num is an integer SE will no longer do an implicit conversion of integer to character. Should this be considered a bug since it used to work in SE 4.10 ? Thanks for your input, Chris

Scott Black wrote: > In previous versions, Informix would convert from numeric to character, > then do the comparison. Now they convert from character to numeric. I > have gone round and round with tech support on this. They deny it, but > like you, I've seen it happen. All you can do is create stored > procedures that convert the number to a character in advance instead of > using the implicit conversion. It's a pain I the ass, but I've had to > do it more times than I can count. I don't think they deny it; it happened so long ago you probably haven't talked to anyone who's been around long enough to remember. I, on the other hand, being older than dirt... As a matter of fact, I documented this change, and the reasons for it, on my unofficial FAQ http://www.geocities.com/SiliconValley/Bridge/4578 ; I don't know if David put this part into the official FAQ. Quite simply, the old way of converting numeric to character was causing some tests to fail which some people thought should not fail. E.g. if you had "0001" in your character field, and compared it to integer 1: should they be equal or not? Well apparently some people (customers, I might add) thought they should, but if you convert the integer to char, and get "1", then they don't. So Informix "fixed the bug", and now characters get converted to integer, rather than the other way around. I suppose Informix could have simply been uncooperative like some other databases I'm working with now, which force you to call CONVERT or TO_CHAR or whatever and convert it yourself. That is still an option for you, TO_CHAR, I mean, if you want to change which field gets converted. June -- june_t@hotmail.com Still alive (believe it or not) and still living on Oreo's Please do not send Informix questions to this account. I would add 'Please do not send spam to this account' but I suppose I would be wasting my bits.