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
How to retrieve multiple rows via lookup?
Moderators: chulett, rschirm, roy
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
Open Help from Manager or Designer and open the topic called Defining Multirow Lookup for Reference Inputs
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
jasjad999 wrote:Do you remember some of the "topics" related to this subject matter?
![Confused :?](./images/smilies/icon_confused.gif)
As mentioned above - you can't. Only the ODBC stage for database tables and the UV stage for Universe/hashes support this feature.you also wrote:BTW, I am using ORAOCI8 stage to access the DB table.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn