Reference link with multi row - OCI stage

dprasanth · Post by **dprasanth** » Wed Oct 03, 2007 7:06 am

Hi,
I am using OCI stage for reference Lookup. I need to select multiple row result set from the lookup. I tried opening the tranform stage, right clicked on the title of the reference link and choose link properties. But couldn't find the checkbox "Reference link with multi row result set" checkbox. Then found out that it is applicable only for ODBC stages.

So just wondering what is the best way to achieve multiple row result using OCI stage?

Thanks in Advance.

chulett · Post by **chulett** » Wed Oct 03, 2007 7:11 am

As you've found, that can't be done with an OCI lookup as that option is only applicable when using ODBC or a UV stage. For a 'pure' Oracle solution, I prefer to load the 'source' information into a work table and join that to the lookup table(s) so that the 'multi-row result set' is what streams in as input to the job.

dprasanth · Post by **dprasanth** » Wed Oct 03, 2007 7:20 am

chulett wrote:As you've found, that can't be done with an OCI lookup as that option is only applicable when using ODBC or a UV stage. For a 'pure' Oracle solution, I prefer to load the 'lookup' information into a work table and join that to the source table(s) so that the 'multi-row result set' is what streams in as input to the job.

I really appreciate your quick response.

My INPUT is a SEQUENTIAL FILE STAGE and LOOKUP is a user defined sql in OCI stage(three tables are joined here). My input file has FIRSTNAME and SURNAME as the input field and I lookup and if it is found I get a list of other columns from the lookup OCI. The only reason I cannot use the lookup as the work table is , I will get million of records and obviously I cannot join that with my Input sequential stage right? But I can do one thing, I can put all the records from the input file to a oracle table and then can join that with the lookup sql. I will try that and will let you know. Once again, thanks a lot!

chulett · Post by **chulett** » Wed Oct 03, 2007 7:31 am

dprasanth wrote:But I can do one thing, I can put all the records from the input file to a oracle table and then can join that with the lookup sql. I will try that and will let you know. Once again, thanks a lot!

Actually, that's exactly what I meant.

Edited the original post to (hopefully) be more clear.