I need to split a query results and load it in different file due to perfermance reasons, Example :
Select Count(*) from A
20
I want to load the first 10 rows in file 1 and and the second 10 rows in file 2.
My DB is Postgresql and i know that i'm going to use OFFSET and LIMIT in my query, my problem is how to iterate the execution and how to modify the number in limit and offset,
OK, this is easier than it may first appear. The way to achieve this is to use a tLoop. Now you will have to figure out things like....
How many loops do you need?
How do you loop (WHILE loop or FOR loop)?
How to name your files (...although I would recommend using the loop iteration number to make it easy to start with)?
But essentially you will start by calculating your total number of records and divide those by 10 to get the number of "loops" stored in a globalMap. Once you have that, then you go to the tLoop component and set up your WHILE or FOR loop using this value. You then create your subjob which carried out the loading of 10 rows and saving to a file. The OFFSET and LIMIT can be calculated using the globalMap variable you used for your max loops and the loop iteration globalMap, which will look something like this ....
You would store those OFFSET and LIMIT values in the globalMap as well. Then you would use those values in your SQL. You can use Java String manipulation to dynamically build your SQL.
Once that is sorted, you simply need to dynamically alter your filename in your file component. Again, the globalMap values can be used here. An easy way would be to simply append the ((Integer)globalMap.get("tLoop_1_CURRENT_ITERATION")) value to your filename.
What this will do is loop through your data in groups of 10 records. For each group of 10, a new subjob will be initiated. This will deal with creating a new file at the beginning and modifying your SQL to return the data you want.
