How to add sequence number

srao · Post by **srao** » Wed Apr 18, 2007 10:06 am

how to add sequence number for Existing primary key field for uniqueness
for Example :

181DAC246 + start with the sequence 0001 to every record

181DAC246 +0001
181DAC247 +0002
thank you

DSguru2B · Post by **DSguru2B** » Wed Apr 18, 2007 10:14 am

Look into surrogate key generator. Search the forums. It has been discussed before.

chulett · Post by **chulett** » Wed Apr 18, 2007 12:17 pm

You mean concatenate, not add - yes? If so, generate the number and use the colon (:) concatenation operator to stick them together. Perhaps you need a surrogate key generated, perhaps it just needs to be the @INROWNUM. You'd have to tell us.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 18, 2007 2:57 pm

Is the base number hexadecimal and you wish to add, or is it the leading digits to which you want to append a four digit sequence number? Should the sequence number be hexadecimal or decimal?

vmcburney · Post by **vmcburney** » Wed Apr 18, 2007 6:28 pm

Have a look at the FAQ forum on generating a unique counter in a parallel job. The surrogate key stage wont let you do a complex increment, a parallel transformer will. You need to use the parallel macros for partition numbers to make sure numbers are unique between parallel instances of the transformer.

srao · Post by **srao** » Wed Apr 18, 2007 8:40 pm

yes, it is concatenate in Datastage 7.x server job. @inrownum giving the variable length number 1,2,...10,....100 etc. I need a fixed 4 digit length like 0001...0010,..0100,..etc.

chulett · Post by **chulett** » Wed Apr 18, 2007 8:44 pm

Code: Select all

PrimaryKey : FMT(@INROWNUM,"4'0'R")

ray.wurlod · Post by **ray.wurlod** » Wed Apr 18, 2007 9:02 pm

FirstPart : Fmt(@INROWNUM, "R%4")