Page 1 of 1
Retrieve all the records matched
Posted: Thu Jan 12, 2006 11:41 am
by 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
Posted: Thu Jan 12, 2006 11:50 am
by Bala R
Use ODBC or Universe stage to return multiple row for lookup
Failed to retrieve all the records matched
Posted: Thu Jan 12, 2006 1:23 pm
by 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
Re: Failed to retrieve all the records matched
Posted: Thu Jan 12, 2006 3:34 pm
by 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.
Posted: Thu Jan 12, 2006 5:23 pm
by 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.