Use an iteration using SQL query OFFSET

[kakooo16]

Hello guys ,  

The problem is i have 22000 items but i want to pass 1000 items in each iteration .

Here is my job is as such 

My query is as such 

My question is how do i have to configure my tloop to achieve this problem .

Thanks guys .

[billimmer · Accepted solution]

Yes you will need to configure your tLoop as a for loop with something like:

from: 0

To: ((Integer)globalMap.get("myRecordCount"))

Step: context.loop_batch_size

Then in your query:

...LIMIT " + context.batch_size + "
OFFSET " + globalMap.get("tLoop_1_CURRENT_VALUE") + ";"

[kakooo16]

Thanks a lot @lli it works like a charm .

[kakooo16]

Hello @lli  , 

I have a little issue in this and i didn't know how to solve please ! .

So , the issue is ,  

if i have a list [0,1,2,3,.......,30]

And i treat  with 10 items . 

The result is as such However  , i want to treat 10 items then the second 10 items then the third 10 items to all my list be treated  . 

Means , 

[1,2,3,4,5,6,7,8,9,10] then [11,12,13,14,15,16,17,18,19,20]  then [20,21,22,,23,24,25,26,27,28,29,30]

If you have any suggestion please 

[billimmer]

when I use 

LIMIT 10 OFFSET 0;  with my postgresql query I get 1 through 10

The the next loop would be 

LIMIT 10 OFFSET 10;  with my postgresql query I get 11 through 20

and then 

LIMIT 10 OFFSET 20;  with my postgresql query I get 21 through 30

So in your query in Talend, after your tloop you should have:

ORDER BY some_id
...LIMIT " + context.param_batch_size + "
OFFSET " + globalMap.get("tLoop_1_CURRENT_VALUE") + ";"

[kakooo16]

Hello @lli ,
could you please check this new topic .
I've explained more details about my issue please ? .

https://community.talend.com/t5/Design-and-Development/How-to-handle-list-iteration-Correctly/m-p/21... !

[kakooo16]

Here is my tloop configuration : 

with batch_size = 10 and  nb_lines=30 

Here is my postgresinput configuration 

Im i correct ?  @lli 

[billimmer]

That looks good to me.  And you have your tLoop connect to your input query with an "iterate"?

If you create a very simple test job it doesn't work?

context.nb_lines=30

context.batch_size=10

tLoop-----iterate------>tYourDBinput------main----->tLogRow 

[kakooo16]

Here is my job Design ! Wen i try to stock the items in a list i get then probelm of : 

[1,....,10] --> [1,.....,20]-->[1,...,30 ] 

My tjavaFlex code is as such 

List<String> listOfitems = new java.util.ArrayList<String>();


listOfitems .add("\\\""+(String)globalMap.get("product_id")+ "\\\"") ;

[kakooo16]

Im i linking the components correctly !

I don't know why the list is filled as such !

@lli