FW: Character to numeric conversion error
Mr. Thomas' mail server doesn't seem to like me so I'm posting to the
group...
FYI Andreas Zeugswetter mailed me a good workaround that sends tabid to
a stored procedure that does the conversion. select num_to_char(tabid)
from systables..., this stops the optimizer from doing any conversion at
all and is good enough for now.
Tech support basically said that's the way it is, and they won't change
it. The tech was obviously a junior level, but I didn't pursue it
because the workaround is really sufficient for my purposes.
Thanks for the help.
> -----Original Message-----
> From: Scott Black
> Sent: Thursday, May 28, 1998 10:22 AM
> To: 'Richard Thomas'
> Subject: RE: Character to numeric conversion error
>
> The fact that numeric comparison would be quicker is an aspect that I
> didn't think of, though I'm sure you're right. So you suspect that
> the optimizer does the conversion cost based as opposed to left right
> or some such, that's interesting. I guess it's the optimizer's job to
> look into cost, but it seems to me that it would have to follow some
> rules of precedence in order to maintain logical equivalence.
> Opinions?
>
> -----Original Message-----
> From: Richard Thomas [SMTP:richard_thomas@yes.optus.com.au]
> Sent: Wednesday, May 27, 1998 6:57 PM
> To: 'informix-list@iiug.org'; Scott Black
> Subject: 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",...)> >
> >
>
> 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.
>
> 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" |
> +------------------------------------------+