How to retrieve multiple rows via lookup?

jasjad999 · Post by **jasjad999** » Thu Oct 14, 2004 3:44 pm

Need help to resolve a problem. We have a sequential file that contains the "partial" key for a table. We would like to do a lookup to the table and fetch every rows (0 to many) that matches this partial key. Is this possible? Here is any example,

Seq file contains, let say, Employee Last Name and DOB,
Smith, 01/01/80
Lee, 08/15/60
.
.

In the EMP table, the columns are
Last Name (part of the key)
DOB (part of the key)
First Name (part of the key)
.
.

And data in the EMP table would look like,
Smith John 01/01/80 ......
Smith James 02/02/82 .....
Smith Jenny 03/03/84 ....
Lee Betty 05/01/60 ....
Lee Brenda 04/04/62 ....

For 1st record in Seq file (Smith), we need to retrieve 3 records (John, James and Jenny). For 2nd record in Seq file (Lee), we need to get 2 records (Betty and Brenda). More of an "explicit cursor" situation.

Is this possible? If yes, any suggestions?

Thank you everyone,
Kevin

dhiraj · Post by **dhiraj** » Thu Oct 14, 2004 4:01 pm

Kevin,

Go to the properties page of the transformer where you are doing the lookup --> inputs tab ---> select your look up link from the drop down list-->check the box which says link returns multiple rows.i am assuming you are using ODBC stage for look up

Search the forum. This has been discussed earlier extensively.

Dhiraj

ray.wurlod · Post by **ray.wurlod** » Thu Oct 14, 2004 4:13 pm

It is possible, as dhiraj says, but only when the reference input link is fed from the ODBC and UniVerse stage types.
Open Help from Manager or Designer and open the topic called Defining Multirow Lookup for Reference Inputs

jasjad999 · Post by **jasjad999** » Thu Oct 14, 2004 9:01 pm

Hi dhiraj/Ray,

Thanks for your information.

Do you remember some of the "topics" related to this subject matter? I've searched the forum with many different key words (i.e. multiple rows, lookup, multirow, etc.), but wasn't able to find any topic closely related to this.

BTW, I am using ORAOCI8 stage to access the DB table.

Thanks,
Kevin

chulett · Post by **chulett** » Thu Oct 14, 2004 9:45 pm

jasjad999 wrote:Do you remember some of the "topics" related to this subject matter?

Searching for 'multirow' in this forum worked fine for me, returning several posts including this one on the subject, which contains some specifics.

you also wrote:BTW, I am using ORAOCI8 stage to access the DB table.

As mentioned above - you can't. Only the ODBC stage for database tables and the UV stage for Universe/hashes support this feature.

How big is your Employee table? Load it into a hash file first and use a UV stage to access it and perform the actual lookup.

vmcburney · Post by **vmcburney** » Thu Oct 14, 2004 9:58 pm

Try this topic:
viewtopic.php?t=84998

viewtopic.php?t=85087

Both of them talk about storing multi rows in hash files and keeping the exact matching of the hash file key.