Page 1 of 1
Run the Extract in loop
Posted: Mon Mar 17, 2008 9:29 pm
by 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
Posted: Mon Mar 17, 2008 10:10 pm
by 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.
Posted: Tue Mar 18, 2008 11:04 am
by 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.
Posted: Tue Mar 18, 2008 3:13 pm
by ray.wurlod
Job sequence with a list of the range pairs.
Posted: Tue Mar 18, 2008 4:06 pm
by 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
Posted: Tue Mar 18, 2008 5:26 pm
by 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)
Posted: Tue Mar 18, 2008 5:29 pm
by 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