Re: Converting integer column from XXXXXN to XXXXXNN
In article <4j904d$fp2@a3bsrv.nai.net>,
John Fontanilla <john@a3bgate.nai.net> wrote:
>Hi,
> We have to convert one of our table's integer column from
> XXXXXN to XXXXXNN because we need to have 100 records for each
> XXXXX instead of the 10 we use now.
>[...]
> Ex. From 444440 to 4444400
> From 444449 to 4444409
You might want to try something like this:-
update tab_name
set int_col = ( ( int_col - mod(int_col, 10) ) * 10 ) + mod(int_col, 10) ;
The formula on the right seems to implement the function that you require.
As an experiment, I ran the following SQL script:-
create temp table t1 ( before integer, after integer ) with no log ;
{ insert a bunch of randomish integers... }
insert into t1 ( before, after ) values ( 91320, 91320 ) ;
insert into t1 ( before, after ) values ( 100430, 100430 ) ;
insert into t1 ( before, after ) values ( 100656, 100656 ) ;
insert into t1 ( before, after ) values ( 103774, 103774 ) ;
insert into t1 ( before, after ) values ( 104879, 104879 ) ;
insert into t1 ( before, after ) values ( 106278, 106278 ) ;
insert into t1 ( before, after ) values ( 107502, 107502 ) ;
insert into t1 ( before, after ) values ( 108521, 108521 ) ;
insert into t1 ( before, after ) values ( 109822, 109822 ) ;
insert into t1 ( before, after ) values ( 109971, 109971 ) ;
insert into t1 ( before, after ) values ( 110171, 110171 ) ;
insert into t1 ( before, after ) values ( 110355, 110355 ) ;
insert into t1 ( before, after ) values ( 111221, 111221 ) ;
update t1
set after = ( ( after - mod(after, 10) ) * 10 ) + mod(after, 10) ;
select * from t1 order by 1 ;
and it returned the following:-
before after
91320 913200
100430 1004300
100656 1006506
103774 1037704
104879 1048709
106278 1062708
107502 1075002
108521 1085201
109822 1098202
109971 1099701
110171 1101701
110355 1103505
111221 1112201
which appears to be correct.
I'm not sure at what version of the software the "mod" function became
available, so you may or may not be able to do this as shown above.
Paul
*************************************************************************
Disclaimer: All opinions expressed in this message are well-reasoned and
insightful; needless to say, they are not those of Informix Software, its
partners or lackeys. Anyone who says otherwise is itching for a fight.