cursor lookup

asaf_arbely · Post by **asaf_arbely** » Mon Feb 23, 2009 10:18 am

Hi
We need to perform a cursor lookup in order to find more than one row in the reference link
Our job design is a source from oracle table from one DB and another oracle table in the reference link.
Any ideas?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Feb 23, 2009 10:26 am

Use 'returns multiple rows' options.

chulett · Post by **chulett** » Mon Feb 23, 2009 10:31 am

Unless something has changed in 8.x the only Server stages that support multiple row returns from reference lookups are the ODBC and UV stages.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Feb 23, 2009 11:03 am

Doh!! Me and my PX !!

In server, depending on volume, you can
1.) Use ODBC link
2.) Create db link
3.) Use hash file with combined rows
4.) Simulate multiple rows from rows so each with gather one reference row.

There are other methods also. Need more information on what you are attempting, volume, match possibility etc.

asaf_arbely · Post by **asaf_arbely** » Mon Feb 23, 2009 11:14 am

Can you collaborate more on "3.) Use hash file with combined rows"?
My source is about 100k records and my reference is about 10 million
The match possibility is 0-n
thank's

chulett · Post by **chulett** » Mon Feb 23, 2009 11:39 am

That would be where the UV stage comes in. You can use one to access a hashed file as long as the hashed file was created in an account or you manually create a VOC record for it.

After that, it's a matter of creating the hashed files with enough keys to ensure all records get loaded and then using fewer keys to do the reference lookup in the UV stage so that there are multiples to return per 'key'. In either case you need to enable the option on the Link Properties page of the reference lookup.

The technique has been discussed here quite a bit.