Converting integer column from XXXXXN to XXXXXNN

by Jonathan Leffl » Sat, 30 Dec 1899 08:00:00 GMT



Or, slightly more simply:
UPDATE Tab_Name
        SET Int_Col = (Int_Col * 10) - 9 * MOD(Int_Col, 10)
It produces the same answer, and has the same version requirements.
Version 5.03 definitely has the MOD function; it is probably present in all
5.0x versions, whether documented or not, and is definitely present in 6.0x
or later versions.  On the other hand, the 4.12 Engine gives a syntax error
on the UPDATE statement; it does not support the MOD function.
Yours,
Jonathan Leffler (jo...@informix.com) #include <disclaimer.h>
}From: probe...@lynx.informix.com (Paul Roberts)
}Date: 26 Mar 1996 22:56:13 GMT
}X-Informix-List-Id: <news.22525>
}
}In article <4j904d$...@a3bsrv.nai.net>,

}>   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) ;
}[...]




Converting integer column from XXXXXN to XXXXXNN

by Scott Rub » Sat, 30 Dec 1899 08:00:00 GMT



Interesting problem.
Wouldn't it be easier to use SQL.
Try this:
--------------------------------------------------------------------------
update table
set intcol = intcol*10 - (trunc((intcol/10 - trunc(intcol/10)) * 10) * 9)
where 1=1
--------------------------------------------------------------------------
Scott Rubin

}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.
}
}       XXXXX can be from 10000 to 99999.
}       N can be 0 to 9.
}       NN can be from 00 to 99.
}
}       Ex. From 444440 to 4444400
}           From 444449 to 4444409
}
}       We use Informix C-compiler (not RDS) version 5 and ISQL version
}       5 in a SCO computer.
}
}       I have already made up a 4gl program that basically does this (pls
}       excuse syntax errors)
}
}       # START PROGRAM
}       database our_db
}
}       globals define
}               a,b,c,d char[7],
}               e,f,g integer
}       end globals
}
}       main
}
}       declare x cursor for
}               select int_col from tab_name
}                where int_col >= 100000
}       # we still haven't decided yet what to do with records whose
}       # int_col is less than 100000 (10000 - 0)
}       foreach x into a
}               let d = a
}               let b = d[1,5]
}               let c = d[6,6]
}               let e = (b * 100) + c
}               update tab_name set int_col = e where int_col = a
}       end foreach
}       end main
}       # END PROGRAM
}
}       I have not run this program yet because I'm not sure if the update
}       would work, plus I think it's going to be slow. Is there another way
}       to make this work? Pls. email or post. Thanks!
}
}John
}
}                      
}
}
}