How to loop through row and create outputfile?

[pawe84]

Hi everyone,

I need an outpufile for each delivery number.

Example: At first I check for the current date if there are any deliveries.

A sql query of my delivery master table shows the result:

01/03/2024 | deliv100

01/03/2024 | deliv200

Now I have to get child information at first from deliv100 then create output file

after that get deliv200 child information and create another new outputfile. 

How I can create this loop query?

Thanks in advance for any hints. 

[DBS1]

Is there any convention for the output file names that needs to be created for each delivery?

Thanks

[pawe84]

Each outputfile must have the same name like in the sql query result.

deliv100.csv

deliv200.csv

etc.

[DBS1]

What data needs to be passed through each of these csv file?

[pawe84]

Item information of the delivery.

That means for the query on delivery child table  

-->  where delivery_no =  deliv100

then next loop

--> where delivery_no =  deliv200

etc

[Shicong_Hong · Accepted solution]

Hello 

Assuming your master table returns the following results:

date;deliv_column_name

01/03/2024 | deliv100

01/03/2024 | deliv200

create the job as below:

tMysqlnput_1--main(row1)-->tFlowToITerate--iterate--tFileTouch--oncomponentok--tMysqlInput_2--main--tFileOutputDelimited

on tMysqlInput_1: read the delivery number from master table

tFileTouch: create an empty file for each delivery number, set a dynamic output file name, eg:

"D:/file/"+(String)globalMap.get("row1.deliv_column_name")+".csv"

tMysqlInput_2: read the child information for the current delivery number, eg:

"select * from tableName where delivery_no='"+(String)globalMap.get("row1.deliv_column_name")+"'"

tFileOutputDelimited: write the child information to the file created by tFileTouch, set the file name as:

"D:/file/"+(String)globalMap.get("row1.deliv_column_name")+".csv"

Please try and let me know if you have any questions/issues.

Regards

Shicong