Multiple rows output required from lookup

mac4rfree85 · Post by **mac4rfree85** » Fri Feb 20, 2015 2:34 am

Hi Guys,

My Source records are unique records. But my reference records contain multiple records for the same key. Now, i want output with multiple records.

Source record
AA 12B 2
BB 23Y 3

Reference recrods
AA 12B U-
AA 12B Y-
BB 23Y I+
BB 23Y O-

Output required
AA 12B U- 2
AA 12B Y- 2
BB 23Y I+ 3
BB 23Y O- 3

My database is oracle. So, i tried to create a hash file for the reference records but hash file only stored unique records.

Can somebody help me in resolving this issue.

Thanks!!

ray.wurlod · Post by **ray.wurlod** » Fri Feb 20, 2015 3:22 am

Don't use a hashed file. Retrieve them directly from Oracle.

mac4rfree85 · Post by **mac4rfree85** » Fri Feb 20, 2015 5:45 am

Ray thanks for the input. But even though i am able to see the multiple records for the same key when i click on view data, I am getting only unique records when i run the job. I am using Oracle Connector Stage.

could you please guide me as how to set up the settings in the reference side so that multiple records will flow for the same key value.

chulett · Post by **chulett** » Fri Feb 20, 2015 8:26 am

Use ODBC which allows a 'multi-row result set' to be returned. That or use the UV stage to access your hashed file. Unless things have changed, those were the only two options for a multiple record lookup.

chulett · Post by **chulett** » Fri Feb 20, 2015 8:27 am

Do an exact search here for "multi-row result set".