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" |
+------------------------------------------+