Page 1 of 1
cursor lookup
Posted: Mon Feb 23, 2009 10:18 am
by asaf_arbely
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?
Posted: Mon Feb 23, 2009 10:26 am
by Sainath.Srinivasan
Use 'returns multiple rows' options.
Posted: Mon Feb 23, 2009 10:31 am
by chulett
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.
Posted: Mon Feb 23, 2009 11:03 am
by Sainath.Srinivasan
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.
Posted: Mon Feb 23, 2009 11:14 am
by asaf_arbely
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
Posted: Mon Feb 23, 2009 11:39 am
by chulett
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.