Re: Character to numeric conversion error
I had a similar problem, only my query did work, but the performance was
very poor
because the index was not used. I did the following:
create procedure varchar(data integer) returning varchar(16);return data;
end procedure;
grant execute on varchar to public;
update statistics for procedure varchar;
And now use this proc whenever I want to convert integers to numbers
and not vice versa.
e.g.:
select * from systables
where tabname in (select varchar(tabid) from systables)
andreas.zeugswetter@telecom.at
Scott Black wrote:
> 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.
> The way Informix is doing the conversion, my queries never work.
> 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]*">
> TIA