Converting integer column from XXXXXN to XXXXXNN

[Scott Rubin]

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

At 02:48 PM 3/26/96 GMT, you 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.
}
} 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
}
}
}
}
}

[Jonathan Leffler]

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: prob...@lynx.informix.com (Paul Roberts)
}Date: 26 Mar 1996 22:56:13 GMT
}X-Informix-List-Id: <news.22525>
}
}In article <4j904d$f...@a3bsrv.nai.net>,

}John Fontanilla <jo...@a3bgate.nai.net> wrote:
}> 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) ;
}[...]