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

-----Original Message----- From: June Tong [SMTP:june_t@hotmail.com] Posted At: Tuesday, June 08, 1999 1:40 AM Posted To: Informix Conversation: Bug ?: Character to numeric error in SQL query Subject: Re: Bug ?: Character to numeric error in SQL query 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... Yes, it can be very frustrating to talk to a first level tech who assumes he knows what he's talking about, but I'm getting off topic. 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. And here I thought I was all alone on this. Isn't this whole Internet thing great? 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, While other customers foresaw these problems and formatted their columns accordingly. Now they're stuck with all of the drawbacks, and none of the advantages. 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. And all of us who need a field to have characters, and still compare this with numbers (don't ask why. Sometimes the users (VP's) ask for dumb things, and sometimes you have to provide them with what they ask for) now suddenly have errors popping up all over the place, where once there were none. And tech support telling us, that this would have always caused an error. (there I go again) 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. I have to admit ignorance on 'to_char'. This is the first time I've heard of it. (But while testing it just now I can only get it to return error 1260). Anyway, we have created our own procedures that do what to_char must be intended to do. It just goes to show; You can please part of the people part of the time... Or as Bart Simpson would say; 'Damned if you do, damned if you don't.' June, thanks for taking the time to elaborate on this. 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.