RE: Character to numeric conversion error
Scott Black wrote:
> 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?
> >
Scott:
It's not just the cost based aspect, there's also an issue with string =
equality and comparison to be considered, eg:
If you compare two numbers as strings:
"5.0" !=3D "5"
"100" < "3"
"00002" !=3D "2"
I guess it was decided at some stage that if the engine is asked to =
compare a number to a string, then the comparison must be done =
numerically, and in the context above that method does make sense. It's =
just a shame that you can't easily avoid the -1213 error.
For the same reason (you may need '<' or '>', not necessarily '=3D'), I =
would rewrite Andreas' SPL to convert the string to either a number (or =
either a NULL or a zero if it contains alpha characters, depending on =
your application). Something along the following lines (unchecked code, =
probably missing half-a-dozen semi-colons):
CREATE PROCEDURE str_to_num (str VARCHAR(20)) RETURNING INTEGER -- or =decimal
DEFINE num INTEGER;
ON EXCEPTION IN (-1213)
RETURN NULL; -- or zero, -99, or whatever suits your application.
END EXCEPTION;
LET num =3D str;
RETURN num;
END PROCEDURE;
... and you're right, our mail server is a joke.
RET
> > -----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" |
> > +------------------------------------------+