Dynamically Create Files - Iterate over List from Query

[talendstar]

I have two tables Cars and CarParts (see sample code and data below).

What I would like to do within Talend is to iterate over the list of Cars and write out all the CarParts for each car into a separate file.

For example:

I have following Car in my car table:

'Corolla','A1111'

For Car Code 'A1111' I would like to retrieve all the parts and write the records to a csv file

and do then move on the next car in my Cars table till I have processed all the cars.

Just wondering how can I do this in Talend.

Here is the sample code for both tables:

create table cars(
	car varchar(10),
	carcode varchar(5)
	)

insert into cars values('Corolla','A1111')
insert into cars values('Honda','B1112')
insert into cars values('C300','C1113')
insert into cars values('Camry','D1114')



create table carparts(
	carcode varchar(5),
	partnum int,
	partname varchar(10)
	)

insert into carparts values ('A1111',1,'Tires')
insert into carparts values ('A1111',2,'Wheels')
insert into carparts values ('A1111',3,'Seats')
insert into carparts values ('A1111',4,'Headlight')
insert into carparts values ('A1111',5,'Mirror')

insert into carparts values ('B1112',1,'Tires')
insert into carparts values ('B1112',2,'Wheels')
insert into carparts values ('B1112',3,'Seats')
insert into carparts values ('B1112',4,'Headlight')
insert into carparts values ('B1112',5,'Mirror')

insert into carparts values ('C1113',1,'Tires')
insert into carparts values ('C1113',2,'Wheels')
insert into carparts values ('C1113',3,'Seats')
insert into carparts values ('C1113',4,'Headlight')
insert into carparts values ('C1113',5,'Mirror')

insert into carparts values ('D1114',1,'Tires')
insert into carparts values ('D1114',2,'Wheels')
insert into carparts values ('D1114',3,'Seats')
insert into carparts values ('D1114',4,'Headlight')
insert into carparts values ('D1114',5,'Mirror')

thanks

[nivedhitha]

Hi @talendstar ,

I have created couple jobs to do what you want. The design of job 1 is

and job2 with a context variable as shown below

what is done here is .. the first jobs does a select * from Cars tables, passes it to tFlowToIterate and for each row in cars table job2 is executed once passing the carcode.

above picture shows passing carcode to job2 in tRunJob.

And the select query in tDBInput1 in job2 is

and I write the output from this component to a csv file where I use the carcode in the context variable as the name of the file being created

And the output is 4 different files for each carcode with its corresponding parts.

Hope this helps!!

Let me know if there is any problem