Run the Extract in loop

[rafidwh]

Hi All,

I am doing an extract in which the where clause is between range id's.First extract should run for id till 01 (where clause is between 1 to 10), second time it should extract from id 11 to 20 and so on till last ID.

How to run this job in loop, everytime it should be a different Id range.

Thanks in advance

Regards,
Rafidwh

[hamzaqk]

Where are you using the where clause? is it a SQL or is it some other rule ? What exactly are you trying to do in the job here? a bit of detail about job design would help. Till then have a look into the start and end loop activity in the sequence.

[rafidwh]

The where clause is in sql. we need to extract within the range ids and I know the starting and end id. why I am doing the multiple extracts is , if iam using the complete id range then it will take huge amount of time, so i want to split and want to extract in ranges ie to run in loop and pass the id's as parametrs to the sql where clause.

[ray.wurlod]

Job sequence with a list of the range pairs.

[rafidwh]

HI Ray,
thiis what i suppose to do

first extract query

select c1, c2, c3 ... from x WHERE
x.ID > 0 AND x.ID <= 10000000

second and so on


select c1, c2, c3 ... from x WHERE
x.ID > 10000000 AND x.ID <= 20000000


select c1, c2, c3 ... from x WHERE
x.ID > 20000000 AND x.ID <= 30000000



select c1, c2, c3 ... from x WHERE
x.ID > 30000000 AND x.ID <= 40000000

select c1, c2, c3 ... from x WHERE
x.ID > 40000000 AND x.ID <= 50000000

I need to run this until my max id is 50000000

Can you please throw some light, how to pass the pairs in sequence to run in loop.

Thanks,
Rafidwh

[ray.wurlod]

1000000|2000000,2000000|3000000,3000000|4000000,4000000|5000000

Comma delimited.

Assign values to your job parameters using the Field() function with "|" as the delimiter character.

Code: Select all

Field(StartLoop.Counter$, "|", 1)
Field(StartLoop.Counter$, "|", 2)

[ray.wurlod]

Alternative solution for your specific example is to use a counted loop from 1 to 4, and use arithmetic expressions for your job parameters.

Code: Select all

StartLoop.$Counter * 1000000
(StartLoop.$Counter + 1) * 1000000