seq numbers

karrisuresh · Post by **karrisuresh** » Wed Dec 03, 2008 4:36 pm

Hi All

there are 10 columns in my target table,
9 columns are filled from source
and 10th column needs to be filled by number sequence starting from 1

eg for load 1
for 100 records, the 10th column is populated as 1 thorugh 100

eg for load 2
for 100 records, the 10th column is populated as 101 thorugh 200

the job is parallel job

src:table/file
tgt:table

can anyone help me,
thanks in advance

Thanks
Suresh

ray.wurlod · Post by **ray.wurlod** » Wed Dec 03, 2008 5:28 pm

Sure, but you can help yourself faster by searching.

Essentially what you need to do is as follows (in a job sequence).

Determine the current maximum value (use a job).

Load this somewhere convenient (a file, or the job's user status area).

Retrieve that value.

Use that value as a job parameter to provide the starting value of your numeric sequence.

Use system variables (such as @PARTNUM, @PARTCOUNT) to generate a sequence of unique numbers irrespective of the degree of parallelism in your main job.

singhald · Post by **singhald** » Thu Dec 04, 2008 4:21 am

Suresh,

you can use oracle sequence and use a lookup stage in your job perform sparse lookup on oracle sequence and get the next value each time for each record.

chulett · Post by **chulett** » Thu Dec 04, 2008 8:11 am

Something wrong with using the Surrogate Key stage?

karrisuresh · Post by **karrisuresh** » Fri Dec 12, 2008 12:08 am

can you please elaborate it

thanks
suresh

karrisuresh · Post by **karrisuresh** » Fri Dec 12, 2008 12:09 am

Hi Deepak Can you please elaborate it

thanks
sai