User variable Activity and loop activity question

[jagadam]

Hi,

I need to loop the datastage job by passing the values from a text file which is delimited by comma to the where clause in a user defined SQL having two parameters.

Sample file :

JOHN,US
APPLE,UK
ADAM,INDIA

Sample Query:

select A,B from table
where NAME='#jpparm1'
and COUNTY_CD='#jpparm2'

In first run the job should pull the data where NAME='JOHN' and COUNTY_CD='US' and in second run it should pull where NAME='APPLE' and COUNTY_CD='UK'

Previously i've implemented with only one parameter by using execute command stage to read the data from a file and using convert function and then using the loop activity stage.

Could anyone please throw your inputs in achieving this requirement.

[Kryt0n]

Doesn't sound like the solution is any different, just split the two values before passing to the job

[chulett]

You might find this post helpful.

[kandyshandy]

Naveen, i am not sure about your exact requirements. But your sample seems to be little simple and but the solution is time consuming. I mean the job has to run once to fetch just 1 record & keep on running until the loop ends.... PX job takes some time to initialize and then kicks off the data processing.

If you are sure that you will have less than 1000 rows in your input file, then you can do something like this.

Step 1: Concatenate each row in the input file to look like this.

('JOHN~US','APPLE~UK','ADAM~INDIA',...............')

Step 2: Change your SQL as follows.

select A,B from table
where NAME||'~'||COUNTRY_CD IN (output from step 1);

In this case, the job will run only once and the much more faster than the loop process.