Re: [IDS 7.31] convert Char to Numeric
dryburghj@yahoo.com (scottishpoet) wrote in message news:<81714288.0407070842.76a7d129@posting.google.com>...
> CREATE TABLE test (col1 CHAR(2));
> INSERT INTO test VALUES ("10");> SELECT sum (col1 * col1) from test;
>
> works OK for me!!
>
> (sum)
>
> 100.000000000000
>
>
> "Fred \\(au boulot\\)" <falxirco@wanadoo.fr> wrote in message news:<ccgche$6a0$1@news-reader5.wanadoo.fr>...
> > Hi !
> >
> > I have a numeric value stored in a char row in a table. I need to calculate
> > a sum :
> >
> > select sum(qte * val_num_in_text)
> > from the_table
> >
> > i get an error : Character to numeric conversion error !
> >
> > how can i convert the text value in numeric, i now about the to_char
> > function but i was not able to find the opposite (to_numeric does not work
> > :-)
> >
> > Many thanks in advance for you help
> >
> > Frederi.
sounds like you have crap in your table you could try something
simular as see below:
create table tessie ( a char(7), mypk int);
insert into tessie values ("BAD",1);
insert into tessie values ("10",1);
create table offender ( a char(7), mypk int);
create procedure convit(f_a char(7), f_mypk int)
returning int;define retval int;
on exception in (-1213)
insert into offender values (f_a, f_mypk); return 0;
end exception with resume ;
let retval = f_a;
return retval;
end procedure;
select sum( convit(a,mypk) * 7 ) from tessie;
select * from offender;
That will return 0 for crap data ; and inserts the crap into a table.
CAREFULL if a lot of crap you may end up with a long trx rollback or
lock overflow; maybe you want a temp table with no log.
See you
Superboer.
Many thanks for your answers,
Will try those solutions right now !
Frederi.
"superboer" <superboer7@planet.nl> a 'crit dans le message de
news:bb790a36.0407072338.5958747a@posting.google.com...
> dryburghj@yahoo.com (scottishpoet) wrote in message
news:<81714288.0407070842.76a7d129@posting.google.com>...
> > CREATE TABLE test (col1 CHAR(2));
> > INSERT INTO test VALUES ("10");> > SELECT sum (col1 * col1) from test;
> >
> > works OK for me!!
> >
> > (sum)
> >
> > 100.000000000000
> >
> >
> > "Fred \\(au boulot\\)" <falxirco@wanadoo.fr> wrote in message
news:<ccgche$6a0$1@news-reader5.wanadoo.fr>...
> > > Hi !
> > >
> > > I have a numeric value stored in a char row in a table. I need to
calculate
> > > a sum :
> > >
> > > select sum(qte * val_num_in_text)
> > > from the_table
> > >
> > > i get an error : Character to numeric conversion error !
> > >
> > > how can i convert the text value in numeric, i now about the to_char
> > > function but i was not able to find the opposite (to_numeric does not
work
> > > :-)
> > >
> > > Many thanks in advance for you help
> > >
> > > Frederi.
>
> sounds like you have crap in your table you could try something
> simular as see below:
>
> create table tessie ( a char(7), mypk int);
> insert into tessie values ("BAD",1);
> insert into tessie values ("10",1);>
> create table offender ( a char(7), mypk int);
> create procedure convit(f_a char(7), f_mypk int)
> returning int;> define retval int;
>
> on exception in (-1213)
>
> insert into offender values (f_a, f_mypk);> return 0;
>
> end exception with resume ;
>
> let retval = f_a;
>
> return retval;
> end procedure;
>
> select sum( convit(a,mypk) * 7 ) from tessie;
>
> select * from offender;>
> That will return 0 for crap data ; and inserts the crap into a table.
>
> CAREFULL if a lot of crap you may end up with a long trx rollback or
> lock overflow; maybe you want a temp table with no log.
>
> See you
>
> Superboer.