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
Run the Extract in loop
Moderators: chulett, rschirm, roy
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.