Retrieve all the records matched

[jayram99]

Hi All,

I have 1 input record with 6 key fields in fileand I am doing Lookup in table .

In Table i have 3 records matched for the recod in file. I need to retrieve 3 records from Table and write to output file.

At present I am getting only 1 record.

Thanks in Advance
Jay

[Bala R]

Use ODBC or Universe stage to return multiple row for lookup

[jayram99]

Bala,

I tried to do with ODBC and DB2 Stage.

I am getting only 1 row through ODBC Stage.

If i use DB2 Stage, I am retrieving 3 rows but only 1 row is matched.

The following warning msg wil display.

FCdb2test2..TFc1_CSearch1: Reference lookup generated 3 rows. Only the last row was used.

--------------
1.Is there any particular procedure to write a query in ODBC Stage or
2. Should i define any stage variable which will retrieve all the records matched .

Thanks,
Jayaram

[kwwilliams]

Read the file through a routine and then pass that keys into the job as a parameter. You can then have your sql parameterized with your keys.

[ray.wurlod]

Read the help on how to signal to the ODBC stage that you want multiple rows returned (it's actually a check box in the Transformer stage). You can not do it at all with a DB2 stage - it returns all three but only ever uses the last one in the result set - and generates the warning you mentioned.