topic Re: Use an iteration using SQL query OFFSET in Talend Studio

Use an iteration using SQL query OFFSET

kakooo16 — Tue, 21 Apr 2020 16:04:29 GMT

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 .

Re: Use an iteration using SQL query OFFSET

billimmer — Tue, 21 Apr 2020 17:04:06 GMT

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") + ";"

Re: Use an iteration using SQL query OFFSET

kakooo16 — Wed, 22 Apr 2020 08:51:04 GMT

Thanks a lot @lli it works like a charm .

Re: Use an iteration using SQL query OFFSET

kakooo16 — Fri, 08 May 2020 20:04:31 GMT

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

Re: Use an iteration using SQL query OFFSET

billimmer — Fri, 08 May 2020 20:17:53 GMT

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") + ";"

Re: Use an iteration using SQL query OFFSET

kakooo16 — Fri, 08 May 2020 20:29:04 GMT

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/219563#M120897 !

Re: Use an iteration using SQL query OFFSET

kakooo16 — Fri, 08 May 2020 20:39:22 GMT

Here is my tloop configuration :

with batch_size = 10 and nb_lines=30

Here is my postgresinput configuration

Im i correct ? @lli

Re: Use an iteration using SQL query OFFSET

billimmer — Fri, 08 May 2020 20:49:37 GMT

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

Re: Use an iteration using SQL query OFFSET

kakooo16 — Fri, 08 May 2020 20:56:56 GMT

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")+ "\\\"") ;

Re: Use an iteration using SQL query OFFSET

kakooo16 — Fri, 08 May 2020 21:25:29 GMT

Im i linking the components correctly !

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

@lli

Re: Use an iteration using SQL query OFFSET

billimmer — Fri, 08 May 2020 21:32:13 GMT

Ok, it looks like your "All_Items" will contain

1 thru 10 on the first pass,

1 thru 20 on the second pass,

1 thru 30 on the 3rd pass.

Because you add to it on each pass. This is not what you want correct? So maybe use the tReplicate to send one "main" to "All_Items", and a second "main" to process only 1-10, 11-20, and 21-30. Something like this...

Re: Use an iteration using SQL query OFFSET

billimmer — Fri, 08 May 2020 21:39:01 GMT

...Or on the second tReplicate, connect it to another tHashOutput but uncheck the "Append" option

Re: Use an iteration using SQL query OFFSET

kakooo16 — Fri, 08 May 2020 21:45:54 GMT

OKey , Thanks a lot i will try both solution and will give you a feed back as soon as possible !

Greetings