Re: Character to numeric conversion error

In article <6kibpd$t1m$1@news.xmission.com>, Richard Thomas <richard_tho mas@yes.optus.com.au> writes > >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",...)>> >> > >The following is just my suspicions... > >It is a reasonable presumption for the Optimiser that since you're trying = >to join a CHAR to a numeric, the CHAR column must actually contain a = >number. So the alternatives to it are: >1) compare the two columns as strings, or >2) compare the two columns as numbers. > This also changed in 4GL. define p_vat_code char(1) let p_vat_code = "R" if p_vat_code = 5 then... Now give a character to numeric conversion error. Of course we have this in several places... define var1 like table1.column if var1 = 5 then var1 was probably an integer, converted to char and no-one noticed the probably because 4gl converted the 5 to "5" not the other way around. All the system testing in the world would not have caught that one in earlier versions.... >Either way one of the columns requires data-type conversion. I would = >suspect the latter comparison would be quicker, and performing the = >conversion text->number allows for things like leading zeroes in the CHAR = >column, which would otherwise fail the equality test if choice (1) was = >taken. > >> 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. >> > >I think you might need a text representation of the number in a second = >column of the other table, which employs the same "mask" as the entries = >in your first table (WRT leading zeroes, decimal places etc). > >> 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 >> >> > >HTH > >RET > >+------------------------------------------+ >| Richard Thomas | >| DBA - Marketing Information Systems | >| Optus IT | >| email: richard_thomas@yes.optus.com.au | >| Ph: +61 2 9342 7188 | >| "My opinions are my opinions" | >+------------------------------------------+ -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care