Re: Character to numeric conversion error

Scott Black wrote: > > HP-UX 10.20 > IDS 7.3 > > Here's a problem that I've noticed going back several versions. > Consider the following select; > > select * from systables > where tabname in (select tabid from systables)> > Now obviously unless you have a table named with a number this will > return no rows, but the query is for simplicity sake. > > This will give a character to number conversion error because it will > try to convert tabname to an integer and then do the comparison. My > question is, shouldn't it convert tabid to a character and then do the > comparison. Shouldn't this be the same as; > > select * from systables > where tabname in ("1","2","3","4","5",...)> > I have several queries on tables with fields that may contain a number > or a character. If it contains a number, I want to join it to a field > in another table containing only numbers. The way Informix is doing the > conversion, my queries never work. This looks like a very odd database design to me. Does this column contain variant values, ie a context-dependent meaning? If so you are not even in first normal form and it's expecting a lot for a relational database to be able to deal with it in all circumstances. If you ever decided to port this to another database it would be a matter of luck whether it worked. Other than changing the design, I suggest you convert to numbers in the other tables to characters as that seems to be how you are using them, ie arbitrary codes that just happen to be numbers. Do you ever do any arithmetic on the numbers? If you do, how do you deal with the characters? > > Now there are things that you can do to try and make the query work, > like; > > select * from systables > where tabname in (select tabid from systables) > and tabname matches "[0-9]*"> > but unless the optimizer decides to do the and clause first the query > will still fail. You can't rely on this to work, in fact on all of my > real world queries it never does. The only workaround I can see is to > load all of the number records into a temporary table and do the join on > the subset. Am I alone in thinking that Informix is doing the > conversion wrong? Now that I'm using 7.3 is there a way to tell the > optimizer to do the subquery first? > > TIA -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/